My Watch List stays empty

[mausman]

Hello all.
Hope someone can help me out.
Im trying to use the webauction and did install and customize a little.
I noticed also the My Watch List is working fine. Untill I logged in a little later and the My Watch List is empty telling that the user has no bids so far. In the mean time no code did change.
So I did build up a fresh install including database on the server again and all worked fine again. Untill after a couples of logins the Watch list is empty again.
Just only did build a new database but that didnt help so it must be something in the code but I cant figure it out.
Any help is appreciated.

[mausman]

Just some additional information.
Also when I run the SQL queuery directly on the database I get no rows back. (ofcourse i replaced the variables and getusername)

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0017 sec)

SELECT p.product_id, p.product_name, concat( '€', format( high_bid, 2 ) ) AS high_bid, high_bidder
FROM products p
INNER JOIN (

SELECT product_id, max( bid_amount ) AS high_bid
FROM bids
GROUP BY product_id
) AS hb ON p.product_id = hb.product_id
INNER JOIN (

SELECT product_id, username AS high_bidder
FROM bids b
WHERE NOT
EXISTS (

SELECT *
FROM bids
WHERE product_id = b.product_id
AND bid_amount > b.bid_amount
)
) AS hbr ON p.product_id = hbr.product_id
WHERE 'testuser01'
IN (

SELECT username
FROM bids
WHERE product_id = p.product_id
)
LIMIT 0 , 30

[mausman]

Im not a SQL expert but I think I found a solution for my problem.
Just implemented it and we are now testing but it looks very promissing.
If someone still has any usefull hints and tips im glad to hear it.

For those who experience the same problems here is the new SQL statement in watch_list.php;

SELECT p.product_id, p.product_name, concat( '€', format( high_bid, 2 ) ) AS high_bid, high_bidder
FROM products p
INNER JOIN (

SELECT product_id, username, max( bid_amount ) AS high_bid
FROM bids
WHERE username = '".getUsername()."'
GROUP BY product_id
) AS hb ON p.product_id = hb.product_id
INNER JOIN (

SELECT product_id, username AS high_bidder
FROM bids b
WHERE NOT
EXISTS (

SELECT *
FROM bids
WHERE product_id = b.product_id
AND bid_amount > b.bid_amount
)
) AS hbr ON p.product_id = hbr.product_id";

[hmaloney]

Thanks very much for your efforts. I was having a similar problem and your new SQL code has solved it.