Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Jul 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    querying development

    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..
    Code:
    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.

  • #2
    New to the CF scene
    Join Date
    Jul 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.


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

  • #3
    Regular Coder
    Join Date
    Feb 2005
    Location
    West Midlands, UK
    Posts
    623
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 - 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:

    PHP Code:
    $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.

  • #4
    New to the CF scene
    Join Date
    Jul 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your help


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •