Solved

mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in ......HELP!

Posted on 2010-08-21
19
10,234 Views
Last Modified: 2012-05-10
Something weird is going on. I believe my syntax to be right but when I run the .php page is is getting a "Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\CustomersPlus\content\themes\phpmysql\8212010.php on line 24" which is the ($num = mysqli_num_rows($r);) line . I've scoured EE for previous topics but none similar to mine. Could someone please offer any assistance.
<?php
// This script retrieves all the records from the customers table.

echo '<h1>Customers:</h1>';

require_once ('mysqli_connect.php'); 	// Connect to the db.
		
/* Write the query...
   	Note: CONCAT_WS is a function that concatenates items together with a given separator.
	In this case, the last name and first name are added together , and separated by a comma and a space. */

$q = "SELECT CustomerID, OldCustomerID, CONCAT_WS(', ', LastName, FirstName, MiddleName) AS 'Customer Name',CONCAT ((LEFT(FirstName, 1)), (LEFT(MiddleName, 1)), LastName) AS Username, cookek.zlu_Cars.Description AS Car, cookek.zlu_CarColor.Description AS 'Car Color', cookek.zlu_Computers.Description AS Computer, IsLaptop, cookek.zlu_Race.Description AS Race, cookek.zlu_Residence.Description AS Residence, cookek.zlu_BirthMonth.Description

FROM ((((((customers 
JOIN zlu_CarColor ON Customer.CarColorID = zlu_CarColor.CarColorID) JOIN zlu_Cars ON Customers.CarID = zlu_Cars.CarID) JOIN zlu_Computers ON Customers.ComputerID = zlu_Computers.ComputerID) JOIN zlu_Race ON Customers.RaceID = zlu_Race.RaceID) JOIN zlu_Residence ON Customers.ResidenceID = zlu_Residence.ResidenceID) JOIN zlu_BirthMonth ON Customers.BirthMonthID = zlu_BirthMonth.BirthMonthID)

WHERE Customers.CustomerID BETWEEN 500 and 600
ORDER BY LastName";

$r = mysqli_query ($dbc, $q); // Run the query. @mysqli_query


// Count the number of returned rows:
$num = mysqli_num_rows($r);


if ($num > 0) { // If it ran OK, display the records.

	// Print the number of users:
	echo "<p>There are currently $num registered customers.</p><br />";

	// Table header.
	echo '<table align="center" cellspacing="3" cellpadding="3" width="75%" border=1>
	<tr>
	    <td><b>CustomerID</b></td>
	    <td><b>OldCustomerID</b></td>
		<td><b>CustomerName</b></td>
	    <td><b>Car</b></td>
		<td><b>Car Color</b></td>
	    <td><b>Computer</b></td>
		<td><b>IsLaptop</b></td>
	    <td><b>Race</b></td>
		<td><b>Residence</b></td>
	    <td><b>Birth Month</b></td>
	</tr>';
	
	/* Fetch and print all the records:
		fetch_array
		Fetch a result row as an associative, a numeric array, or both. 

		Parameters:
		type - one of MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH (default).
		By using the MYSQLI_ASSOC constant this function will behave identically to the mysqli_fetch_assoc(),
		while MYSQLI_NUM will behave identically to the mysqli_fetch_row() function. The final option MYSQLI_BOTH 
		will create a single array with the attributes of both. 

		Returns:
		a result row as an associative, a numeric array, or both or null if there are no more rows in the result set
	*/

	 WHILE($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
		echo '<tr><td>' . $row['CustomerID'] . '</td><td>' . $row['OldCustomerID'] . '</td><td>'  . $row['CustomerName'] . '</td><td>' . $row['Car'] . '</td><td>' . $row['Car Color'] . '</td><td>' . $row['Computer'] . '</td><td>' . $row['IsLaptop'] . '</td><td>' . $row['Race'] . '</td><td>' . $row['Residence'] . '</td><td>' . $row['Birth Month'] .  '</td></tr>' ;
	}

	echo '</table>'; // Close the table.
	
	mysqli_free_result ($r); // Free up the resources.	

} else { // If no records were returned.

	echo '<p class="error">There are currently no customers.</p>';

}

mysqli_close($dbc); // Close the database connection.

?>

Open in new window

0
Comment
Question by:corporateKeenan
  • 9
  • 6
  • 4
19 Comments
 
LVL 3

Expert Comment

by:WabbitSeason
ID: 33492789
The problem is that mysqli_query() returns a boolean instead of a result. This will probably be boolean false, because a succesful SELECT query would return a result. All this means that your query cannot be run. It might have a wrong syntax or the database might have changed making it fail somehow. Try calling mysqli_error() after mysqli_query to see what went wrong.
0
 

Author Comment

by:corporateKeenan
ID: 33492862
Is there a reason that a query will not run on the .php page but will run successfully with the MySQL Workbench or MS Access? Does my SELECT Query look right to you?
0
 
LVL 3

Expert Comment

by:WabbitSeason
ID: 33492914
Hmmm... The query looks alright to me, and if it works in the MySQL Workbench and/or MS Access it should be fine. The problem could be in $dbc not containing the proper MySQL connection, have you checked that?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:corporateKeenan
ID: 33492995
Ok I recoded the page and put the database connection syntax on the same code, but just as before I get the same result with the query not displaying inner joins. It works fine with the just the Select statement, From and Where clause but INNER JOINS will still not work. Has anybody ever heard of this?
<?php

$db_host = 'localhost';
$db_user = 'cge';
$db_pwd = '04252';

$database = 'cookk';
$table = 'customers';

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

	


// THIS QUERY WORKS PERFRECT IN MS ACCESS BUT NOT IN MySQL?!?!? 

$result = mysql_query("SELECT CustomerID, OldCustomerID, CONCAT_WS(', ', LastName, FirstName, MiddleName) AS 'Customer Name',CONCAT ((LEFT(FirstName, 1)), (LEFT(MiddleName, 1)), LastName) AS Username, zlu_Cars.Description AS Car, zlu_CarColor.Description AS 'Car Color', zlu_Computers.Description AS Computer, IsLaptop, zlu_Race.Description AS Race, zlu_Residence.Description AS Residence, zlu_BirthMonth.Description
FROM ((((((customers 
JOIN zlu_CarColor ON Customers.CarColorID = zlu_CarColor.CarColorID) JOIN zlu_Cars ON Customers.CarID = zlu_Cars.CarID) JOIN zlu_Computers ON Customers.ComputerID = zlu_Computers.ComputerID) JOIN zlu_Race ON Customers.RaceID = zlu_Race.RaceID) JOIN zlu_Residence ON Customers.ResidenceID = zlu_Residence.ResidenceID) JOIN zlu_BirthMonth ON Customers.BirthMonthID = zlu_BirthMonth.BirthMonthID)
WHERE Customers.CustomerID BETWEEN 500 and 600
ORDER BY LastName"); 




if (!$result) {
    die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);



echo "<table align='center' cellspacing='3' cellpadding='3' width='75%' border=1><tr>";

for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";

while($row = mysql_fetch_row($result))
{
    echo "<tr>";


    foreach($row as $cell)
        echo "<td>$cell</td>";

    echo "</tr>\n";
}
mysql_free_result($result)

?>

Open in new window

0
 
LVL 3

Expert Comment

by:WabbitSeason
ID: 33493216
It's strange indeed. Have you tried working in the mysql_error() function? If so, what does it say after the SELECT with the JOINS fails?
0
 

Author Comment

by:corporateKeenan
ID: 33493757
No luck so far and mysqli_error() function isn't isolating the issue....I'll see what tomorrow brings.
0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 33495248
Add this to the top of the PHP script:
error_reporting(E_ALL);

Change the code so you use either mysql or mysqli - but not both.

Change the query to something like what is shown in the code snippet so you can see the ACTUAL query string you are sending to MySQL or MySQLI.

Then post the new code and the output from the script.  Thanks, ~Ray




$sql = "SELECT CustomerID, OldCustomerID, CONCAT_WS(', ', LastName, FirstName, MiddleName) AS 'Customer Name',CONCAT ((LEFT(FirstName, 1)), (LEFT(MiddleName, 1)), LastName) AS Username, zlu_Cars.Description AS Car, zlu_CarColor.Description AS 'Car Color', zlu_Computers.Description AS Computer, IsLaptop, zlu_Race.Description AS Race, zlu_Residence.Description AS Residence, zlu_BirthMonth.Description
FROM ((((((customers 
JOIN zlu_CarColor ON Customers.CarColorID = zlu_CarColor.CarColorID) JOIN zlu_Cars ON Customers.CarID = zlu_Cars.CarID) JOIN zlu_Computers ON Customers.ComputerID = zlu_Computers.ComputerID) JOIN zlu_Race ON Customers.RaceID = zlu_Race.RaceID) JOIN zlu_Residence ON Customers.ResidenceID = zlu_Residence.ResidenceID) JOIN zlu_BirthMonth ON Customers.BirthMonthID = zlu_BirthMonth.BirthMonthID)
WHERE Customers.CustomerID BETWEEN 500 and 600
ORDER BY LastName"; 

echo PHP_EOL . $sql;
$res = mysql_query($sql);
if (!$res)
{
    echo mysql_errno();
    echo mysql_error();
}

Open in new window

0
 

Author Comment

by:corporateKeenan
ID: 33495265
@Ray

 Thanks for the code this is what displayed after it ran:

SELECT CustomerID, OldCustomerID, CONCAT_WS(', ', LastName, FirstName, MiddleName) AS 'Customer Name',CONCAT ((LEFT(FirstName, 1)), (LEFT(MiddleName, 1)), LastName) AS Username, zlu_Cars.Description AS Car, zlu_CarColor.Description AS 'Car Color', zlu_Computers.Description AS Computer, IsLaptop, zlu_Race.Description AS Race, zlu_Residence.Description AS Residence, zlu_BirthMonth.Description FROM ((((((customers JOIN zlu_CarColor ON Customers.CarColorID = zlu_CarColor.CarColorID) JOIN zlu_Cars ON Customers.CarID = zlu_Cars.CarID) JOIN zlu_Computers ON Customers.ComputerID = zlu_Computers.ComputerID) JOIN zlu_Race ON Customers.RaceID = zlu_Race.RaceID) JOIN zlu_Residence ON Customers.ResidenceID = zlu_Residence.ResidenceID) JOIN zlu_BirthMonth ON Customers.BirthMonthID = zlu_BirthMonth.BirthMonthID) WHERE Customers.CustomerID BETWEEN 500 and 600 ORDER BY LastName

1054Unknown column 'zlu_Cars.CarID' in 'on clause'

Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource in .......\view_customers.php on line 105

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in .....\view_customers.php on line 118

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in.........\view_customers.php on line 128
0
 
LVL 3

Expert Comment

by:WabbitSeason
ID: 33495311
Are you sure that the table zlu_Cars has a column called CarID (case sensivite, so carID or CarId and so on won't work) in the database cookk?
0
 

Author Comment

by:corporateKeenan
ID: 33495378
@WabbitSeason you were correct it was the spelling I didn't make the CarID and ComputerID plural. I'm still getting the errors Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource in .......\view_customers.php on line 105

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in .....\view_customers.php on line 118

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in.........\view_customers.php on line 128

 though but I think im getting closer . Here is the code as it stands now...

<?php
//.....
$database = 'cookek';
$table = 'customers';

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");


$result = "SELECT CustomerID, OldCustomerID, CONCAT_WS(', ', LastName, FirstName, MiddleName) AS 'Customer Name',CONCAT ((LEFT(FirstName, 1)), (LEFT(MiddleName, 1)), LastName) AS Username, zlu_Cars.Description AS Car, zlu_CarColor.Description AS 'Car Color', zlu_Computers.Description AS Computer, IsLaptop, zlu_Race.Description AS Race, zlu_Residence.Description AS Residence, zlu_BirthMonth.Description
FROM ((((((customers 
JOIN zlu_CarColor ON Customers.CarColorID = zlu_CarColor.CarColorID) JOIN zlu_Cars ON Customers.CarID = zlu_Cars.CarsID) JOIN zlu_Computers ON Customers.ComputerID = zlu_Computers.ComputersID) JOIN zlu_Race ON Customers.RaceID = zlu_Race.RaceID) JOIN zlu_Residence ON Customers.ResidenceID = zlu_Residence.ResidenceID) JOIN zlu_BirthMonth ON Customers.BirthMonthID = zlu_BirthMonth.BirthMonthID)
WHERE Customers.CustomerID BETWEEN 500 and 600
ORDER BY LastName"; 


/*echo PHP_EOL . $result;
$res = mysql_query($result);
if (!$res)
{
    echo mysql_errno();
    echo mysql_error();
}
*/
//

if (!$result) {
    die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);



echo "<table align='center' cellspacing='3' cellpadding='3' width='75%' border=1><tr>";

for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";

while($row = mysql_fetch_row($result))
{
    echo "<tr>";


    foreach($row as $cell)
        echo "<td>$cell</td>";

    echo "</tr>\n";
}
mysql_free_result($result)

?>

Open in new window

0
 
LVL 3

Expert Comment

by:WabbitSeason
ID: 33495386
You're mixing up $res and $result. Try passing $res instead of $result to mysql_num_fields and so on. And please, do yourself and other coders a favour by renaming $result to $sql, as the name is very misleading in its current state :-).
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 33495390
Check line 30 - looks like $res and $result are confused.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 33495396
A good practice for this kind of thing is to choose these names and not deviate from them:

$sql = SQL query string
$res = query results resource handle
$row = an array from the results set
$num = the number of rows in the results set

You can make variants on those, of course, but it's best to keep it simple so you don't confuse the variable names.
0
 

Author Closing Comment

by:corporateKeenan
ID: 33495403
Great find.
0
 

Author Comment

by:corporateKeenan
ID: 33495406
@WabbitSeason

 Thanks for your help as well I wish I could award 2 Accepted Solutions you really hung in there with me, thanks again!
0
 
LVL 3

Expert Comment

by:WabbitSeason
ID: 33495420
No problem, though AFAIK you can award an "Assisted Solution" to somebody who has been helpful, too.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 33495461
You can accept two solutions and split points:
http://www.experts-exchange.com/help.jsp#hs=8&hi=100

I think you can also ask a moderator to re-open the question if you want to make a change.  I would have no objection at all, since I think you got help from both of us on this Q.

Best to all, ~Ray
0
 

Author Comment

by:corporateKeenan
ID: 33495473
Will do Ray thanks...
0
 

Author Comment

by:corporateKeenan
ID: 33495489
Waiting on Mods to hit be back about the reopen request...
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question