0

I have a problem to select question from table 'questions',randomize it, and only select 15 question. I came up with the following query which is not working like I hoped.

$singleSQL = mysql_query("SELECT * FROM questions WHERE id='$question' ORDER BY RAND() LIMIT 1");

    while($row = mysql_fetch_array($singleSQL))
    {
        $id = $row['id'];
        $thisQuestion = $row['question'];
        $type = $row['type'];
        $question_id = $row['question_id'];
        $q = '<h2>'.$thisQuestion.'</h2>';

        $sql2 = mysql_query("SELECT * FROM answers WHERE question_id='$question' ORDER BY rand()");
        while($row2 = mysql_fetch_array($sql2))
        {
            $answer = $row2['answer'];
            $correct = $row2['correct'];
            $answers .= '<label style="cursor:pointer;"><input type="radio" name="rads" value="'.$correct.'">'.$answer.'</label> 
            <input type="hidden" id="qid" value="'.$id.'" name="qid"><br /><br />
            ';

        }
        $output = ''.$q.','.$answers.',<span id="btnSpan"><button onclick="post_answer()">Submit</button></span>';
        echo $output;
       }
    }
user3286777
  • 29
  • 2
  • if you had even basic error handling, you'd have been told about the syntax error in your first query. NEVER assume success. always check for errors: `mysql_query(...) or die(mysql_error());` – Marc B Dec 15 '15 at 19:08
  • `ORDER BY` goes after a `WHERE` clause, not before. RT*M http://dev.mysql.com/doc/refman/5.7/en/select.html – Funk Forty Niner Dec 15 '15 at 19:11
  • thanks for your concern sir, it seems i have made a careless mistake when typing the query, it will try to avoid it next time. – user3286777 Dec 15 '15 at 19:17
  • @Fred-ii- I also say like that only in my answer `order by rand` is after `where` clause. – Shailesh Katarmal Dec 15 '15 at 19:21
  • [Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Jay Blanchard Dec 15 '15 at 19:24
  • Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Dec 15 '15 at 19:24
  • 'order by rand' does not randomize the question like i wanted too, is there any other solution? – user3286777 Dec 15 '15 at 19:25
  • we're not sure if your code contains errors or not. Consult these following links http://php.net/manual/en/function.mysql-error.php and http://php.net/manual/en/function.error-reporting.php and apply that to your code. Plus, this function is unknown to us `onclick="post_answer()"` or if you have form tags for the inputs. Or using JS, hard to say here. – Funk Forty Niner Dec 15 '15 at 19:26

1 Answers1

-2
"SELECT * FROM questions ORDER BY RAND(), WHERE id='$question' LIMIT 1"

Replace with:

"SELECT * FROM questions  WHERE id='$question' ORDER BY RAND() LIMIT 1"
Shailesh Katarmal
  • 2,757
  • 1
  • 12
  • 15
  • The question from the table cannot be randomize after i replace with the query you have shared, i wonder where it went wrong... – user3286777 Dec 15 '15 at 19:05
  • it selects only one record from table also for particular question id . so may be it only one record with that question id in table. – Shailesh Katarmal Dec 15 '15 at 19:08
  • I understand the part where it matches the id from the table, but it still making me confused, 'order by rand()' should be functioning perfectly, but it still does not show any result that i want, my first query before i put the 'order by rand()' is mysql_query("SELECT * FROM questions WHERE id='$question' LIMIT 1"); – user3286777 Dec 15 '15 at 19:15