php querying database

[Champak]

Background:
I have a shopping cart and using fedex as my shipping. Unfortunately, the shopping cart does not connect with fedex directly to print out the shipping labels, get tracking number and everything else, it just gives the rate of the shipment. I contacted fedex and they gave me a software to batch upload all orders at once, or individually. Problem, my cart does not export shipping info in csv, so I either have to go into the database every day to export and create a custom query in the format of fedexs csv uploader, or type each order out individually. I would rather not do either of those.

Prefer:
I would like to set up a web page in my general admin area where I could query the database from online and it spits out a straight csv file or a zipped csv file with the info requested. It took quite a few hours on my end, being ignorant to this whole programming thing, to make the initial query statement that needs to run..

SELECT orders_id, customers_company, customers_name,  delivery_street_address,   delivery_suburb,  delivery_city,  delivery_state,  delivery_country, delivery_postcode,  customers_telephone FROM  `gp_orders`  WHERE orders_id>147

What I need to be able to happen is the query of "orders_id>147" needs to be a text area that I type the number into. That is the first order of the day and I would type it in, that way I only get the orders of this day to spit out when I press the link or submit button.

And I know there are other carts out there that may do this, but I prefer this cart and it has a whole lot of other features.

Can someone help me put together this php page?

Thanks.

[lintz]

Is this something like what you're after?

<?php
$OrderNumber = $_POST['order_number']; //Get order number entered

if (!isset($_POST['submit'])) { // if form hasn't been submitted to itself then show form

?>
<center>
<form method="POST" action="<?php echo $PHP_SELF;?>">

Order Number: <input type="text" name="order_number" size="5"><p>
<input type="submit" value="Get Orders" name="submit"><p></td>

<?

} else {

//Connect to your database here if not already

$sql = "SELECT orders_id, customers_company, customers_name,  delivery_street_address,   delivery_suburb,  delivery_city,  delivery_state,  delivery_country, delivery_postcode,  customers_telephone FROM  `gp_orders`  WHERE orders_id> '$OrderNumber'"; //query database
$query = mysql_query($sql)
or die("".mysql_error());

//This shows all the orders greater than the one entered in the first screen
echo "<center>Below are all the orders greater than <b>$OrderNumber</b>.<p>";

//Show results

while($order_details = mysql_fetch_row($query)) {

echo "$order_details[0], $order_details[1], $order_details[2]<br>"; //etc...

}
}

?>

[Champak]

That is excellent! Thanks. Three things though.

1/ I would rather need this to spit out in zipped csv file, or just open up in notepad in csv style, if either of those are possible.

2/ I don't know if this is possible, but I would actually prefer to do this by the date, but it wont work. The date column format is as follows, "2005-06-29 07:03:19". I actually only want to put in the date portion of that.

3/ The submit button and text box to doesn't with the results. As it is, once the result appears, or the action goes through, if a new search needs to be done, I need to press the back button.

4/ And lastly, is there a way to convert things to something else when the results are presented. Ex. the database gives the states as full words like New York, but the fedex takes it abbreviated as NY, and same with the country is United States and fedex takes it as US. This I guess I could do manually after the fact if it's not possible, but would be great if it's done automatically.


Thanks a lot.

[lintz]

1. The results come out in comma seperated format so why not copy them to Notepad?

2. Instead of saving the date in format "2005-06-29 07:03:19" use time() when order is placed then you will be able to sort by date

3. Add the below after your query is actioned.

<center> 
<form method="POST" action="<?php echo $PHP_SELF;?>"> 

Order Number: <input type="text" name="order_number" size="5"><p> 
<input type="submit" value="Get Orders" name="submit"><p>

4. The easiest way would to have the abbreviations in a table. Can you get them from Fedex?

[Champak]

1/ I guess I could do that, I am just being real picky and would prefer it the other way. But it's all good.

2/ a/ I think the way the date is set up down to the seconds is better for security and tracking things if anything goes wrong with "bad people". b/ I wouldn't know where to begin in the script to find out where to change this, and if it would mess something else up in the cart.

3/ I ended up putting it somewhere else which works fine.

4/ I don't understand what you are saying in here.

Here is what I did with the code so far to "translate" the states to the abbreviated format.

//This shows all the orders greater than the one entered in the first screen
echo "<center>Below are all the orders greater than <b>$OrderNumber</b>.<p>";

//Show results

while($order_details = mysql_fetch_row($query)) {


$search = array("New York, United States");
$replace = array("NY, US");
$subject = "$order_details[0], $order_details[1], $order_details[2], $order_details[3], $order_details[4], $order_details[5], $order_details[6], $order_details[7], $order_details[8], $order_details[9]<br>";


echo str_replace($search, $replace, $subject); "$order_details[6], $order_details[7]<br>"; //etc...

The only problem I'm having is, I can't set the thing to search for all the states and provinces, because if they aren't in the query results, then the function doesn't work at all.

Here is what I mean...If orders came in from NY and United States, the replacement will go through fine.

But let's say I had California and a bunch of other states in the search and replace function, but again, only orders from New York came in today, then the replacement wont even happen.

[Champak]

Thanks for your help. I've finished coding it and it seems to work fine now.

[visualAd]

You can have your CSV file download by sending the following headers:

header('Content-Type: text/plain');
header('Content-Disposition: attachment; filename=db.csv'); 

Also, why don't you store the states and their abbreviations in a separate table, you can then use a query to display them?

[Champak]

Thanks, I got everything taken care of now.