I keep on getting this error "Warning: mysql_fetch_row() expects parameter 1 to be resource, boolean given in" from both of my while loop conditions. Can anyone review my code and let me know what I'm doing wrong?

<html>
<head>
	<title>Task # 4 - Test Page</title>
</head>
<body>

<?php

	$link = mysql_connect('127.0.0.1');
	
	if (!$link)
  	{
  		die('Could not connect: ' . mysql_error());
  	}// open connection


	mysql_select_db('task4', $link); // database selection
	
	
	$totalTrainees = 0;
	
	$q1 = mysql_query("select trainerID from trainer", $link); //get all trainerID 
	
	
			while($row=mysql_fetch_row($q1))
			{
				echo $row[0]." ";
		
				$v1 = "select courseID, count(courseID) as totalCourses from courses where trainerID=$row[0]";
		
				$q2 = mysql_query($v1);
		
				while($row1=mysql_fetch_row($q2))
				{
					echo $row1[0]." ";
			
					$v2 = "select count(attendanceStatus) from trainee where $row1";
			
					$totalTrainees += $totalTrainees;
			
				}//end while 2
		
			echo $totalTrainees; 
		
		}//end while 1
					
	
	
	
	
		
	mysql_close($link);	//close connection to db 
		
?>
</body>
</html>
Member Avatar for diafol
$link = mysql_connect('127.0.0.1');

You're actually able to connect with that?? OK..

As you're using mysql instead of mysqli, you don't need to include the $link:

$q1 = mysql_query("select trainerID from trainer");

Akin to your previous threads on this topic, I'm struggling to see the logic of your DB structure.

while($row=mysql_fetch_row($q1))

is causing a problem?

You can test for existing data:

echo "num_rows = " . mysql_num_rows($q1) . "<br />";

before the while loop.

Surprisingly, that is the only way that works for for me in terms of connection.
As for the structure, this is how the databases of the system my work is based on is designed.
I will try your suggestion.

I have added your line of code and it turns out that the first query is empty! :s

Member Avatar for diafol

OK, empty isn't the same as bad query. If the query was bogus (bad table name or fields name), you'd get an error message on the mysql_num_rows line. The fact that it's empty, means that nothing is returned, so that all subsequent calls (where you use $row{0}) will throw a problem.

OK, so the problem seems to be with your data. Do you have any data in the table?

Yes. There are no null values in any of the tables.

Member Avatar for diafol

$link = mysql_connect('127.0.0.1');

THis makes me wonder ... You're on localhost (=127.0.0.1), but require no user nor password. Do you connect via phpmyadmin in the same way?

Yes. Why is so strange that it works?

Member Avatar for diafol

Most setups have a username="root"/password="" as default.

Anyway, there's data in the table (verified by phpmyadmin I take it).

Run the query in the SQL box in phpmyadmin. Does it give a result?

Member Avatar for diafol

Hmm. Weird.

how about:

$q1 = mysql_query("select * from trainer");
echo "<br />rows = " . mysql_num_rows($q1) . "<br />";

And you're sure about database is 'task4'?

Yes, the database name is correct (task4) and the error is still there.

Member Avatar for diafol

Sorry NC, I'm stumped. Anybody else?

Thank you for your help, anyways. :)

I ran into this awhile back, the aha moment for me was when I performed a var_dump on the resource returned by the mysql_fetch_row() command.

$query = "SELECT * FROM $tablename";

$dbq = mysql_query($query) or die ( "Query failed on table: $tablename <br /><br />".mysql_error() );

var_dump ( $dbq );

resource(3) of type (mysql result)


So basically I had a pointer to the array, not the actual data of the array, when I later ran my mysql_fetch_row($dbq) command.

To verify that I was on the right track, I put in the following print stmt and when it successfully printed data, I knew I was on the right track...

$data = mysql_fetch_row($dbq);
print $data[0]."-".$data[1]."-".$data[2]."-".$data[3]."<br />";

Of course the obvious issue is that I could not always guarantee that my table would only have 4 fields to a row could I. Thankfully mysql_num_fields($dbq); solves that problem.

I don't have time to specifically address your code at the moment, will try to come back online later tonight or tomorrow and see if you responded that this helped you to fix your problem or not.

Instead I offer a code snippet that I know worked for me that should help you work out the logic in your two while loops. I just happened to use two for loops in my old example. I am sure you can work out the results for your while loop.


I hope this helps you.

<?php

$localhost='localhost:3306'; 
$user='tester';
$password='password';
$database='Queue1';
$tablename='Store';

// connect and use the database.
$dbh = mysql_connect( $localhost, $user, $password ) or die ( "Connection Error: <br /><br />".mysql_error() ); 

$query = "USE $database";
$dbc = mysql_query( $query ) or die ( "Unable to USE database: $database <br /><br />".mysql_error() );

//formulate the query
$query = "SELECT * FROM $tablename";

//return MySQL resource information
$dbq = mysql_query($query) or die ( "Query failed on table: $tablename <br /><br />".mysql_error() );

// get count from MySQL result resource, how many rows of data where returned?
$total = mysql_num_rows($dbq);

// Get the number of fields in this database
$num_fields = mysql_num_fields($dbq);


for ($i=0; $i<$total; $i++ ) {
    $data = mysql_fetch_row($dbq);
    for ($j=0; $j<$num_fields; $j++) {
        print $data[$j];  // prints each column of row
    }
    print "<br />";
}
?>

Good luck and I will check back later . . .

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.