• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12526
  • Last Modified:

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

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
corporateKeenan
Asked:
corporateKeenan
  • 9
  • 6
  • 4
1 Solution
 
WabbitSeasonCommented:
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
 
corporateKeenanAuthor Commented:
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
 
WabbitSeasonCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
corporateKeenanAuthor Commented:
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
 
WabbitSeasonCommented:
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
 
corporateKeenanAuthor Commented:
No luck so far and mysqli_error() function isn't isolating the issue....I'll see what tomorrow brings.
0
 
Ray PaseurCommented:
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
 
corporateKeenanAuthor Commented:
@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
 
WabbitSeasonCommented:
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
 
corporateKeenanAuthor Commented:
@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
 
WabbitSeasonCommented:
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
 
Ray PaseurCommented:
Check line 30 - looks like $res and $result are confused.
0
 
Ray PaseurCommented:
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
 
corporateKeenanAuthor Commented:
Great find.
0
 
corporateKeenanAuthor Commented:
@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
 
WabbitSeasonCommented:
No problem, though AFAIK you can award an "Assisted Solution" to somebody who has been helpful, too.
0
 
Ray PaseurCommented:
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
 
corporateKeenanAuthor Commented:
Will do Ray thanks...
0
 
corporateKeenanAuthor Commented:
Waiting on Mods to hit be back about the reopen request...
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now