Breaking Up Alphabetical Sorts into Blocks

[jrotunda85]

I have a large amount of items stored in my DB (around 3,000 total and growing) and I have a list of them sorted alphabetically that displays on a page. I was interested; however, in having the items display in blocks instead that look something like:

A
[All items that start with the letter A]

B
[All items that start with the letter B]

C
[All items that start with the letter C]

How would I go about doing this in PHP or is it even possible?

[diafol]

Ordinarily I'd ask to see some code, but I know you have a go. So...

$orig = "";
while ($data = ....){
  $first_letter = strtoupper(substr(trim($data['string'],0,1)));
  if($orig != $first_letter){
    echo "\n<h3>$first_letter</h3>";
    $orig = $first_letter;
  }
  echo "\n<p>$data['string']</p>";
}

Not tested. BTW - this is for A-Z characters. If you accept accented characters, you'll need to use mb string functions.

[jrotunda85]

Ordinarily I'd ask to see some code, but I know you have a go. So...

$orig = "";
while ($data = ....){
  $first_letter = strtoupper(substr(trim($data['string'],0,1)));
  if($orig != $first_letter){
    echo "\n<h3>$first_letter</h3>";
    $orig = $first_letter;
  }
  echo "<p>$data['string']</p>";
}

Not tested.

Thanks for the help - quick question though, would this require me setting the $first_letter variable each time, so in essence having to set it 26 times? :?:

[diafol]

No never. You never set it - it's taken from your records. So if no 'F' words are stored, the 'F' title won't appear. You just let it run. Put your feet up.

You need to ORDER your SQL though:

"..... ORDER BY string";

[jrotunda85]

Ardav,

I just got a chance to play around with this code today; however, I'm not sure how to incoporate what I'm already doing. Here's my current code I'm usuing (keep in mind that is just a big list, with no seperation between the letters).

<?PHP

include 'dbconnection.php';

$SQL = "Select a.CigarID, b.Description, a.CName from cigar a
LEFT JOIN cd_manufacturer b ON a.Manufacturer = b.Code
ORDER BY b.Description, a.CName ASC";
$result = mysql_query($SQL);
$result2 = mysql_fetch_array(mysql_query($SQL));
 
	if (mysql_num_rows($result) > 0) { 
		 while ($row = mysql_fetch_row($result)) {
			echo("<li><a href=\"cigar.php?id=$row[0]\">$row[1] $row[2]</a></li>");
		 }
	}
	else { 
      echo "No rows found!"; 
	} 
	mysql_free_result($result);
	
	?>

I'm assuming (gulp) that this my $SQL statement should go into the $data part but it's not displaying a result. Any ideas? :confused:

[diafol]

<?PHP

include 'dbconnection.php';

$SQL = "Select a.CigarID, b.Description, a.CName from cigar a
LEFT JOIN cd_manufacturer b ON a.Manufacturer = b.Code
ORDER BY b.Description, a.CName ASC";
$result = mysql_query($SQL);
$orig = "";
$result2 = mysql_fetch_array(mysql_query($result));
 
	if (mysql_num_rows($result) > 0) { 
		 while ($row = mysql_fetch_row($result)) {
                        $first_letter = strtoupper(substr(trim($row[2],0,1)));
                        if($orig != $first_letter){
                              echo "\n<h3>$first_letter</h3>";
                              $orig = $first_letter;
                        }
			echo("<li><a href=\"cigar.php?id=$row[0]\">$row[1] $row[2]</a></li>");
		 }
	}else { 
                echo "No rows found!"; 
	} 
	mysql_free_result($result);
	
	?>

Maybe?

[jrotunda85]

Alright, I've tried placing the code you posted and I'm getting the following messages:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in blah/blah/blah/brandlist.php on line 113

Warning: Wrong parameter count for trim() in blah/blah/blah/brandlist.php on line 117

Warning: Wrong parameter count for substr() in blah/blah/blah/brandlist.php on line 117

That the last two ones (trim and substr) are appearing before EVERY item whereas the array is only showing once.

Line 113

$result2 = mysql_fetch_array(mysql_query($result));

Line 117

$first_letter = strtoupper(substr(trim($row[2],0,1)));

Any ideas what's going on?

[diafol]

include 'dbconnection.php';
 
$result = mysql_query("Select a.CigarID, b.Description, a.CName from cigar a
LEFT JOIN cd_manufacturer b ON a.Manufacturer = b.Code
ORDER BY b.Description, a.CName ASC");
$orig = "";
 
	if (mysql_num_rows($result) > 0) { 
		 while ($row = mysql_fetch_row($result)) {
                        $first_letter = strtoupper(substr(trim($row[2]),0,1));
                        if($orig != $first_letter){
                              echo "\n<h3>$first_letter</h3>";
                              $orig = $first_letter;
                        }
			echo "<li><a href=\"cigar.php?id={$row[0]}\">{$row[1]} {$row[2]}</a></li>";
		 }
	}else { 
                echo "No rows found!"; 
	} 
	mysql_free_result($result);
 
	?>

Any better?

[jrotunda85]

No more error messages but it's displaying the list in a crazy order. From what I can tell, it's ordering it by the CName column instead of the Description column it should be per the query. In either case, it's not in alphabetical order (even if you were to sort by CName).

Also, one side issue I thought of, will this display numbers? So if I have a cigar that's 601 Blah, will it display???

Thank you so much for your help man, it's been a tremendous help! :icon_mrgreen:

[jrotunda85]

You know what I just realized, it is sorting by the Description column in this result set, it's just the letter up at the top that's wrong. It was calling the wrong part of the array which was a simple fix! Thank you again for the help, you're my hero! :cool:

[diafol]

You're welcome.