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
or
mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given
or
Call to a member function fetch_array() on boolean / non-object
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'];
}
The same applies to code like
$result = mysqli_query($mysqli, 'SELECT ...');
// mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given
while( $row=mysqli_fetch_array($result) ) {
...
and
$result = $mysqli->query($mysqli, 'SELECT ...');
// Call to a member function fetch_assoc() on a non-object
while( $row=$result->fetch_assoc($result) ) {
...
and
$result = $pdo->query('SELECT ...', PDO::FETCH_ASSOC);
// Invalid argument supplied for foreach()
foreach( $result as $row ) {
...
and
$stmt = $mysqli->prepare('SELECT ...');
// Call to a member function bind_param() on a non-object
$stmt->bind_param(...);
and
$stmt = $pdo->prepare('SELECT ...');
// Call to a member function bindParam() on a non-object
$stmt->bindParam(...);
since $username is a php variable we need to pass it as string to mysqli so since in the query u started with a single quote we will use the double quote, single quote and a fullstop for the concatination purposes ("'.$username.'") if you started with a double quote you would then reverse the quotes ('".$username."').
$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'];
}
$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'];
}
but use of Mysql has depreciated much, use PDO instead.it is simple but very secure
Traditionally PHP has been tolerant to bad practice and failures in code, which makes debugging quite hard. The problem in this specific case is that both mysqli and PDO by default don't tell you, when a query failed and just return FALSE. (I will not talk about the depricated mysql extention. The support for prepared statements is reason anough to switch either to PDO or mysqli.) But you can change the default behavior of PHP to always throw exceptions when a query fails.
For PDO: Use $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
error_reporting(E_ALL);
$pdo = new PDO("mysql:host=localhost;dbname=test", "test","");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$result = $pdo->query('select emal from users');
$data = $result->fetchAll();
This will show you the following:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'emal' in 'field list'' in E:\htdocs\test\mysql_errors\pdo.php on line 8
PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'emal' in 'field list' in E:\htdocs\test\mysql_errors\pdo.php on line 8
As you see, it tells you exactly, what is wrong with the query, and where to fix it in your code.
Without $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); you will get
Fatal error: Call to a member function fetchAll() on boolean in E:\htdocs\test\mysql_errors\pdo.php on line 9
For mysqli: Use mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
error_reporting(E_ALL);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'test', '', 'test');
$result = $mysqli->query('select emal from users');
$data = $result->fetch_all();
You will get
Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'Unknown column 'emal' in 'field list'' in E:\htdocs\test\mysql_errors\mysqli.php on line 8
mysqli_sql_exception: Unknown column 'emal' in 'field list' in E:\htdocs\test\mysql_errors\mysqli.php on line 8
Without mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); you only get
Fatal error: Call to a member function fetch_all() on boolean in E:\htdocs\test\mysql_errors\mysqli.php on line 10
Of course, you could manually check the MySQL errors. But I would go crazy if I had to do that every time I made a typo - or worse - every time I want to query the database.