SQL within PHP - need another set of eyes

Question

This question might have been asked before and I apologise if it has. I have tried some solutions to my error but still can't find the solution.

My code is:

$sql = "SELECT S.ID, S.sentence "
   . "FROM   word I, word2category C, sentence S "
   . "WHERE  C.category_ID = 10 "
   . "AND    I.ID = C.word_ID "
   . "AND    S.word_ID = I.ID "
   . "AND    S.ID NOT IN (SELECT sentence_ID FROM annotation WHERE IP = '" . $ip . "')"
   . "ORDER BY RAND()";

   $res = mysql_query($sql);
   $row = mysql_fetch_array($res);
   $ID = $row["S.ID"];
   echo "<input type='hidden' name='ID' value='$ID'/>\n";  // --- get sentence ID to     store an annotation later on

   $sentence = $row["S.sentence"];
   // $sentence = str_replace("£", "&pound;", $sentence);

I'm attempting to retrieve a random sentence which has an ID within the sentence table and is a part of a category in another table (category_id) .

I'm receiving this error:

 mysql_fetch_array() expects parameter 1 to be resource, boolean given

I think it's because the $sql query failed (or returned 0 rows). This causes $res to become null/false, and "mysql_fetch_array()" will return that error if $res is null/false. Effectively, the error is saying that it's expecting $res to be a mysql result, but it's actually a Boolean (false).

However, I've looked at it for so long, I can't resolve it :(

Thankyou in advance.

You're right, your query is failing providing boolean false which is giving you your error. **Stop using mysql functions, they are deprecated** - use `mysqli` or `PDO` instead. However, a better way to find out your problem would be to add `mysql_error()` onto the end of your query: `mysql_query($sql) or die(mysql_error());` - at least this will tell you where your query is failing. — scrowler, Dec 19 '13 at 20:54
@scrowler I've added mysql_query($sql) and it just gives me the same error. — , Dec 19 '13 at 20:59
@user3120662 then you haven't actually added it. `mysql_query($sql) or die(mysql_error())` will **die immediately** when that query fails, it won't get to your `mysql_fetch_array()` call — scrowler, Dec 19 '13 at 21:01
should be this line: `. "AND S.ID NOT IN (SELECT sentence_ID FROM annotation WHERE IP = '" . $ip . "') "` you are a missing a space (added at the end) — Leonardo, Dec 19 '13 at 21:03
"I think it's because the $sql query failed" - why don't you *know*? — symcbean, Dec 19 '13 at 21:03
@scrowler ah thanks, got it to work now. just a mysql deprecate error left and I should have it solved. thanks. — , Dec 19 '13 at 21:06

score 1 · Answer 1 · answered Dec 19 '13 at 21:47

There's a fair bit wrong with your PHP (aside from using a deprecated method) but I'm not best qualified to fix that, so I'll just stick to the query...

$sql = "
SELECT s.ID
     , s.sentence 
  FROM word i
  JOIN word2category c
    ON c.word_ID = i.ID 
  JOIN sentence s 
    ON s.word_ID = i.ID
  LEFT
  JOIN annotation a
    ON a.sentence_id = s.ID
   AND a.ip = '$ip'
 WHERE c.category_ID = 10 
   AND a.sentence_id IS NULL
 ORDER 
    BY RAND();
";

SQL within PHP - need another set of eyes

1 Answers1