Solved

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

Posted on 2006-06-29
16
393 Views
Last Modified: 2006-11-18
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. =)

0
Comment
Question by:stormist
  • 9
  • 4
  • 3
16 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 17011141
$strsql = "select * from questions where questionid not in ( " . join (",", $quests) . ")  ORDER BY RAND() LIMIT 5 ";
0
 
LVL 8

Expert Comment

by:Autogard
ID: 17011217
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
 
LVL 8

Expert Comment

by:Autogard
ID: 17011229
whups -- angelll's should also work, but to just get 1 question you will want "LIMIT 1"
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17011252
Autogard: >Retrieving 5 random values ...
0
 
LVL 8

Expert Comment

by:Autogard
ID: 17011266
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
 
LVL 10

Author Comment

by:stormist
ID: 17011347
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
 
LVL 10

Author Comment

by:stormist
ID: 17011375
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
 
LVL 8

Assisted Solution

by:Autogard
Autogard earned 200 total points
ID: 17011389
$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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

Expert Comment

by:Autogard
ID: 17011397
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
 
LVL 8

Expert Comment

by:Autogard
ID: 17011407
With regards to angelll's suggestion it looks like you are missing the opening and closing parentheses in your implementation.
0
 
LVL 8

Expert Comment

by:Autogard
ID: 17011434
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17011454
>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
 
LVL 8

Expert Comment

by:Autogard
ID: 17011468
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
 
LVL 10

Author Comment

by:stormist
ID: 17011826
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17012952
>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
 
LVL 8

Expert Comment

by:Autogard
ID: 17012955
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now