CODEWITHSUNDEEP
phpmysqldatabase
MauriLopez
MauriLopez

Reputation: 3

How can I exclude some records in MySql

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

Answers (7)

Rick James
Rick James

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

Er. Anurag Jain
Er. Anurag Jain

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

user1432124
user1432124

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

iiro
iiro

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

Adam
Adam

Reputation: 1694

try:

SELECT * FROM Question, AskedQuestions WHERE AskedQuestions.idQuestion != Question.idQuestion AND AskedQuestions.idPerson = 'PERSON_ID' LIMIT 1

Upvotes: 0

Guillaume Poussel
Guillaume Poussel

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

juergen d
juergen d

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

Related Questions