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(...);
Answers
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.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
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) {
die(mysql_error()); // TODO: better error handling
}
while($row = mysql_fetch_array($result))
{
echo $row['FirstName'];
}
mysqli extension
procedural style:
procedural style:
$username = mysqli_real_escape_string($mysqli, $_POST['username']);
$result = mysqli_query($mysqli, "SELECT * FROM Users WHERE UserName LIKE '$username'");
// mysqli_query returns false if something went wrong with the query
if($result === FALSE) {
yourErrorHandler(mysqli_error($mysqli));
}
else {
// as of php 5.4 mysqli_result implements Traversable, so you can use it with foreach
foreach( $result as $row ) {
...
oo-style:
$username = $mysqli->escape_string($_POST['username']);
$result = $mysqli->query("SELECT * FROM Users WHERE UserName LIKE '$username'");
if($result === FALSE) {
yourErrorHandler($mysqli->error); // or $mysqli->error_list
}
else {
// as of php 5.4 mysqli_result implements Traversable, so you can use it with foreach
foreach( $result as $row ) {
...
using a prepared statement:
$stmt = $mysqli->prepare('SELECT * FROM Users WHERE UserName LIKE ?');
if ( !$stmt ) {
yourErrorHandler($mysqli->error); // or $mysqli->error_list
}
else if ( !$stmt->bind_param('s', $_POST['username']) ) {
yourErrorHandler($stmt->error); // or $stmt->error_list
}
else if ( !$stmt->execute() ) {
yourErrorHandler($stmt->error); // or $stmt->error_list
}
else {
$result = $stmt->get_result();
// as of php 5.4 mysqli_result implements Traversable, so you can use it with foreach
foreach( $result as $row ) {
...
These examples only illustrate what should be done (error handling), not how to do it. Production code shouldn't use
or die
when outputting HTML, else it will (at the very least) generate invalid HTML. Also, database error messages shouldn't be displayed to non-admin users, as it discloses too much information.
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.
Put quotes around
$username
. String values, as opposed to numeric values, must be enclosed in quotes.$result = mysql_query("SELECT * FROM Users WHERE UserName LIKE '$username'");
Also, there is no point in using the
LIKE
condition if you're not using wildcards: if you need an exact match use =
instead of LIKE
.
Your code should be something like this
$username = $_POST['username'];
$password = $_POST['password'];
$query = "SELECT * FROM Users WHERE UserName LIKE '$username'";
echo $query;
$result = mysql_query($query);
if($result === FALSE) {
die(mysql_error("error message for the user"));
}
while($row = mysql_fetch_array($result))
{
echo $row['FirstName'];
}
Once done with that, you would get the query printed on the screen. Try this query on your server and see if it produces the desired results. Most of the times the error is in the query. Rest of the code is correct.
This query should work:
$result = mysql_query("SELECT * FROM Users WHERE UserName LIKE '%$username%'");
while($row = mysql_fetch_array($result))
{
echo $row['FirstName'];
}
The problem is single quotes, thus your query fails and returns FALSE and your WHILE loop can't execute. Using % allows you to match any results containing your string (such as SomeText-$username-SomeText).
This is simply an answer to your question, you should implement stuff mentioned in the other posts: error handling, use escape strings (users can type anything into the field, and you MUST make sure it is not arbitrary code), use PDO instead mysql_connect which is now depricated.
If you tried everything here, and it does not work, you might want to check your MySQL database collation. Mine was set to to a Swedish collation. Then I changed it to
utf8_general_ci
and everything just clicked into gear.
I hope this helps someone.
Try this, it must be work, otherwise you need to print the error to specify your problem
$username = $_POST['username'];
$password = $_POST['password'];
$sql = "SELECT * from Users WHERE UserName LIKE '$username'";
$result = mysql_query($sql,$con);
while($row = mysql_fetch_array($result))
{
echo $row['FirstName'];
}
There might be two reasons:
- Have you opened the connection to the database prior to calling mysql_query function? I don't see that in your code. Use mysql_connect before making the query. See
php.net/manual/en/function.mysql-connect.php
- The variable $username is used inside a single quote string, so its value will not be evaluated inside the query. The query will definitely fail.
Thirdly, the structure of query is prone to SQL injection. You may use prepared statements to avoid this security threat.
<?php
$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'];
}
?>
And if there is a user with a unique user name, you can use "=" for that. There is no need to like.
Your query will be:
mysql_query("SELECT * FROM Users WHERE UserName ='".$username."'");
Don't use the depricated mysql_* function (depricated in php 5.5 will be removed in php 7). and you can make this with mysqli or pdo
here is the complete select query
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
// code here
}
} else {
echo "0 results";
}
$conn->close();
?>
First, check your connection to the database. Is it connected successfully or not?
If it's done, then after that I have written this code, and it works well:
if (isset($_GET['q1mrks']) && isset($_GET['marks']) && isset($_GET['qt1'])) {
$Q1mrks = $_GET['q1mrks'];
$marks = $_GET['marks'];
$qt1 = $_GET['qt1'];
$qtype_qry = mysql_query("
SELECT *
FROM s_questiontypes
WHERE quetype_id = '$qt1'
");
$row = mysql_fetch_assoc($qtype_qry);
$qcode = $row['quetype_code'];
$sq_qry = "
SELECT *
FROM s_question
WHERE quetype_code = '$qcode'
ORDER BY RAND() LIMIT $Q1mrks
";
$sq_qry = mysql_query("
SELECT *
FROM s_question
WHERE quetype_code = '$qcode'
LIMIT $Q1mrks
");
while ($qrow = mysql_fetch_array($sq_qry)) {
$qm = $qrow['marks'] . "<br />";
$total += $qm . "<br />";
}
echo $total . "/" . $marks;
}
Any time you get the...
"Warning: mysqli_fetch_object() expects parameter 1 to be mysqli_result, boolean given"
...it is likely because there is an issue with your query. The
prepare()
or query()
might return FALSE
(a Boolean), but this generic failure message doesn't leave you much in the way of clues. How do you find out what is wrong with your query? You ask!
First of all, make sure error reporting is turned on and visible: add these two lines to the top of your file(s) right after your opening
<?php
tag:error_reporting(E_ALL);
ini_set('display_errors', 1);
If your error reporting has been set in the php.ini you won't have to worry about this. Just make sure you handle errors gracefully and never reveal the true cause of any issues to your users. Revealing the true cause to the public can be a gold engraved invitation for those wanting to harm your sites and servers. If you do not want to send errors to the browser you can always monitor your web server error logs. Log locations will vary from server to server e.g., on Ubuntu the error log is typically located at
/var/log/apache2/error.log
. If you're examining error logs in a Linux environment you can use tail -f /path/to/log
in a console window to see errors as they occur in real-time....or as you make them.
Once you're squared away on standard error reporting adding error checking on your database connection and queries will give you much more detail about the problems going on. Have a look at this example where the column name is incorrect. First, the code which returns the generic fatal error message:
$sql = "SELECT `foo` FROM `weird_words` WHERE `definition` = ?";
$query = $mysqli->prepare($sql)); // assuming $mysqli is the connection
$query->bind_param('s', $definition);
$query->execute();
The error is generic and not very helpful to you in solving what is going on.
With a couple of more lines of code you can get very detailed information which you can use to solve the issue immediately. Check the
prepare()
statement for truthiness and if it is good you can proceed on to binding and executing.$sql = "SELECT `foo` FROM `weird_words` WHERE `definition` = ?";
if($query = $mysqli->prepare($sql)) { // assuming $mysqli is the connection
$query->bind_param('s', $definition);
$query->execute();
// any additional code you need would go here.
} else {
$error = $mysqli->errno . ' ' . $mysqli->error;
echo $error; // 1054 Unknown column 'foo' in 'field list'
}
If something is wrong you can spit out an error message which takes you directly to the issue. In this case there is no
foo
column in the table, solving the problem is trivial.
If you choose, you can include this checking in a function or class and extend it by handling the errors gracefully as mentioned previously.
Check your connection first.
Then if you want to fetch the exact value from the database then you should write:
$username = $_POST['username'];
$password = $_POST['password'];
$result = mysql_query("SELECT * FROM Users WHERE UserName =`$usernam`");
Or you want to fetch the
LIKE
type of value then you should write:$result = mysql_query("SELECT * FROM Users WHERE UserName LIKE '%$username%'");
You can also check wether
$result
is failing like so, before executing the fetch array$username = $_POST['username'];
$password = $_POST['password'];
$result = mysql_query('SELECT * FROM Users WHERE UserName LIKE $username');
if(!$result)
{
echo "error executing query: "+mysql_error();
}else{
while($row = mysql_fetch_array($result))
{
echo $row['FirstName'];
}
}
Usually an error occurs when your database conectivity fails, so be sure to connect your database or to include the database file.
include_once(db_connetc.php');
OR
// Create a connection
$connection = mysql_connect("localhost", "root", "") or die(mysql_error());
//Select database
mysql_select_db("db_name", $connection) or die(mysql_error());
$employee_query = "SELECT * FROM employee WHERE `id` ='".$_POST['id']."';
$employee_data = mysql_query($employee_query);
if (mysql_num_rows($employee_data) > 0) {
while ($row = mysql_fetch_array($employee_data)){
echo $row['emp_name'];
} // end of while loop
} // end of if
- Best practice is to run the query in sqlyog and then copy it into your page code.
- Always store your query in a variable and then echo that variable. Then pass to
mysql_query($query_variable);
.
0 comments:
Post a Comment