repeating query

[guhyaraja]

Hi

I got a code with some while loops where a query is being run. I'm using a function that I call DoSql2() which runs a mysql query and returns the result. Now I'm repeating this query several time in the loops, but I'm sure that this could be done with just one call. But I can't get it to work...

any ideas?

<?php

$result1= DoSql2($banner_report1);
while ($row = mysql_fetch_array($result1))
{
	if ($row[3] == "1"){
	 $level2 = $row[0];
	 echo "\t<tr style='background-color: #E8E8E8'>";
	 echo "<td width='200'>";
	 echo "<b>" . strtolower($row['cat_name']) . "</b>";
	 echo "</td>";
	 echo "<td>";
		 echo "<a href='#'";
			 if (!$row['code']){
				 echo " style='color: red'>" . $row['prod_name'] . "</a>";
			 }else{
				 echo ">" . $row['prod_name'] . "</a>";
			 }	 
	 echo "</td>";
	 echo "</tr>\n";

$result2 = DoSql2($banner_report1);
while ($row2 = mysql_fetch_array($result2))
{

if ($row2[2] == $row[0]) {
 echo "\t<tr style='background-color: #F0F0F0'>";
 echo "<td>";
 echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
 echo strtolower($row2['cat_name']);
 echo "</td>";
 echo "<td>";
	 echo "<a href='#'";
		 if (!$row2['code']){
			 echo " style='color: red'>" . $row2['prod_name'] . "</a>";
		 }else{
			 echo ">" . $row2['prod_name'] . "</a>";
		 }
 echo "</td>";
 echo "</tr>\n";

$result3 = DoSql2($banner_report1);
while ($row3 = mysql_fetch_array($result3))
{
if ($row3[2] == $row2[0]) {
 echo "\t<tr style='background-color: #F8F8F8'>";
echo "<td>";
echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
echo strtolower($row3['cat_name']);
echo "</td>";
echo "<td>";
	 echo "<a href='#'";
		 if (!$row3['code']){
			 echo " style='color: red'>" . $row3['prod_name'] . "</a>";
		 }else{
			 echo ">" . $row3['prod_name'] . "</a>";
		 }
echo "</td>";
echo "</tr>\n";



				}
			}
		}
	}
}	
}

?>

[laserlight]

Well, perhaps it could be done with just one query, but what are you trying to do? For example, you might want to tell us the relevant database schema and the queries that you are currently using, and what exactly you want to select.

[guhyaraja]

ok here is the query

$banner_report1 = "
SELECT categories.cat_id, categories.cat_name, categories.cat_parent, categories.cat_level, categories.cat_active, products.prod_id, products.prod_name, cat_products.id, test_codes.code
FROM categories
LEFT JOIN cat_products ON ( categories.cat_id = cat_products.cat_id )
LEFT JOIN products ON ( cat_products.prod_id = products.prod_id )
LEFT JOIN test_codes ON ( test_codes.code = products.prod_name )
ORDER BY cat_id ASC";

basically I got a categories table with a id - name - parent( id of parent category) etc

I'm displaying them like this

i.e.

chickens---------(id =1)
---eggs----------(id =2 & parent =1)
------green -----(id =3 & parent =2)
------red---------(id =4 & parent =2)
------spotted---(id =5 & parent =2)
---feathers-----(id =6 & parent =1)
------fluffy-------(id =7 & parent =6)

currently I'm repeating the query several times for every level of the categories...

hope this made it a bit clearer...

[laserlight]

Take a look at my answer in this thread on gouping a result. You can use a similiar idea:
1. Select all the second level categories (e.g., those with parent = 1), ordered by some appropriate attribute (e.g., name or id).
2. Select all the third level categories. This means doing a "SELECT ... WHERE parent IN (second_level_category_id_list) ORDER BY ...". Now, first order by the chosen parent attribute, then order by the category's own appropriate attribute.
3. Until there is an empty result set, repeat #2.

Assuming that the categories form a tree (or at least contain no cycles), this algorithm should terminate. The number of queries required is equal to the height of the tree.

To actually print the tree, you could try the trick I mentioned in that other thread, but it may need tweaking to work (if it is even possible, but I think it is) since the depth of the tree is unknown. Alternatively, you can actually construct a tree using a multi-dimensional array and then traverse it depth first.