...

View Full Version : datetime select issuse



clunky
07-27-2007, 08:18 AM
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 ;-)

_Aerospace_Eng_
07-27-2007, 09:38 AM
What does your php code look like?

clunky
07-27-2007, 10:00 AM
$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

clunky
07-27-2007, 10:01 AM
edited this due to page loading loads of tabs in FF and entering it twice!?!

_Aerospace_Eng_
07-27-2007, 10:05 AM
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.

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

clunky
07-27-2007, 10:17 AM
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.

_Aerospace_Eng_
07-27-2007, 10:19 AM
What I'm referring to is this

$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

$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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum