Showing data in specific order

[freakystreak]

I am stuck and would appreciate any help with this problem. I would like to display my sub categories and my products as such;

Subcategory1
Product1
Product2

Subcategory2
Product1
Product2

So far, my code displays it as this;

Subcatergory1
Subcategory2

Product1
Product2

Any ideas on how I can acheive this or any tutorials which show you how?

[forkaya]

This is not enough info to be able to help you... Please post your code.

[freakystreak]

Sorry, here's the code. I have a routine which keep the column count to 4 and then creates a new row if it passes the maximum amount per row.

<?php

$query = mysql_query("SELECT subcatName FROM subcategory where cid='$categorytarget' ORDER BY subcatName ASC") or die("SELECT error: " . mysql_error());
$num_rows = mysql_num_rows($query);
$count = $num_rows;

echo '<table cellspacing="5" cellpadding="5">';

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

$i = $row['subcatName'];
echo '<td>' . $i . '</td></tr>';

}


$result = mysql_query("SELECT merchant_name, thumb_nail, subcatName FROM merchant, subcategory WHERE merchant.msubcid = subcategory.subcid AND cid= '$categorytarget'") or die("SELECT error: " . mysql_error());
$num_rows = mysql_num_rows($result);
//$result = mysql_query("SELECT subcatName FROM subcategory where cid='$categorytarget' ORDER BY subcatName ASC") or die("SELECT error: " . mysql_error());
$sql = mysql_query($result);

$columns_counter=4;


echo '<tr>';


while($row = mysql_fetch_array($result))
{
//echo '<td>' . $row['subcatName'] . '</td></tr>';
echo '<td align = "center">';
echo '<img src='.$row['thumb_nail'].' /><br />' . ($row['merchant_name']);
//echo '</td><td>';
echo '</td>';
$columns_counter--;
if(!$columns_counter)
{
echo '</tr><tr>';
$columns_counter=4;
}
}
echo '</tr></table>';

//echo '</tr></table>';
//echo show_merchant_list ($categorytarget);
?>

[r937]

that query inside the while loop should be eliminated

a single join query is a lot more efficient

sorry to butt in on your thread

[freakystreak]

No problem with butting in. Thanks for the suggestion. I think the problem may be with the way my database has been set out. I have got a few ideas to try but if anyone else has any thoughts then please give them to me.

[freakystreak]

OK, I give in, I need help again. Can anyone suggest a script or tutorial where I can achieve the result I want. I believe that the way to go is via an Adjacency List but how would I go about creating the kind of output I want. Namely printing the products out that belong to the subcategory under the sub category heading.

Any ideas?

[funkdaddy]

Your SQL statement shoud join the 2 tables like this:

SELECT
	s.subcatName
	,m.merchant_name
	,m.thumb_nail
FROM
	subcategory s
LEFT JOIN
	merchant m
ON
	m.msubcid = s.subcid
ORDER BY
	s.subcatName
ASC

Then when you loop through the results, just set check for /set the category:

$currentCat = '';
while($row = mysql_fetch_array($query)) {
	extract($row);
	if ($currentCat != $subcatName) {
		//if a new category, write category head
		echo "<tr><td colspan=\"2\"><h1>$subcatName</h1></td></tr>";
		$currentCat = $subcatName;
	} else {
		echo "<tr><td>$merchant_name</td><td>$thumb_nail</td></tr>";
	}
}

Does that help you out any?

[freakystreak]

Thank you funkdaddy. I will try that out today and let you know how I get on.

[freakystreak]

Your solution works. Thanks funkdaddy, however it is missing out the first merchant record. This happens whatever subcategory the merchant belongs to. Example, 4 merchants assigned to subcat 1, only three display. 3 merchants assigned to subcat 2, only 2 display. It's always the first record for the subcategory. Thanks for your help.

Is this a looping problem? Here's my amended code including your solution;

<?php

echo '<table cellspacing="5" cellpadding="5" align="center">';

$result = mysql_query("SELECT s.subcatName, m.merchant_name, m.thumb_nail FROM subcategory s LEFT JOIN merchant m ON m.msubcid = s.subcid WHERE cid='$categorytarget' ORDER BY s.subcatName ASC") or die("SELECT error: " . mysql_error());

$columns_counter=5;

echo '<tr>';

$currentCat = '';
while($row = mysql_fetch_array($result)) {
$subcatName = $row['subcatName'];
$merchant_name = $row['merchant_name'];
$thumb_nail = $row['thumb_nail'];

extract($row);
if ($currentCat != $subcatName) {
//if a new category, write category head
echo "<tr><td colspan=\"2\"><h4>$subcatName</h4></td></tr>";
$currentCat = $subcatName;
} else {
echo '<td align = "center">';
echo '<A HREF="'.$config['thissite'].'/voucher-code/'.makeurlsafe($merchant_name).'"><img src='.$thumb_nail.' /></A><br />' . '<H4><A HREF="'.$config['thissite'].'/voucher-code/'.makeurlsafe($merchant_name).'">'.$merchant_name.'</A></H4>';
}

echo '</td>';
$columns_counter--;
if(!$columns_counter)
{
echo '</tr><tr>';
$columns_counter=5;
}
}

echo '</tr></table>';


?>

[funkdaddy]

Whoops... just remove the else statement from the cat checker:

echo '<table cellspacing="5" cellpadding="5" align="center">';
 
$result = mysql_query("SELECT s.subcatName, m.merchant_name, m.thumb_nail FROM subcategory s LEFT JOIN merchant m ON m.msubcid = s.subcid WHERE cid='$categorytarget' ORDER BY s.subcatName ASC") or die("SELECT error: " . mysql_error());
 
$currentCat = '';
while($row = mysql_fetch_array($result)) {
	$subcatName = $row['subcatName'];
	$merchant_name = $row['merchant_name'];
	$thumb_nail = $row['thumb_nail'];
 
	extract($row);
	if ($currentCat != $subcatName) {
		//if a new category, write category head
		echo "<tr><td><h4>$subcatName</h4></td></tr>";
		$currentCat = $subcatName;
	}
	echo '<tr><td align = "center"><A HREF="'.$config['thissite'].'/voucher-code/'.makeurlsafe($merchant_name).'"><img src='.$thumb_nail.' /></A><br />' . '<H4><A HREF="'.$config['thissite'].'/voucher-code/'.makeurlsafe($merchant_name).'">'.$merchant_name.'</A></H4></td></tr>'; 
}
 
echo '</table>';

(Not sure why you had the columns_counter in there if you're putting everything in one table cell...)

[freakystreak]

Funkdaddy you're a life saver. Thanks so much. If I could give kudos, I would.