Reputation: 3
here is my problem... sorry if it's too simple for some users, I'm a newbie at this.
I have three DB tables, People(idPerson), Question(idQuestion,Answer), AskedQuestions(idPerson,idQuestion). When I ask a question to a user, i add a row to AskedQuestions so i can have the registry.
The problem I have is that I have to select a random question, which wasnt asked before to a specified user.
I've been reading for hours for the best way to select random rows, and separately how to make a query to select only the asked ones, but havent figured how can I mix them.
Anyone knows how can I do it, in PHP and MySql? I'll apreciate it very much. Thanks to the community in advance, and sorry for my english, my english skills are not the best!
Greetings
EDIT
I tried to vote all of u up, but I can't until I have rep15, ahahhaa... I forgot to metion one thing, as I was reading, using RAND() makes a full scan of the table, which in my case is unacceptable because the tables are pretty big Questions about 500000 records and AskedQuestions something about a 1500000 or even more... so the times that takes doing the RAND are tooo slow...
I need something like... join the questions and asked questions and exluding the ones that have the idUser equal to the session user and the idQuestion == idAskedQuestion... and the select a random one from there?
Is there a way to do that, and in that case, It will be horribly slow??
Upvotes: 0
Views: 1290
Reputation: 142560
I have made a survey of how to pick random rows efficiently. The results are here.
For your case, do you have an AUTO_INCREMENT
? If so, then see if "Case: AUTO_INCREMENT with gaps, 1 row returned" will satisfy your need. Note that the WHERE
clause to filter out unwanted questions leads to "gaps".
If not, then consider the FLOAT or UUID approaches. Either should do nicely, but probably requires a column for this "random" purpose.
Upvotes: 0
Reputation: 1793
Try query given below
SELECT * FROM `Question` AS qs WHERE qs.idQuestion not in(SELECT aq.idQuestion FROM
AskedQuestions` AS aq WHERE aq.idPerson = loggedinUserID) ORDER BY RAND() LIMIT 0,1
If You not want to use rand() in query then there is one way for it..
/*Select max and min id*/
$range_result = mysql_query( " SELECT MAX(`idQuestion `) AS max_id , MIN(`idQuestion `) AS
min_id FROM `Question` ");
$range_row = mysql_fetch_object( $range_result );
$random = mt_rand( $range_row->min_id , $range_row->max_id );
$result = mysql_query( " SELECT * FROM `Question` AS qs WHERE qs.`idQuestion` >= $random
AND qs.idQuestion not in (SELECT aq.idQuestion From ASkedQuestions AS aq where
aq.idPerson = loggedInUSerID) LIMIT 0,1 ");
I hope it will be helpful for you.
thanks
thanks
Upvotes: 0
Reputation:
$res=mysql_query("select count(*) as number from question where idquestion not
in(select idquestion from askedquestion where idperson='".$_session['id']."')");
$row=mysql_fetch_assoc($res);
$num=$row['number'];
//$num has no. of rows
$final_query="select * from question where idquestion
not in(select idquestion from askedquestion
where idperson='".$_session['id']."') limit ".rand(0,$num).",1";
It will select different question
Upvotes: 2
Reputation: 3118
What have you done so far?
you could try this:
SELECT * Question q LEFT JOIN AskedQuestions a ON a.idQuestion = q.idQuestion WHERE a.idQuestion is null AND a.id AND a.idperson = 2 ORDER BY RAND() LIMIT 1;
Upvotes: 0
Reputation: 1694
try:
SELECT * FROM Question, AskedQuestions WHERE AskedQuestions.idQuestion != Question.idQuestion AND AskedQuestions.idPerson = 'PERSON_ID' LIMIT 1
Upvotes: 0
Reputation: 9822
Have you already tried ORDER BY RAND()
to select random rows, and NOT IN
to match only not asked questions?
Something like:
SELECT q.idQuestion FROM Question q WHERE q.idQuestion NOT IN (SELECT aq.idQuestion FROM AskedQuestions aq WHERE aq.idPerson = 1) ORDER BY RAND()
Obviously, replace 1 by the appropriate value (probably a PHP variable).
Upvotes: 0
Reputation: 204934
select random questions:
select * from question order by rand() limit 1
Select questions not asked before:
select * from question where idQuestion not in (select idQuestion from AskedQuestions)
combined:
select * from question
where idQuestion not in (select idQuestion from AskedQuestions)
order by rand()
limit 1
Upvotes: 1