$sql variable causes mysql_fetch_array() expects parameter 1 to be resource

Question

I have a search form in php and when the user enters a name to search, form calls the following php code to give results. My problem is that this code when executed gives me "mysql_fetch_array() expects parameter 1 to be resource, boolean given". If I change the $sql to somthing like$sql = " SELECT `name`, `surname`, `email`, `user_id` FROM usersthen it works fine. Any idea where it might be my problem and how to move on?

//more code

$start=0;
$per_page=10;
$page=1;

 $sql = " SELECT `name`, `surname`, `email`, `user_id` FROM users WHERE (surname LIKE '$name%' AND name LIKE '$surname%') OR (surname LIKE '$surname%' AND name LIKE '$name%') LIMIT 100 ";

 $sql .= "LIMIT $start, $per_page";

 $result = mysql_query($sql);

  while($run = mysql_fetch_array($result)){
      $surname = $run['surname'];
  }

you are using the `LIMIT` twice in you query remove any one. — Code Lღver, Jul 08 '13 at 13:23
See [this answer](http://stackoverflow.com/a/11674313/250259) for how to troubleshoot this. — John Conde, Jul 08 '13 at 13:26
check the error message echo mysql_errno($link) . ": " . mysql_error($link) . "\n"; — Sundar, Jul 08 '13 at 13:27
FYI, [you shouldn't use `mysql_*` functions in new code](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [red box](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — John Conde, Jul 08 '13 at 13:27

score 2 · Answer 1 · answered Jul 08 '13 at 13:23

You're generating an invalid query;

$sql = "...LIKE '$name%') LIMIT 100 ";
$sql .= "LIMIT $start, $per_page";

...will generate 2 LIMIT clauses and cause a syntax error. When you get a syntax error and don't check the return value of mysql_query, you'll get the error message in your question since you're not getting a result back. If you checked the return value using;

$result = mysql_query($sql) or die(mysql_error());

...you'd have had an indication to what was wrong as an error message.

oh I see, I use LIMIT 2 times this way... – user2491321 Jul 08 '13 at 13:32 — user2491321, Jul 08 '13 at 13:32

score 1 · Answer 2 · answered Jul 08 '13 at 13:23

1

It looks like you have the LIMIT statement repeated twice. The first line ends with LIMIT 100 and on the next line you are appending LIMIT $start, $per_page which would result in something like:

LIMIT 100 LIMIT 0,10

answered Jul 08 '13 at 13:23

Mike

1,827
10
9

score 0 · Answer 3 · answered Jul 08 '13 at 13:25

This happens because your query fails. Why not put a or die(mysql_error()); to catch errors ?

do something like this

if($result === FALSE) {
    die(mysql_error()); // TODO: better error handling
}

while($run = mysql_fetch_array($result)){
    $surname = $run['surname'];
}

As @Mike pointed , you have a syntax error, == > 2 times LIMIT

$sql variable causes mysql_fetch_array() expects parameter 1 to be resource

3 Answers3