Getting random record from database with group by
- by Saif Bechan
Hello i have a question on picking random entries from a database. I have 4 tables, products, bids and autobids, and users.
Products
-------
id 20,21,22,23,24(prime_key)
price...........
etc...........
users
-------
id(prim_key)
name user1,user2,user3
etc
bids
-------
product_id
user_id
created
autobids
--------
user_id
product_id
Now a multiple users can have an autobid on an product. So for the next bidder I want to select a random user from the autobid table
example of the query in language:
for each product in the autobid table I want a random user, which is not the last bidder.
On product 20 has user1,user2,user3 an autobidding.
On product 21 has user1,user2,user3 an autobidding
Then I want a resultset that looks for example like this
20 – user2
21 – user3
Just a random user. I tried miximg the GOUP BY (product_id) and making it RAND(), but I just can't get the right values from it. Now I am getting a random user, but all the values that go with it don't match.
Can someone please help me construct this query, I am using php and mysql
