Help with a Mysql Statement. Retrieving 5 random values excluding some based on the key

I have a MySQL Table:

questions (
questionid int unsigned not null auto_increment primary key,
question text not null
);

   
I need to retrieve a random question from the database, excluding five contained in the array $quests.

i.e.
if i had
      $quests[0] = 5
      $quests[1] = 7
      $quests[2] = 13
      $quests[3] = 16
      $quests[4] = 21

then:
I need a random questions(the text) excluding any with number keys 5,7,13,16,21.
This may seem simple but I keep getting erros in my SQL statement so please help. =)

LVL 10
stormistAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
$strsql = "select * from questions where questionid not in ( " . join (",", $quests) . ")  ORDER BY RAND() LIMIT 5 ";
0
 
AutogardCommented:
Set up the series of items to exclude:
$excludeQueryPart = implode(" OR questionid != ", $quests);

Then your query would be:
SELECT question FROM questions WHERE questionid != $excludeQueryPart ORDER BY RAND() LIMIT 1

(http://www.desilva.biz/mysql/random.html)
0
 
AutogardCommented:
whups -- angelll's should also work, but to just get 1 question you will want "LIMIT 1"
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Autogard: >Retrieving 5 random values ...
0
 
AutogardCommented:
I was basing my comment on the text of the question, not the title:

Text: "I need to retrieve a random question from the database, excluding five" and "I need a random question"
Title: "Retrieving 5 random values"

Hence, the confusion.  :)
0
 
stormistAuthor Commented:
I can't seem to get it to work.. Can we go back to basics and go over the proper way to extract the results from this statement? I just want to make sure I'm not making a silly error..

0
 
stormistAuthor Commented:
Substituting the values the variable puts in the following statement doesnt seem to be working:

$sql = "SELECT * FROM questionstoask WHERE questionid NOT IN 20,1,19,23,16 ORDER BY RAND() LIMIT 1"

Here is my actual table layout to avoid confusion:

 table questionstoask (
    questionid int unsigned not null auto_increment primary key,
    question text not null,
    category char(50) not null,
    picture char(30)
);
0
 
AutogardConnect With a Mentor Commented:
$result = mysql_query("SELECT question FROM questions WHERE questionid != $excludeQueryPart ORDER BY RAND() LIMIT 1");
if(!$result)
{
    print "ERROR: " . mysql_error() . "<br>";
}
else
{
    $questionRow = mysql_fetch_object();
    print "Question: " . $questionRow->question . "<br>";
}
0
 
AutogardCommented:
Two fixes related to my suggestion:

1. $excludeQueryPart = implode(" OR questionid != ", $quests); -- replace "OR" with "AND" (in my first suggestion)
2. Replace "SELECT question FROM questions" with "SELECT question FROM questionstoask" (in my last suggestion)
0
 
AutogardCommented:
With regards to angelll's suggestion it looks like you are missing the opening and closing parentheses in your implementation.
0
 
AutogardCommented:
3. Replace "mysql_fetch_object();" with "mysql_fetch_object($result);"

Note: this is if you really just want 1 question.  If you want 5 you will need to loop through the results (www.php.net/mysql_fetch_object);
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>With regards to angelll's suggestion it looks like you are missing the opening and closing parentheses in your implementation.
Here is the code again:
$strsql = "select * from questions where questionid not in ( " . join(",", $quests) . ")  ORDER BY RAND() LIMIT 5 ";

where are there parentheses missing? I think all is in there? Or are you referring to the output posted by the questionner? there indeed are the (  ) missing, although they should be there.
0
 
AutogardCommented:
Yes, I was referring to stormist's "$sql = "SELECT * FROM questionstoask WHERE questionid NOT IN 20,1,19,23,16 ORDER BY RAND() LIMIT 1"" --> no parentheses.
0
 
stormistAuthor Commented:
Thanks guys its working.. While you two are here, might I ask a problem related to this? I am passing the numbers to the processing script by AJAX and a GET statement. The problem is it doesnt seem to work when I use any letters or nonnumerical characters. So I have five questions on the screen. The script returns a question that doesn't include any of the five already listed. (which now works thanks to your help) The problem is I'm using "88" as a seperator. This is all fine unless one of my question Ids happens to be one that contains and eight.
<?php
$allofthem = $_GET['questions'];
$quests = explode('88', $allofthem);
$conn = dbinfo();        
$sql = "select * from questionstoask where questionid not in ( " . join (",", $quests) . ")  ORDER BY RAND() LIMIT 1 ";
$result = mysql_query($sql) or die(mysql_error());
$newquestion = mysql_result($result,0,1);
echo $newquestion;
?>
any ideas on how to solve this? Thanks a bunch for your help
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>This is all fine unless one of my question Ids happens to be one that contains and eight.

how did you come to the idea to use 88 as separator?????

depending on what you have as values (assuming you only have numerical values), you might use _  or __ instead

0
 
AutogardCommented:
Your IDs are always numerical values, right?  So can you use another delimiter besides "88"?

If there are always 5 questions you could also do something like this: ?q1=14&q2=17&q3=12&q4=8&q5=3
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.