Re: php querying database
Is this something like what you're after?
PHP Code:
<?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...
}
}
?>
Re: php querying database
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.
Re: php querying database
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.
PHP Code:
<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?
Re: php querying database
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.
Code:
//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.
Re: php querying database
Thanks for your help. I've finished coding it and it seems to work fine now.
Re: php querying database
You can have your CSV file download by sending the following headers:
PHP Code:
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?
Re: php querying database
Thanks, I got everything taken care of now.