Random row selection using MySQL returns NULL

Question

I am trying to get a random row from MySQL table but all three attemps:

$query = "SELECT cid FROM table LIMIT 1 OFFSET ".rand(1,$num_rows);
$query = "SELECT cid FROM table OFFSET RANDOM() * (SELECT COUNT(*) FROM table) LIMIT 1";
$query = "SELECT * FROM table ORDER BY RAND() LIMIT 1";

give a NULL result in mysql_query($query).

Higher up my PHP code I obtain a row from the same table OK by specifying WHERE, so I don't understand why I can't retrieve a random one.

Here is the code snippet:

    $query = "SELECT uid,clu FROM uable WHERE un = '$un'";
    $result = mysql_query($query) or die(sqlerror(__LINE__,mysql_errno(),mysql_error()));
    $resultid = mysql_fetch_assoc($result);
    $uid = $resultid['uid'];
file_put_contents('debugging.txt',__LINE__.' - $uid = '.var_export($uid,true).PHP_EOL,FILE_APPEND);
    $query = "SELECT * FROM table WHERE uid = $uid AND cn = '$cn'";
    $result = mysql_query($query) or die(sqlerror(__LINE__,mysql_errno(),mysql_error()));
    $cr = mysql_fetch_assoc($result);
    $cid= $cr['cid'];
file_put_contents('debugging.txt',__LINE__.' - $cid= '.var_export($cid,true).PHP_EOL,FILE_APPEND);
    $query = "SELECT * FROM fable WHERE cid= '$cid'";
    $result = mysql_query($query) or die(sqlerror(__LINE__,mysql_errno(),mysql_error()));
file_put_contents('debugging.txt',__LINE__.' - $result = '.var_export($result,true).PHP_EOL,FILE_APPEND);
    $fr = mysql_fetch_assoc($result);
file_put_contents('debugging.txt',__LINE__.' - $fr = '.var_export($fr,true).PHP_EOL,FILE_APPEND);
    echo  '<form action="'.$_SERVER['PHP_SELF'].’" method="post">';
    if (!$fr) {
        $o= $cn;
        while ($o= $cn) {
//    $ac = mysql_query("SELECT * FROM table") or die(sqlerror(__LINE__,mysql_errno(),mysql_error()));
//    $num_rows = mysql_num_rows($ac);
//file_put_contents('debugging.txt',__LINE__.' - $num_rows = '.$num_rows.PHP_EOL,FILE_APPEND);
//    --$num_rows;
//    $query = "SELECT cid FROM table LIMIT 1 OFFSET ".rand(1,$num_rows);
    $query = "SELECT cid FROM table OFFSET RANDOM() * (SELECT COUNT(*) FROM table) LIMIT 1";
//        $query = "SELECT * FROM table ORDER BY RAND() LIMIT 1";
        $resultid = mysql_query($query) or die(sqlerror(__LINE__,mysql_errno(),mysql_error()));
        $opr = mysql_fetch_assoc($resultid);
        $o= $opr['cn'];
        }
file_put_contents('debugging.txt',__LINE__.' - $query = '.$query.PHP_EOL,FILE_APPEND);
file_put_contents('debugging.txt',__LINE__.' - $resultid = '.var_export($resultid,true).PHP_EOL,FILE_APPEND);
file_put_contents('debugging.txt',__LINE__.' - $op[\'cid\'] = '.$op['cid'].PHP_EOL,FILE_APPEND);
        $query = "SELECT * FROM table WHERE cid= ".$op;
        $result = mysql_query($query) or die(sqlerror(__LINE__,mysql_errno(),mysql_error()));
        $opr = mysql_fetch_assoc($opr);
        $o= $opr['cn'];
        $od= $opr['description'];
        echo  '<p>'.$op;
        if ($od<> '') {
        echo  ','.$odesc;
        }
        echo  '</p>';
        echo  '<input type="submit" name="continue" id="continue" value="Continue">';
    } else {
        echo  '<p>'.$fr['p'].'</p>';
        echo  '<input type="submit" name="continue" id="continue" value="Continue">';
    }
    echo  '</form>';

The resulting debugging.txt:
24 - $uid = '4'
29 - $cid = '21'
32 - $result = NULL
34 - $fr = false

Atleast the last one is perfectly fine. Your error lies somewhere else. Mind adding your PHP code to question? — Hanky Panky, Apr 29 '15 at 16:48
"NULL" as in no records, or "NULL" as in all fields have the NULL value? — Uueerdo, Apr 29 '15 at 17:36
@Hanky웃Panky - I have edited the question to include the PHP code. — Jason Stallard, Apr 30 '15 at 12:44
@Uueerdo - The SQL returns null so I assume no records are returned. There are two records in the table for testing at the moment but that will obviously grow. — Jason Stallard, Apr 30 '15 at 12:46
@JasonStallard if there are no rows returned, your `mysql_fetch_assoc` call should return FALSE, not NULL; can you confirm that this fits what you see? — Topher Hunt, Apr 30 '15 at 14:19
As a side note, you should be aware that the `mysql_query` and `mysql_fetch_assoc` functions are deprecated for a reason; they allow and encourage dangerous query techniques and make it easy to accidentally create injection vulnerabilities. Look into the [Mysqli](http://php.net/manual/en/book.mysqli.php) library; it can take some getting used to but to start out you can use it in a function-oriented way to minimize the changes. — Topher Hunt, Apr 30 '15 at 14:19

score 2 · Accepted Answer · answered Apr 29 '15 at 17:58

2

These queries look OK, but I think you're starting at the wrong place. When you're uncertain how to frame something in SQL, open up a SQL client like SequelPro or Navicat and try writing a few queries by hand until you get the result you want. (Also this gives you a chance to double-check the contents of relevant tables and ensure the expected data are there.) Then you can go back into the PHP with full confidence that the SQL code is correct, so if there's a problem it must be with the PHP (either the variables you inject into a Mysql statement, or the way you call that statement).

answered Apr 29 '15 at 17:58

Topher Hunt

4,404
2
27
51

Thanks Topher. The second and third attempts have no injection but all three return a result of NULL. I will amended my question to include the surrounding PHP. – Jason Stallard Apr 30 '15 at 11:30
That PHP code is probably helpful, but I'm not a compiler; the human brain is not designed to parse code. ;-) Again I'd recommend that you set up direct access with the database (via a graphical program like Sequel Pro) and run the raw Mysql query to verify that it gives you the result you expect. Have you done this? – Topher Hunt Apr 30 '15 at 14:14
1

When using running the same code directly 2/3 gave the expected result so it must indeed be my PHP code. I deleted some superfluous lines and checked the variable names and it now works OK. I was in a rut thinking it was the SQL. Thanks for your assistance @Topher-Hunt. – Jason Stallard May 01 '15 at 11:14
Awesome, glad to hear it! Yeah I've found it saves a ton of time when I start my troubleshooting from the ground up: get the Mysql right, then look to the PHP afterwards. – Topher Hunt May 01 '15 at 15:33

Random row selection using MySQL returns NULL

1 Answers1