Loop results and format into columns limiting number by 8 per row

[jacko_162]

OK so i have the following code;

<!--START NAVIGATION-->
<?php
 
$categories = mysql_query("SELECT * FROM $table2 WHERE parent = '0' AND view = 'Yes' ORDER BY name ASC") or die(mysql_error()); // Select all the root categories and order them alphabetically
 
while($category = mysql_fetch_array($categories)) // Start the loop for root categories
{
	echo '<img src="images/arrow.gif"> <a href="catagory.php?name=' . $category['name'] . '">' . $category['name'] . '</a>'; // Display category name
 
	$subcategories = mysql_query("SELECT * FROM $table2 WHERE parent = '" . $category['id'] . "' AND view = 'yes' ORDER BY name ASC") or die(mysql_error()); // Same as the query above but this time the parent is the root category that has just been echoed
 
	echo '<br>';
 
	while($subcategory = mysql_fetch_array($subcategories)) // Start the loop for subcategories
	{
	echo '  <img src="images/bluearrow.gif"> <a href="subcatagory.php?name=' . $category['name'] . '&sub=' . $subcategory['name'] . '"><em>' . $subcategory['name'] . '</em></a><br>'; // Display subcategory name
	}
}
?>
<!--END NAVIGATION-->

and it displays a nice list of my category and sub category like so;

 

 

No i need to keep the category in the same place but where it lists the sub category i want each one to sit in a <div></div> and run in columns to a maximum of 8 before starting a new row, i have done this before but seen as it has 2 main query its confusing the crap out of me.

 

i know this should be in PDO but unfortunately i just have to fix the site up in the next few days for a friend and until then i'm not going to start editing the whole site to use PDO.

 

can anyone help me or point me in a good direction of existing code and where is best to put it.

 

thank you

[mac_gyver]

this is the same basic task as in your previous thread - http://forums.phpfreaks.com/topic/301791-looping-results-from-query-and-limit-amount-per-row/

 

the sql query would join the table to itself to get the category/sub-category information using one query. then, change the $items_per_row = 5 value to 8, and make any changes you need to the html markup that's being produced.

[jacko_162]

aah that makes sense, how would i join it to itself?

$query = "SELECT products.id, products.category as category, products.name, products.make, images.name as imgName
FROM products
JOIN images ON products.id = images.insert_id
GROUP BY products.id
ORDER BY products.category, products.make, products.name";

i obv wont need to query the images.name and/or join it to images.insert_id

[jacko_162]

ok so i worked out a JOIN query;

 

SELECT c1.id AS parent, c1.view, c2.filename, c2.name, c2.id
FROM category c1 
INNER JOIN category c2 
ON c1.id = c2.parent 
GROUP BY c2.name 
ORDER BY c1.id

 

on my code on the page its indexing the category by subcategory but i cant get it to display the parent category name

// pre-process the data and index it by category, creating an array of arrays with the main index being the category$data = array();
while($row = mysql_fetch_assoc($result))
{
    $data[$row['parent']][] = $row;
}


// produce the output from the data
foreach($data as $category => $arr)
{
    // Create a table with an id name of the category
    echo "<table width='100%' id='$category'>\n";
    // Write the first row of the table - Category Title
    echo "<tr class='product-details'><td><span class='catTitle'>$category</span></td></tr></table><table width='100%' id='$category'>\n";

    

 

see image;

Edited August 21, 2016 by jacko_162

[Jacques1]

I don't think you understand what GROUP BY means. Get rid of it.

 

aah that makes sense, how would i join it to itself?

 

By using an alias.