...

View Full Version : querying development



Champak
07-06-2005, 08:57 PM
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 php 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.

Champak
07-08-2005, 05:14 AM
I managed to hack together the following code with someones help. However there are three more things that are needed. Two of which are just for ease of use, but the last is a MUST.

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

2/ Like how I have to leave the 00:00:00 format as the value, I'm wondering if there is someway to have two separate text boxes, 1 empty for me to put the date in and the 2nd with the default 00:00:00, and on submit it merges the two.

Those two were just for my ease of use, this next one is an absolute MUST to fix.

3/ The MAIN only problem I'm having is, my shopping cart script database has the states and country in their full names instead of abreviated. FedEx only accepts abbreviated format. So I had to make a search and replace function in the results. However, I can't set the thing to search for all the states and provinces to replace, because if they aren't in the query results, then the function doesn't work at all.

Ex. If orders came in from New York and United States, the replacement will go through fine and change them to NY and US; because as you see I have New York and United States in the query and replace.

However, 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 function wont even happen for the ones in New York. I'm assuming because it was searching for the other states and didn't find them and it somehow disrupted everything else.

HELP PLEASE!!! Especially on 3.




<?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;?>">


<td class="pageHeading" align="left">FedEx Shipping Info</td></tr><tr>
<br>
<td><br>Order Number: <input type="text" name="order_number" size="18" value="00:00:00"><p>
<input type="submit" value="Get Orders" name="submit"><p></td>

</form>
</center>
<?

} else {

//Connect to your database here if not already

//This puts the search function in the results
?>
<center>
<form method="POST" action="<?php echo $PHP_SELF;?>">


<td class="pageHeading" align="left">FedEx Shipping Info</td></tr><tr>
<br>
<td><br>Order Number: <input type="text" name="order_number" size="18" value="00:00:00"><p>
<input type="submit" value="Get Orders" name="submit"><p></td>

</form>
</center>

<?
$sql = "SELECT orders_id, customers_company, customers_name, delivery_street_address, delivery_suburb, delivery_city, delivery_state, delivery_country, delivery_postcode, customers_telephone FROM `gpcommun_orders` WHERE date_purchased> '$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 placed after <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...

}
}

?>

delinear
07-08-2005, 02:40 PM
For point 1, if you set an excel MIME header and output the data, in theory it should open up excel (or a compatable spreadsheet app) and display the content. Here is a list of mime headers (http://www.webmaster-toolkit.com/mime-types.shtml) - there are a few for excel, you could try playing with them and see if any of them do what you want.

For point 3, try running through a loop for all the elements in your array, like this:


$search = array("New York, United States", "California, United States"); // set up the arrays
$replace = array("NY, US", "CA, US"); // the arrays must correspond for this to work

$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>";

for($x=0; $x < count($search); $x++) {
echo str_replace($search[$x], $replace[$x], $subject); "$order_details[6], $order_details[7]<br>"; // etc... // this will run the replace on every element
}

It's not very good practice, coding-wise, since it's using more resources than necessary but without rewriting large sections of what you have it should hopefully provide what you need.

Champak
07-12-2005, 03:25 AM
Thanks for your help



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum