num_rows error

awebb88

Hi guys, new to the forum and learning PHP but hoping to stick around for a long time. 🙂

I've got a book I'm getting through and and after some very good and successful tutorials I attempted to create a php page that can edit users profile.

Part of the code is below (if someone needs all of it that's fine to provide but thought a snippet of where the problem line is and after it would be easier), and then underneath that is the error, the page itself is accessed through view_users.php

// Retrieve the user's information:
$q = "SELECT first_name, last_name, email FROM users WHERE user_id=$id";
$r = @mysqli_query ($dbc, $q);

if (mysqli_num_rows($r) == 1) { // Valid user ID, show the form.

// Get the user's information:
$row = mysqli_fetch_array ($r, MYSQLI_NUM);

// Create the form:
echo '<form action="edit_user.php" method="post">
	<p>First Name: <input type="text" name="first_name" size="15" maxlength="15" value="' . $row[0] . '" /></p>
	<p>Last Name: <input type="text" name="last_name" size="15" maxlength="30" value=' . $row[1] . '" /></p>
	<p>Email Address: <input type="text" name="email" size="20" maxlength="40" value=' . $row[2] . '" /></p>
	<p><input type="submit" name="submit" value="Submit" /></p>
	<input type="hidden" name="submitted" value="TRUE" />
	<input type="hidden" name="id" value="' . $id . '" />
	</form>';

} else { // Not a valid user ID.
echo '<p class="error">This page has been accessed in error.</p>';
}

mysqli_close($dbc);
include ('footer.html');
?>

error: Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /studhome/0/0812578/public_html/edit_user.php on line 89

This page has been accessed in error.

...I wish I could say I've tried everything as I probably haven't but I have no idea what the error is for. I'm guessing it is needing there to be only one user but more/none are coming up?

Thanks for any help.

johanafm

Something went wrong. The call to

$r = @mysqli_query ($dbc, $q);

returned false, not a result set, which is why you get that error: mysqli_num_rows(false)

if (!$r) {
	# error handling. error_log(), display some kind of error message to user, exit?
	error_log(mysqli_error($dbc));
}
elseif (mysqli_num_rows($r) == 1)

Also, there's no reason for the error supression operator, @, in front of the call to mysqli_query. There rarely ever is a need for @. If you use it you're probably doing something the wrong way.

awebb88

Ah right, thanks a lot for the reply.

I use the @ just because that's what they done in the book, pretty new to it all so I'll take that on board and not use it unless needed.

I'll re-do the code later, see what results I get and re-post with hopefully success!

awebb88

I added the error message and that's now what's coming up.

So it is saying that there is no result to even continue with the script? Which would mean there is a problem with the code further up the script? (If I'm on the right lines of course)

laserlight

Check that the related SQL statement is correct. Maybe you made a spelling mistake (be it in this statement or the one that created the table). Maybe you even forgot to create the table.

awebb88

I'm going off of the tutorials in the book, it does make sense to me with what he wants to do, I'm just struggling with why it isn't working.

Here's the full code:

<?php # Script 9.3 - edit_user.php

// This page is for editing a user record.
// This page is accessed through view_users.php

$page_title = 'Edit a User';
include ('header.html');

echo '<h1>Edit a User</h1>';

// Check for a valid user ID, through GET or POST:
if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) { // From view_users.php
$id = $GET['id'];
} elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) { // Form submission.
$id = $_POST['id'];
} else { // No valid ID, kill the script.
echo '<p class="error">This page has been accessed in error.</p>';
include ('footer.html');
exit();
}

require_once('mysqli_connect.php');

// Check if form has been submitted:
if (isset($_POST['submitted'])) {
	$errors = array();

// Check for a first name:
if (empty($_POST['first_name'])) {
	$errors[] = 'You forgot to enter your first name.';
} else {
	$fn = mysqli_real_escape_string($dbc, trim($_POST['first_name']));
}

// Check for a last name:
if (empty($_POST['last_name'])) {
	$errors[] = 'You forgot to enter your last name.';
} else {
	$ln = mysqli_real_escape_string($dbc, trim($_POST['last_name']));
}

// Check for an email address:
if (empty($_POST['email'])) {
	$errors[] = 'You forgot to enter your email address.';
} else {
	$e = mysqli_real_escape_string($dbc, trim($_POST['email']));
}

if (empty($errors)) { // If everything's ok.

		  // Test for unique email address:
		  $q = "SELECT user_id FROM users WHERE email='$e' AND user_id != $id";
		  $r = mysqli_query($dbc, $q);
		  if (mysqli_num_rows($r) == 0) {

			  // Make the query:
			  $q = "UPDATE users SET first_name='$fn', last_name='$ln', email='$e' WHERE user_id=$id LIMIT 1";
			  $r = @mysqli_query ($dbc, $q);
			  if (mysqli_affected_rows($dbc) == 1) { // If it ran ok.

			  // Print a message:
			  echo '<p>The user has been edited.</p>';
			  } else { // If it did not run ok
			  echo '<p class="error">The user could not be edited due to a system error. We apologize for any inconvenience.</p>'; // Public message.
			  echo '<p>' . mysqli_error($dbc) . '<br />Query: ' . $q . '</p>'; // Debugging message.
			  }

		  } else { // Already registered.
		  echo '<p class="error">The email address has already been registered.</p>';
		  }

		  } else { // Report the errors.

		  echo '<p class="error">The following error(s) occurred:<br />';
		  foreach ($errors as $msg) { // Print each error
		  echo " - $msg<br />\n";
		  }
		  echo '</p><p>Please try again</p>';
		  } // End of if (empty($errors)) IF.

} // End of submit conditional.

// Always show the form...

// Retrieve the user's information:
$q = "SELECT first_name, last_name, email FROM users WHERE user_id=$id";
if (!$r) {
    # error handling. error_log(), display some kind of error message to user, exit?
    error_log(mysqli_error($dbc));
	echo '<p class="error">You are unable to edit your user\'s profile at this stage.</p>';
}
elseif (mysqli_num_rows($r) == 1) {


// Get the user's information:
$row = mysqli_fetch_array ($r, MYSQLI_NUM);

// Create the form:
echo '<form action="edit_user.php" method="post">
	<p>First Name: <input type="text" name="first_name" size="15" maxlength="15" value="' . $row[0] . '" /></p>
	<p>Last Name: <input type="text" name="last_name" size="15" maxlength="30" value=' . $row[1] . '" /></p>
	<p>Email Address: <input type="text" name="email" size="20" maxlength="40" value=' . $row[2] . '" /></p>
	<p><input type="submit" name="submit" value="Submit" /></p>
	<input type="hidden" name="submitted" value="TRUE" />
	<input type="hidden" name="id" value="' . $id . '" />
	</form>';

} else { // Not a valid user ID.
echo '<p class="error">This page has been accessed in error.</p>';
}

mysqli_close($dbc);
include ('footer.html');
?>

I am thinking that the problem is further up the script (problem is in // Retrieve user information), and that $r has not been assigned a true value/any value at all. I've tried changing a few things around in the // make the query and // check for unique email address but to no avail. I've checked all the spelling and it seems to be in order (gone over it twice but it might just be me) and there are no brackets, curly brackets or semi-colon's missing. Really stumped ;/

webdevnoob

awebb88;10957498 wrote:
I'm going off of the tutorials in the book, it does make sense to me with what he wants to do, I'm just struggling with why it isn't working.

Books sometimes have typos so double check the books code. Your error message indicates to me that the problem is directly with your SQL, i.e., looking for a table or field that doesn't exist. Make sure your query actually matches the db you are using.

Jeff,
http://www.webdevnoobs.com

awebb88

Gone through the code in the book twice to the code I have and it's all fine, I've tried changing statements, checking the database connection. Completely stumped.

My delete users file works, and it's very similar to the edit one:

<?php # Script 9.2 - delete_user.php

// This page is for deleting a user
// Page is accessed through view_users.php

$page_title = 'Delete a User';
include ('header.html');

echo '<h1>Delete a User</h1>';

// Check for a valid user ID, through GET or POST:
if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) )
	{ // From view_users.php
	$id = $_GET['id'];
	} elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) 
	{ // Form submission
	$id = $_POST['id'];
	} else { // No valid ID, kill the script.
	echo '<p class="error">This page has been accessed in error.</p>';
	include ('footer.html');
	exit();
	}

require_once('mysqli_connect.php');

// Check if the form has been submitted:
if (isset($_POST['submitted'])) {
	if ($_POST['sure'] == 'Yes') { // Delete the record.

	// Make the query:
	$q = "DELETE FROM users WHERE user_id=$id LIMIT 1";
	$r = @mysqli_query ($dbc, $q);
	if (mysqli_affected_rows($dbc) == 1) { // If it ran ok.

	// Print a message:
	echo '<p>The user has been deleted.</p>';

	} else { // If the query did not run ok.
	echo '<p class="error">The user could not be deleted due to a system error.</p>'; // Public message.
	echo '<p>' . mysqli_error($dbc) . '<br />Query: ' . $q . '</p>'; // Debugging message.
	}

	} else { // No confirmation of deletion.
	echo '<p>The user has NOT been deleted.</p>';
	}

} else { // Show the form.

// Retrieve the user's information:
$q = "SELECT CONCAT(last_name, ', ', first_name) FROM users WHERE user_id=$id";
$r = @mysqli_query($dbc, $q);
if (mysqli_num_rows($r) == 1) { // Valid user ID, show the form.

// Get the user's information:
$row = mysqli_fetch_array ($r, MYSQLI_NUM);

// Create the form:
echo '<form action="delete_user.php" method="post">
<h3>Name: ' . $row[0] . '</h3>
	<p>Are you sure you want to delete this user?<br />
	<input type="radio" name="sure" value="Yes" /> Yes
	<input type="radio" name="sure" value="No" checked="checked" /> No</p>
	<p><input type="submit" name="submit" value="submit" />
	<input type="hidden" name="submitted" value="TRUE" />
	<input type="hidden" name="id" value="' . $id . '" />
</form>';

} else { // Not a valid user ID.
echo '<p class="error">This page has been accessed in error.</p>';
}

}// End of the main conditional.

mysqli_close($dbc);

include('footer.html');
?>

Appreciate the advice already given guys, even though it isn't working I feel as though I'm learning.

bradgrafelman

Going back to post #2 above...

What's the exact SQL error message returned from MySQL? Also, output the SQL query string itself and paste it here for us to see.

awebb88

This is the error message:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /studhome/0/0812578/public_html/edit_user.php on line 89

This page has been accessed in error.

Line 89 is this:

 if (mysqli_num_rows($r) == 1) { // Valid user ID, show the form.

And the error message is saying it isn't a valid user ID.

If that was what you wanted?

bradgrafelman

awebb88 wrote:
And the error message is saying it isn't a valid user ID.

No, it's not.

The PHP error message is saying that you passed an unexpected parameter to mysql_num_rows(); you gave it a boolean when it expected a MySQLi_Result object.

Where did the boolean come from? The $r parameter, of course.

Why is $r a boolean? Because it contains the result of the call to [man]mysqli_query/man.

Why did [man]mysqli_query/man return a boolean? Read the manual:

PHP Manual wrote:
Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a result object. For other successful queries mysqli_query() will return TRUE.

Since you issued a SELECT query, this must mean that the former boolean case (FALSE on failure) is what happened.

Why was there a failure? Ask MySQL: [man]mysqli_error/man.

awebb88

I'm a bit lost with it all at the moment tbh, fairly new to it all so go easy on me lol 😃

Code:

 else { // If the query did not run ok.
		echo '<p class="error">The user could not be edited due to a system error.</p>'; // Public message.
		echo '<p>' . mysqli_error($r) . '<br />Query: ' . $q . '</p>'; // Debugging message.
		}

This is the error message I get:

The user could not be edited due to a system error.

Warning: mysqli_error() expects parameter 1 to be mysqli, boolean given in /studhome/0/0812578/public_html/edit_user.php on line 107

Query: SELECT first_name, last_name, email FROM users WHERE user_id=

So because it displayed the second error message, that means the query didn't run, so it has to be something to do with the $r/$q code further up? Unless I'm way off here.

But looking at the code, with the statements nothing looks out of place. ;/

bradgrafelman

The [man]mysqli_error/man function expects a 'MySQLi' object, which is the object you get when you create a connection to the MySQL server.

In your code above, it would appear that this resource is stored in the variable named $dbc.

johanafm

awebb88;10957498 wrote:

Here's the full code:

// Retrieve the user's information:
$q = "SELECT first_name, last_name, email FROM users WHERE user_id=$id";
if (!$r) {
    # error handling. error_log(), display some kind of error message to user, exit?
    error_log(mysqli_error($dbc));
	echo '<p class="error">You are unable to edit your user\'s profile at this stage.</p>';
}

It seems to me you've removed actually calling the db: there's a line of code missing between $q= and if (!$r).
Once you put mysqli_query back in between those lines, mysqli_error($dbc) would tell you what went wrong, if anything did.

awebb88

Aah I see, my bad!

That's definately helped and shown what the error is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

So something to do with one of the queries? I'll have a look through it now and see what I can come up with.

bradgrafelman

You probably have an empty variable or something of that nature.

Echo out the query string itself to visually inspect it. If you still can't spot the error, post the query for us to see as well.

jeremyphphaven

Hello...

Are you sure the $id is being set? I'm having a hard time seeing where you're ever getting that data from.

I see that $id is being set when you come from view_users.php, or a form submission happens.
But I don't see where you set the $id for testing. Meaning... if you're just sitting there refreshing edit_user.php over and over again for the book's sake... I don't see where $id is set (i think it's not set) and thus the mysqli query fails (returning the FALSE boolean)

To test my crazy theory... try adding the following right before any of your queries... example:

// Retrieve the user's information: 
echo ' user id is equal to: '.$id; exit();
$q = "SELECT first_name, last_name, email FROM users WHERE user_id=$id";

if it's not that query, try the other one you mention.

Or I'm crazy... but worth a shot. =O)

awebb88

Sorry for the slight delay fellas - been majorly busy with work.

I'll dig the code up later and give it a bash and post what happens. 🙂

awebb88

Looks like you guys are right, there is nothing being assigned to $id, when I put your code in Jeremy it comes up with:

user id is equal to:

Because at the start of the code it says accessed through view_users.php but looking at it I'm not sure I can actually see where it fetches the page from? Does it need to be included/required at some point?

webdevnoob

awebb88;10958282 wrote:
Looks like you guys are right, there is nothing being assigned to $id, when I put your code in Jeremy it comes up with:

Because at the start of the code it says accessed through view_users.php but looking at it I'm not sure I can actually see where it fetches the page from? Does it need to be included/required at some point?

Two things to do:
1) Hard code a value into your page for testing.
Right above the line

if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) )

add $_GET['id] = 1; //use an id of a record that already exists in the db

So your code will look like:

$_GET['id] = 1; //use an id of a record that already exists in the db
if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) )

The above test will tell u if the script is running.

You wrote: Because at the start of the code it says accessed through view_users.php but looking at it I'm not sure I can actually see where it fetches the page from? Does it need to be included/required at some point?

view_users.php will be passing an id to your delete_user.php by using either a post or a get. In other words, view_users.php contains a form that will post to delete_users.php OR it contains a link that is to delete_users.php?id=1 (1 being what ever the id is of the record you wish to delete.)

Jeff