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 7 of 7
  1. #1
    New Coder
    Join Date
    Jul 2007
    Posts
    65
    Thanks
    2
    Thanked 0 Times in 0 Posts

    datetime select issuse

    good day to everyone.
    I am having a problem selecting rows from a table with a select statement using the value of Time.

    my db table field for this is set to 'datetime' .
    When using phpmyadmin I can select the data using the following example;

    select * from tb where date_purchased > '2007-07-21 16:52:11'

    Now this returns some 65 rows which is correct.

    Within php I have run the same query but it will only return the first row.
    If I change the select to get a different field in the where clause it works fine.
    So what is it about the 'datetime' date_purchased field which I am using which is causing the problem?
    I set the value of date_purchased with an update using $time =date("Y-m-d H:s", (time()+10*3600));, and that works fine.

    If anyone has experienced this or could shed some light on it, I would be greatful.
    Cheers ;-)

  • #2
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    What does your php code look like?
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #3
    New Coder
    Join Date
    Jul 2007
    Posts
    65
    Thanks
    2
    Thanked 0 Times in 0 Posts

    php

    PHP Code:
    $Q_Order mysql_query("SELECT o.orders_id,payment_method,date_purchased FROM " ORDERS " o, " ORDERS_TOTAL " ot WHERE date_purchased >'2007-07-21 00:00:00' AND
                                ot.orders_id = o.orders_id AND
                                ot.class = 'ot_total' ORDER BY date_purchased"
    );
    while(
    $R_Order mysql_fetch_assoc($Q_Order)){
        
    $Q_Item mysql_query("SELECT op.products_price, op.products_tax, op.products_quantity  FROM " ORDERS_PRODUCTS " op, " PRODUCTS " p WHEREop.orders_id = '$R_Order[orders_id]' AND p.products_id = op.products_id");
                                
    echo 
    "$R_Order[orders_id]<br>"
    the echo just brings up one result.....

    BTW the code is copied from another page which works, the only difference being with the date > bit, so the sql seems to work ok

    cheers

  • #4
    New Coder
    Join Date
    Jul 2007
    Posts
    65
    Thanks
    2
    Thanked 0 Times in 0 Posts

    php

    edited this due to page loading loads of tabs in FF and entering it twice!?!

  • #5
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    Where are you constants? Also you should be doing some error checking to see if the query even works so you should be using or die(). Try the below, tell us if you get any errors.
    PHP Code:
    $Q_Order mysql_query("SELECT o.orders_id,payment_method,date_purchased FROM " ORDERS " o, " ORDERS_TOTAL " ot WHERE date_purchased >'2007-07-21 00:00:00' AND
                                ot.orders_id = o.orders_id AND
                                ot.class = 'ot_total' ORDER BY date_purchased"
    ) or die(mysql_error());
    while(
    $R_Order mysql_fetch_assoc($Q_Order)){
        
    $Q_Item mysql_query("SELECT op.products_price, op.products_tax, op.products_quantity  FROM " ORDERS_PRODUCTS " op, " PRODUCTS " p WHEREop.orders_id = '$R_Order[orders_id]' AND p.products_id = op.products_id") or die(mysql_error());
                                
    echo 
    "$R_Order[orders_id]<br>"
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #6
    New Coder
    Join Date
    Jul 2007
    Posts
    65
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Not sure what you mean about the constants?, however there are no errors with the query, it just brings up the order id number 66, which is the first row returned.

    Many thanks for your help, its greatly appreciated.

  • #7
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    What I'm referring to is this
    PHP Code:
    $Q_Order mysql_query("SELECT o.orders_id,payment_method,date_purchased FROM " ORDERS " o, " ORDERS_TOTAL 
    That right there shows that you are trying to concatenate normal text, your query should be erroring out. I think your first query should be
    PHP Code:
    $Q_Order mysql_query("SELECT o.orders_id,payment_method,date_purchased FROM ORDERS_TOTAL WHERE date_purchased > '2007-07-21 00:00:00' AND orders_id = o.orders_id AND class = 'ot_total' ORDER BY date_purchased") or die(mysql_error()); 
    It would help if we could see your table structure.
    Last edited by _Aerospace_Eng_; 07-27-2007 at 10:22 AM.
    ||||If you are getting paid to do a job, don't ask for help on it!||||


  •  

    Posting Permissions

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