Categories
mysql php

mysql_fetch_array()/mysql_fetch_assoc()/mysql_fetch_row()/mysql_num_rows etc… expects parameter 1 to be resource

995

I am trying to select data from a MySQL table, but I get one of the following error messages:

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

This is my code:

$username = $_POST['username'];
$password = $_POST['password'];

$result = mysql_query('SELECT * FROM Users WHERE UserName LIKE $username');

while($row = mysql_fetch_array($result)) {
    echo $row['FirstName'];
}

8

  • 15

    you can get more useful eroor msg using:: QUERY or die(mysql_error());

    – nik

    Jun 4, 2010 at 10:26

  • 124

    Also the obligatory note: Your code is prone to SQL injection. You should validate and/or escape the user input. Have a look at mysql_real_escape_string. Never trust user data.

    Jun 4, 2010 at 10:26


  • 7

    Actually, the OP’s code will cause a syntax error on the MySQL server, but at least it is not vulnerable to SQL Injection because single quotes doesn’t have variable interpolation.

    – szgal

    Jul 4, 2014 at 14:06

  • 4

    @FelixKling I realize this is very old, and likely the most accurate possible at the time, but your comment is now dangerously wrong in one way: mysql_real_escape_string is not the be-all and end-all of SQL injection protection; it’s still vulnerable to a number of attacks. (No, you never said it’s perfect, but you implied it was the only required solution) The best solution now is PDO, as far as I know.

    – Nic

    Mar 13, 2017 at 0:30


  • 2

    Gah. Extending this question to include MySQLi and PDO was a bad idea. They each have their own slightly different syntax and error messages and they could perfectly well have had their own questions. Combining everything into one giant three-part question just makes this less Googleable and forces people who arrive here to wade through irrelevant content to get to what they want. It’s also invalidated a great many of the answers below, and makes this question “Too Broad” by the standards we normally apply. It’s a mess, in my opinion, but it’s too late to fix now.

    Nov 15, 2018 at 23:30

676

A query may fail for various reasons in which case both the mysql_* and the mysqli extension will return false from their respective query functions/methods. You need to test for that error condition and handle it accordingly.

*mysql_ extension**:

NOTE The mysql_ functions are deprecated and have been removed in php version 7.

Check $result before passing it to mysql_fetch_array. You’ll find that it’s false because the query failed. See the [mysql_query][1] documentation for possible return values and suggestions for how to deal with them.

$username = mysql_real_escape_string($_POST['username']);
$password = $_POST['password'];
$result = mysql_query("SELECT * FROM Users WHERE UserName LIKE '$username'");

if($result === FALSE) { 
    trigger_error(mysql_error(), E_USER_ERROR);
}

while($row = mysql_fetch_array($result))
{
    echo $row['FirstName'];
}

5

  • 9

    Right, but using a die() if the query fails is a little to much.

    – 2ndkauboy

    Jun 4, 2010 at 10:28

  • 29

    I was going to design an entire error handling mechanism for the OP, but decided that might be beyond the scope of my answer.

    Jun 4, 2010 at 10:29

  • @scompt.com Yes it’s also covered in several other answers. I guess I was just making the point that since this is the accepted answer on a high visibility question, in addition to the (excellent) advice about how to properly catch errors in future, it should (IMHO) actually answer the specific question (ie explain why there’s an error in this case).

    – Sepster

    Apr 23, 2013 at 22:17


  • 2

    Instead of if($result === FALSE) you can use if(! $result). Correct me if I’m wrong

    – anestv

    Jun 17, 2014 at 12:52


  • 1

    mysql_query(): The mysql extension is deprecated and will be removed in the future: use mysqli

    – Greg

    Dec 25, 2014 at 17:24

165

This error message is displayed when you have an error in your query which caused it to fail. It will manifest itself when using:

  • mysql_fetch_array/mysqli_fetch_array()
  • mysql_fetch_assoc()/mysqli_fetch_assoc()
  • mysql_num_rows()/mysqli_num_rows()

Note: This error does not appear if no rows are affected by your query. Only a query with an invalid syntax will generate this error.

Troubleshooting Steps

  • Make sure you have your development server configured to display all errors. You can do this by placing this at the top of your files or in your config file: error_reporting(-1);. If you have any syntax errors this will point them out to you.

  • Use mysql_error(). mysql_error() will report any errors MySQL encountered while performing your query.

    Sample usage:

    mysql_connect($host, $username, $password) or die("cannot connect"); 
    mysql_select_db($db_name) or die("cannot select DB");
    
    $sql = "SELECT * FROM table_name";
    $result = mysql_query($sql);
    
    if (false === $result) {
        echo mysql_error();
    }
    
  • Run your query from the MySQL command line or a tool like phpMyAdmin. If you have a syntax error in your query this will tell you what it is.

  • Make sure your quotes are correct. A missing quote around the query or a value can cause a query to fail.

  • Make sure you are escaping your values. Quotes in your query can cause a query to fail (and also leave you open to SQL injections). Use mysql_real_escape_string() to escape your input.

  • Make sure you are not mixing mysqli_* and mysql_* functions. They are not the same thing and cannot be used together. (If you’re going to choose one or the other stick with mysqli_*. See below for why.)

Other tips

mysql_* functions should not be used for new code. They are no longer maintained and the community has begun the deprecation process. Instead you should learn about prepared statements and use either PDO or MySQLi. If you can’t decide, this article will help to choose. If you care to learn, here is good PDO tutorial.

1

  • 1

    Given this question today stackoverflow.com/q/43804651/1415724 and other similar ones lately; I think it may be worthwhile to update your answer to contain something like “That error can also be caused by not executing the query with mysql_query() / mysqli_query($connection) etc.”; thoughts? Since no other answers in this Q&A mentions this.

    May 5, 2017 at 12:17


112

Error occurred here was due to the use of single quotes ('). You can put your query like this:

mysql_query("
SELECT * FROM Users 
WHERE UserName 
LIKE '".mysql_real_escape_string ($username)."'
");

It’s using mysql_real_escape_string for prevention of SQL injection.
Though we should use MySQLi or PDO_MYSQL extension for upgraded version of PHP (PHP 5.5.0 and later), but for older versions mysql_real_escape_string will do the trick.

2

  • 5

    Why adding noise with string concatenation instead of just putting the variable in the query string?

    Jun 4, 2010 at 16:53

  • 1

    @Matteo Riva Yeah, but I thought this is little cleaner way to separate variables from string. 🙂

    – nik

    Aug 7, 2012 at 7:32