Getting number of rows for use in FOR loop

PHP
1

Is it not possible to use PDO to get the same thing as MySQL_num_rows() does? I am sorry if this has been asked before, but I am just now starting to learn PDO and it’s been a challenge.

 $dsn = sprintf('%s:host=%s;dbname=%s','mysql','localhost','sports');

  $pdo = new PDO($dsn,'','');
  $pdo->setAttribute(PDO::ATTR_ERRMODE,  PDO::ERRMODE_EXCEPTION);
  $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);

  $query = "SELECT * FROM sp_users";
  $report = mysql_query( $query . " ORDER BY id");
	if (!$report) {
	  echo "</TABLE>";
		echo "<P>Error retrieving listing from database!<BR>" .
		      "Error: " . mysql_error();
		exit();
	}

  $numofrows = mysql_num_rows($report);
	
  for($i = 0; $i < $numofrows; $i++) {
    $info = mysql_fetch_array($report);
    if($i % 2) { //this means if there is a remainder
      echo "  <TR BGCOLOR=\"#EEEEEE\">";
    } else { //if there isn't a remainder we will do the else
      echo "  <TR BGCOLOR=\"#FFFFFF\">";
    }

    echo "<TD>".$info['id']."</TD>";
    echo "<TD>".$info['username']."</TD>";
2

You can do it one of two ways:

  1. Loop through the PDO object
$report = $db->executeQuery($query);
while ($info in $report->fetch(PDO::FETCH_ASSOC)) {
  // php code goes here 
}

or use fetchAll() and process the array

$report = $db->executeQuery($query);
$allRows = $report->fetchAll();

foreach ($allRows As $info) {
  // php code goes here 
}

Which you use is up to you. Most cases, I personally would use fetchAll().

3

(Though, for the record, this is little to do with counting the number of rows, and more to do with moving away from unneeded FOR structures in favor of WHILE/FOREACH.)

Additionally, i’d suggest moving your row colors into CSS (even inline CSS if need be).

4

Oh my gosh. This is tough. I thought I was about finished with my code, only to find out each day I am falling further and further behind.

In the first reply, do I just remove my $report and replace it with Dave Maxwell’s, or is there a little more to it?

Secondly, I have tried CSS and can’t help but fall back on tables. How would you go about removing the FOR structure in my code and replacing it with WHILE?

I have been coding off and on for years, a month here and a month there. I get burned out quick when I run into the changes that have been brought forth today.

5

Welcome to the world of IT. You walk away for a while, and you fall behind…

I pretty much gave you exactly what you needed. Basically, here is your code sample with my code in it’s place.

$dsn = sprintf('%s:host=%s;dbname=%s','mysql','localhost','sports');

$query = "SELECT * FROM sp_users ORDER BY id";

$pdo = new PDO($dsn,'','');
$pdo->setAttribute(PDO::ATTR_ERRMODE,  PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);
  
$report = $pdo->executeQuery($query);
  
$allRows = $report->fetchAll();

foreach ($allRows As $info) {
    echo "<TR>";
    echo "<TD>".$info['id']."</TD>";
    echo "<TD>".$info['username']."</TD>";
	:	// rest of your code goes here...
}

Has nothing to do with the tables - he’s talking about the 1 % 2 code and the if statement. You can do the same thing by adding these two lines to your css:

tr:nth-child(even) {background: #EEE}
tr:nth-child(odd) {background: #FFF}

See code above…

6

What am I missing?
I try to run it and I get the error:

Fatal error: Call to undefined method PDO::executeQuery() in C:\Apache24\htdocs\2014\admin\admins_list.php on line 58

Line 58 is:

 $query = "SELECT * FROM sp_users ORDER BY id";
  $report = $pdo->executeQuery($query);  <--Line 58
7

It’s just as the error states, you’re calling an undefined function on your PDO object. The PDO library does not define a method called executeQuery - it does, however, define a query function which seems suitable for your situation.