counting rows

[TravisB]

I am trying to count rows from my movie database. For example, I would like to know how many titles start with the letter A, and then B, etc. The code is below and reads the first letter, but the counting is not working.

<?php
include 'db.php';

if (!($connection = @ mysql_connect($hostname, $username, $password)))
die("Could not connect to the database: " . mysql_error());

if (!mysql_select_db($dbname, $connection))
die ("Could not find database" . mysql_error());

$sql = "SELECT * FROM movie_id ORDER BY movie_title";

$rs = mysql_query($sql, $connection) or die(mysql_error());

$rowsFound = mysql_num_rows($rs);

if($rowsFound > 0)
{

$letter = "";
$letter2 = "";
$counter = 0;

while ($row = @ mysql_fetch_array($rs))
	{
	$counter++;

    //get first letter of title
$letter = substr($row["movie_title"],0,1);

   //see if they are the same letter
    if($letter != $letter2)
{
	echo "<br>" . $letter;
	echo " - " . $counter;
	$counter = 1;		
}

// make $letter2 equal $letter
	$letter2 = $letter;


}

?>

[matt_4013]

There doesn't seem to be anything fundamentally wrong with that. Just reading through the logic and it looks alright. What's it actually outputting?

[TravisB]

Here is the list of movies:
http://www.travisbarden.com/movies/list.php

and here is the counting program:
http://www.travisbarden.com/movies/count.php

[Daveyz1983]

Nottice that your "A" is always 1? No matter wad?
The error occurs here

$letter = substr($row["movie_title"],0,1);

   //see if they are the same letter
    if($letter != $letter2)
{
    echo "<br>" . $letter;
    echo " - " . $counter;
    $counter = 1;        
}

Notice that before that you declared your $letter2 as "". Therefore on the very first while loop. $letter = "A" (presumming the first string is Asadasdas) and $letter2 is "".

This cause the if else statement to be logically correct and therefore print it to be 1 and go on to other statements.

Also, in the if statement, you set $counter =1, which is incorrect, because at the start of the while loop, you already do a $counter++. Therefore causing the extra count in each group.

Try this

$letter = "";
$letter2 = "";
$counter = 0;
$loop = 1; //Use as test variable.

while ($row = @ mysql_fetch_array($rs))
    {
    $counter++;

    //get first letter of title
$letter = substr($row["movie_title"],0,1);


// This make sure you don't skip the first loop.
if ($loop == 1) {
   $letter2 = $letter;
   $loop = 0;
}

//see if they are the same letter
if($letter != $letter2) {
    echo "<br>" . $letter;
    echo " - " . $counter;
    $counter = 0;        
}

// make $letter2 equal $letter
    $letter2 = $letter;


}

[TravisB]

I am now using the code from Dave, but it is not working:

$letter = "";
$letter2 = "";
$counter = 0;
$loop = 1; //Use as test variable.

while ($row = @ mysql_fetch_array($rs))
    {
    $counter++;
    //get first letter of title
    $letter = substr($row["movie_title"],0,1);

// This make sure you don't skip the first loop.

if ($loop == 1) {
   $letter2 = $letter;
   $loop = 0;
}


   //see if they are the same letter

   if($letter != $letter2) {
   echo "<br>" . $letter;
   echo " - " . $counter;
   $counter = 0;        

   }

// make $letter2 equal $letter
    $letter2 = $letter;

}

I understand what you mean about the count being off from the first loop, but still the numbers that are showing up are nowhere near correct.

Here is the list of movies:
http://www.travisbarden.com/movies/list.php

and here is the counting program:
http://www.travisbarden.com/movies/count.php