Looping alphabet through php query

brad0525
brad0525 used Ask the Experts™
on
I am using the script below to run a query and return results. It works fine but I need to seperate the results by the first letter. I am able to succesfully do this but my script below requires me to run 26 querys, which I do not want to do. I would like to only 1 run query and instead of using

if ($letter=='A' || $letter=='a')

I need a way to make it loop through the alphabet..

Basically I need to return the information as I have it now, but I need the letter it is running the loop for to appear first, then all of the results...thanks
include ('db.php');

$query = "SELECT 
tris_categories_description.categories_name  AS categories_name,
tris_categories_description.categories_id AS categories_id
".

 "FROM tris_categories_description order by tris_categories_description.categories_name "; 
	


$result = mysql_query($query) or die(mysql_error());
// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result)){
	$categories_id = $row['categories_id'];
	$categories_name = $row['categories_name'];
	$letter =  substr($row['categories_name'], 0, 1); 
	if ($letter=='A' || $letter=='a')
	{
	echo "<br><br><b>$letter - $categories_name</b><br>";
	
	$query1 = "SELECT 
tris_products.products_id             AS  products_id,	
tris_products.products_image          AS  products_image,
tris_products_description.products_name 	      AS products_name,
tris_products_description.products_description    AS products_description
".

 "FROM tris_products 
 LEFT JOIN tris_products_description AS tris_products_description ON tris_products_description.products_id = tris_products.products_id
 WHERE tris_products.master_categories_id = '$categories_id' ORDER by tris_products_description.products_name  "; 
	


$result1 = mysql_query($query1) or die(mysql_error());
// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result1)){
$products_name = $row['products_name'];	
echo "$products_name<br>";	
}
	
	
	
	
	}else
	{
	echo "";
	}
}

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
One way to do this is run your query and cycle through all the results putting each one of them into a PHP array.
You can then run your checks or even resort or manipulate the array without needing to run further queries.
I presdume what your after is outputing your query with Alphabetical headings so if that's true the following will do what you need.
I have used the $MyArray in this example to show you what I mean by returning your query into an array but this can be done without having to do that if you just move the contents of the ForEach statement into your while loop and change the variables.
It firsts gets the data and puts it into the array.
It then cycles through each array element and checks if the First Letter of each category has changed. If it has it will then create a heading for the data. This will only work if your data is sorted by category name. If you need to change the sorting you must sort it first by category name and then by what ever else you need to sort it by. For example in your query "order by tris_categories_description.categories_name,tris_categories_description.categories_name". This would first sort by category and then by the name.
 

include ('db.php');

$query = "SELECT 
tris_categories_description.categories_name  AS categories_name,
tris_categories_description.categories_id AS categories_id
".

 "FROM tris_categories_description order by tris_categories_description.categories_name "; 

$MyArray=array(); # Define your array before using it.
$result1 = mysql_query($query1) or die(mysql_error());
// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result1))
   {
   $MyArray[]=$row; #Insert into my new array the contents of the row.
   }

print("<table>");
$OldHeading="";
foreach($MyArray as $row)
   {
   if($OldHeading<>substr(strtoupper($row['categories_name']),0,1))
      {
      $OldHeading=substr(strtoupper($row['categories_name']),0,1);
      print("<tr><td><b>Categories Beggining with ".$OldHeading."</b></td></tr>");
      }
   print("<tr><td>".$row['products_name']."</td></tr>");
   }
print("</table>");

Open in new window

Commented:
Sorry my comment above about sorting in your query should read as follows:
"ORDER BY tris_categories_description.categories_name,tris_categories_description.SomeOtherFieldName"
 

Commented:
You may try the following. In this case you let to the DBMS do the job...
include ('db.php');

$query = "SELECT tcd.categories_name, tcd.categories_id
tp.products_id, tp.products_image, 
tpd.products_name, tpd.products_description
FROM tris_categories_description AS tcd 
LEFT JOIN tris_products AS tp, tris_products_description AS tpd
ON tp.master_categories_id=tcd.categories_id AND tpd.products_id=tp.products_id
ORDER BY tcd.categories_name, tpd.products_name;"; 
  
$lastCategory='';      
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
   $firstLetter =  strToUpper(substr($row['categories_name'], 0, 1)); 
   if($row['categories_name']!=$lastCategory){
      echo '<br><br><b><u>',$firstLetter,' - ',strToUpper($row['categories_name']),'</u></b><br>';
      $lastCategory=$row['categories_name'];
   }             
   echo '<b>',$row['products_name'],'</b><br>';
   echo $row['products_description'],'<br>';
}

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial