Counting rows

[Patrick Taylor]

I have this query:

$query = "SELECT DATE_FORMAT(info_date, '%M %d %Y, %W') AS info_date_formatted, COUNT(*) AS dailies FROM userdata GROUP BY info_date_formatted ORDER BY UNIX_TIMESTAMP(info_date) DESC LIMIT $offset,$page_size";

It returns a set of rows with my daily hits. Each day that passes, an extra row is added with that day's hits.

However, I also have a pager in my PHP. It can be set to create new pages when the number of rows reaches a number. Thing is, I can't work out how to do another query to count the rows - the number I need for the pager.

Normally when I use this pager I just repeat the main query followed by this:

$result = @mysql_query($query);
if ($row = mysql_fetch_row($result)) $row_count = $row[0];
else $row_count = 0;

... but in this case when I print $row_count I just get a date.

Help would be appreciated.

[coopster]

$result = @mysql_query($query);
if ($row = mysql_fetch_row($result)) $row_count = $row[0];
else $row_count = 0;

Keep in mind that $row is going to contain an array of columns that was fetched by your mysql_fetch_row() statement, indexed numerically. And, since you are only returning two columns, the formatted date and a COUNT(*), $row[0] is going to be holding the date.

There are a couple of ways you could do this, one of the simpler was is to use your own row counter inside your while loop.

$result = @mysql_query($query); 
$row_count = 0; // initialize 
if ($row = mysql_fetch_row($result)) { 
 ++$row_count; 
} else { 
 $row_count = 0; 
}

[Patrick Taylor]

Thanks. The rows are now being counted with:

// Get hits grouped by date - %D to %d
$query = "SELECT DATE_FORMAT(info_date, '%M %d %Y, %W') AS info_date_formatted, COUNT(*) AS dailies FROM userdata GROUP BY info_date_formatted ORDER BY UNIX_TIMESTAMP(info_date) DESC LIMIT $offset,$page_size";

// Run the query.
$result = mysql_query($query);
$row_count = 0; // initialize

// Display dailies
echo '<table cellspacing="0" cellpadding="0" width="100%">';
while ($row = mysql_fetch_assoc($result)) {
++$row_count;
echo '<tr><td width="200" valign="top"><p class="text1a">' . $row['info_date_formatted'] . ':</p></td><td width="70" valign="top"><p class="text1a">' . $row['dailies'] . '</p></td><td width=" " valign="top" bgcolor="#EBEBEB"><p class="bar"><img src="bar.jpg" height="11" width="' . (($row['dailies'] / $total) * 100) . '%" /></td></tr>';
}
echo '</table>';

// Print row count
echo '<p class="text1a">' . $row_count . '<br /></p>';

Apologies for all the code.

The $row_count variable is being correctly printed. However, the pager is no longer working, though it seems to me it should be. Before the above code I have:

// Set the page size variable.
$page_size = 20;

// Create the pager.
if ($page) {
if (!is_numeric($page)) $page = 0;
} else $page = 0;

$offset = $page * $page_size;

// Now redundant query to count how many rows there are.
// $query = "SELECT COUNT(*) etc etc - this query is now redundant";
// $result = @mysql_query($query);
// if ($row = mysql_fetch_row($result)) $row_count = $row[0];
// else $row_count = 0;

and below it is:

<?php
// Page bottom navigation.
echo '<p class="center"><b>';
if ($page > 0) {

?>
<a href="dailies.php?page=<?=$page-1?>" class="links">Previous page</a> </b>¦<b>
<?php

}

if ($row_count > ($page_size * ($page+1))) {?>
<a href="dailies.php?page=<?=$page+1?>" class="links">Next page</a> </b>¦<b>
<? }?>
<?php

// Add the pager.
$j = ceil($row_count / $page_size);
for ($i=0; $i<$j; $i++) {
if ($i==$page) echo ($i+1) . "&nbsp;&nbsp;";
else echo '<a href="dailies.php?page=' . $i . '" class="links">' . ($i+1) . "</a>&nbsp;&nbsp;";
}
if ($j == 0) {
echo "1";
}
echo "</b></p>";

// Close the database connection.
mysql_close();

?>

The only thing I can see not getting picked up is the line "if ($row_count > ($page_size * ($page+1)))". 47 is the actual row count, 20 is the set page size, so I can't see where the problem is.

I'll keep trying! Thanks again... I learnt something new.

[ergophobe]

You need to establish your row count with a different query. You are using the LIMIT clause in your SELECT, which means that the # of rows will always be equal to the page size (except on the last page of the result set), thus, no next page. So to get row count for the purposes of paging, you need to find out the size of your entire result set. So the query that is "now redundant" actually isn't

$q = "SELECT info_date FROM userdata";
$r = mysql_query($q);
$total_rows = mysql_num_rows($r);

- OR -

$q = "SELECT COUNT(*) as dailies FROM user_data";
$r = mysql_query($q);
$row = mysql_fetch_assoc($r);
$total_rows = $row['dailies'];

What is redundant id the count you have in your select. For any page other than the last one, you know that the count will equal the $page_size in you LIMIT clause.

Cheers,

Tom

[Patrick Taylor]

Thanks, and I see what you mean. I've now reintroduced the first query as:

$query = "SELECT COUNT(*) as dailies FROM userdata";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$row_count = $row['dailies'];

Unfortunately it's returned the same row count as the number of results: 19,338 as it happens! (the pager seemed to enjoy it though.) I need $row_count to be the number of rows grouped as "dailies" like the main query. Maybe I could use the main query with coopsters suggestions added. I'll try it.

<added>That worked. Thanks to you both.</added>

[ergophobe]

Sorry, I didn't read the beginning carefully enough.