[RESOLVED] 2 table query

asa_carter

Hi,

echo $num = mysql_num_rows(mysql_query("SELECT DISTINCT bid_history.item_id FROM bid_history, item
WHERE item.auction_end > now() AND bid_history.user_id='$valid_user'"));

I am trying to wite a query to calculate how many item id's there are in the bid_history table for one user and where item has not ended in the item table.

If I set the timestamp in the item table to something in the past, I still get a result of 1(when it should be 0).

Do I need to use a JOIN?

I haven't really used multiple table queries before..

Any help much appreicated..

Asa

bogu

SELECT bid_history.item_id FROM bid_history, item 
WHERE item.auction_end > CURDATE() AND bid_history.user_id='$valid_user'
GROUP BY bid_history.item_id

try it like this ...

asa_carter

It is still returning 1 even though auction_end is in the past.

bid_history table:

id - item_id - user_id - current_bid - highest_bid - quantity - date_time

1 - 57 - asa_carter - 56 - 56 - 1 - 2006-09-19 13:49:56
2 - 57 - asa_carter - 58 - 58 - 1 - 2006-09-19 14:07:29

item table extract:

auction_end
2006-09-12 13:49:30

If the auction end is in the future, from this data it should return 1. If it is in the past it should return 0.

bogu

I because your action_end is a date_time field ...

SELECT bid_history.item_id FROM bid_history, item 
WHERE UNIX_TIMESTAMP(item.auction_end) > NOW() AND bid_history.user_id='$valid_user' 
GROUP BY bid_history.item_id

try it like this ...

asa_carter

That returns 0 but it also returns 0 if auction_end is in the future.

Actually.... looking at the SQL, there is nothing linking which row to use in the item table for auction_end.

item_id - auction_end
57 - 2006-09-20 13:49:30

Will I also need to say something like where item_id in the item table is the item_id in the bid_history table?