control break

[PaulaRun]

I am trying to print the category name and subcategory name out for each category and subcategory when returning rows from a database search. I can only get it to work for the first category and sub category. I know it is something with the way I'm going through my for loop, but I just can't figure it out. Am I checking for the field value correctly?

THANKS in advance!

<script language="php">
if($citysrch == "Go") {
	include "conndb.php";
	$srch = "SELECT cp.coupons_id, cp.coupons_business_name, cp.coupon_path,
					c.city_name, c.city_town,
					cg.cat_name,  

					sc.sub_name
			 FROM 	coupons cp, categories cg, city c, sub_categories sc
			 WHERE 	cp.city_id = $city
			 AND	cp.city_id = c.city_id
			 AND	cp.cat_id = cg.cat_id
			 AND	cp.sub_id = sc.sub_id
			 ORDER BY cp.city_id,
			 		cp.cat_id,
			 		cp.sub_id,
			 		cp.coupons_business_name
			 LIMIT 	0, 30"; 
	$query = mysql_query($srch) or die ("Could not execute search 4: &nbsp; $query. " . mysql_error()); 
	$num=mysql_num_rows($query); 
	$category = 0;
	$subcat = 0;
	if ($num <=0){
		echo "<font face=\"Arial\" color=\"#800000\" size=\"3\"><strong>There are currently no coupons for the city you selected.  Please check back or try another city.</strong>";
		return;
	}	
	echo "<table border=\"0\" cellspacing=\"2\" style=\"border-collapse: collapse\" width=\"100%\" id=\"AutoNumber2\">";
	for ($i=0; $i<$num; $i++){
		$row = mysql_fetch_array($query);
		if ($category !== cg.cat_id){
			$category = cg.cat_id;	
			echo "<tr>";
    			echo "<td width=\"41%\"><strong><font face=\"Arial\" color=\"#800000\" size=\"2\">".stripslashes($row["cat_name"])."</td>";
 			echo "</tr>";
 		}
		if ($subcat !== sc.sub_id){
			$subcat = sc.sub_id;
			echo "<tr>";
    			echo "<td width=\"41%\"><strong><font face=\"Arial\" color=\"#800000\" size=\"2\">&nbsp;&nbsp;".stripslashes($row["sub_name"])."</td>";
 			echo "</tr>";
 		}

	echo "<tr>";
		echo "<td width=\"70%\"><strong><font face=\"Arial\" color=\"#0000FF\" size=\"2\">&nbsp;&nbsp;&nbsp;".stripslashes($row["coupons_business_name"])."</td>";
		echo "<td width=\"30%\"><a href=\"javascript:Start('".stripslashes($row["coupon_path"])."')\">Get Coupon</a></font></strong></td>";
	echo "</tr>";
}	
echo "</table>";
}	
</script>

[tsinka]

Hi,

where do you set cg.cat_id ? Your query doesn't select it and you do not access the cat_id of the result set.

You should extend your SQL query to select cg.cat_id (e.g. SELECT ... cg.cat_id as cat_id) and change

cg.cat_id

in your loops to $row['cat_id']

same for sc.sub_id

Inside the for loop you should use mysql_fetch_row because mysql_fetch_row fetches only one row. mysql_fetch_array fetches the complete result set in one array. So you could either use the for loop with mysql_fetch_row or use mysql_fetch_array to fetch the complete result set once and walk through that array.

I would change for ($i=0; $i<$num; $i++){ to
while ($row = mysql_fetch_row($query) {
and remove the $row = mysql_fetch_array($query);
line.

I think the code can be optimized, you don't need the mysql_num_rows function at all.

[PaulaRun]

Thanks, tsinka. I have tried many versions of "while ($row = mysql_fetch_row($query) { " and I just can't get it to work.

If I don't use $num=mysql_num_rows($query), How can I do the for loop without having the value of $num?

[PaulaRun]

Here is my code with the changes - I get parse errors.

<script language="php">
if($citysrch == "Go") {
	include "conndb.php";
	$srch = "SELECT cp.coupons_id, cp.coupons_business_name, cp.coupon_path,
					c.city_name, c.city_town, cg.cat_id, sg.sub_id,
					cg.cat_name,  

					sc.sub_name
			 FROM 	coupons cp, categories cg, city c, sub_categories sc
			 WHERE 	cp.city_id = $city
			 AND	cp.city_id = c.city_id
			 AND	cp.cat_id = cg.cat_id
			 AND	cp.sub_id = sc.sub_id
			 ORDER BY cp.city_id,
			 		cp.cat_id,
			 		cp.sub_id,
			 		cp.coupons_business_name
			 LIMIT 	0, 30"; 
	$query = mysql_query($srch) or die ("Could not execute search 4: &nbsp; $query. " . mysql_error()); 
	$num=mysql_num_rows($query); 
	$category = 0;
	$subcat = 0;
	if ($num <=0){
		echo "<font face=\"Arial\" color=\"#800000\" size=\"3\"><strong>There are currently no coupons for the city you selected.  Please check back or try another city.</strong>";
		return;
	}	
	echo "<table border=\"0\" cellspacing=\"2\" style=\"border-collapse: collapse\" width=\"100%\" id=\"AutoNumber2\">";
	for ($i=0; $i<$num; $i++){
		while ($row = mysql_fetch_row($query) { 

//		if ($category !== cg.cat_id){
//			$category = cg.cat_id;	
//			echo "<tr>";
//    			echo "<td width=\"41%\"><strong><font face=\"Arial\" color=\"#800000\" size=\"2\">".stripslashes($row["cat_name"])."</td>";
// 			echo "</tr>";
// 		}
//		if ($subcat !== sc.sub_id){
//			$subcat = sc.sub_id;
//			echo "<tr>";
//    			echo "<td width=\"41%\"><strong><font face=\"Arial\" color=\"#800000\" size=\"2\">&nbsp;&nbsp;".stripslashes($row["sub_name"])."</td>";
// 			echo "</tr>";
// 		}

	echo "<tr>";
		echo "<td width=\"70%\"><strong><font face=\"Arial\" color=\"#0000FF\" size=\"2\">&nbsp;&nbsp;&nbsp;".stripslashes($row["coupons_business_name"])."</td>";
		echo "<td width=\"30%\"><a href=\"javascript:Start('".stripslashes($row["coupon_path"])."')\">Get Coupon</a></font></strong></td>";
	echo "</tr>";
	}		
}
echo "</table>";
}	
     </script>

[tsinka]

Hi,

if you use mysql_fetch_array instead of mysql_fetch_row you can get the row count by count($array).

Ok, mysql_fetch_row might be better if you have a large amount of records because it would be more expensive to walk through a very large array.

did you get any errors by using while ($row....) { ?

[tsinka]

Oops,

I posted before reading the new code snipplet 🙁

for ($i=0; $i<$num; $i++){
while ($row = mysql_fetch_row($query) {

Remove the for loop because your're already walking through the record set with while ...

There is a ) missing it should read

while ($row = mysql_fetch_row($query)){

[PaulaRun]

Thanks for looking at this tsinka. I still have a way to go on this.