Solved

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

Posted on 2010-08-21
19
9,499 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
 

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 108

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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 108

Expert Comment

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

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 108

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
This article discusses how to create an extensible mechanism for linked drop downs.
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now