looping results from query and limit amount per row

[jacko_162]

Trying to show data from a query and echo the results and grouping them by category after printing the category name.

 

So far all is working as intended, however i'm trying to incorporate a count limit so it only echo x5 <td></td> before making a new <tr> row

 

here is my current code;

   <?php
	   $select = "SELECT * FROM `products` ORDER BY `catagory`";
    $result = mysql_query($select);

    $current_cat = null;
    $last_cat = null;
     while ($rows = mysql_fetch_array($result)) { 

            if ($current_cat == null) {
        // Create a table with an id name of the first table
        echo "<table width='100% id='" . $rows["catagory"] . "'>";
// Write the first row of the table - Category Title
echo "<tr><td><span class='catTitle'>" . $rows["catagory"] . "</span></td></tr><tr>";
        }

    // Set the $current_cat to current loop category value
         $current_cat = $rows["catagory"];
         $getID = $rows['id'];

    if ($last_cat != null) {
            if ($current_cat != $last_cat) {
            // Close table from previous $current_cat
        echo "</table>";
        // Create new table with id name of the category
        echo "<table width='100%' id='" . $rows["catagory"] . "'>";
// Write the first row of the table - Category Title
echo "<tr><td><span class='catTitle'>" . $rows["catagory"] . "</span></td></tr><tr>";
        }
    }

//Fetch Image name from "IMAGES" table that corresponds to the product ID
    $thumbnail_query = mysql_query("SELECT name FROM $table3 WHERE insert_id = $getID LIMIT 1") or die (mysql_error());
	//Fetch Results
	while($data = mysql_fetch_array($thumbnail_query))
{ $imgName = $data['name']; }
                
// Write new <td> in table with the data of the title
	 
	echo '<td width="20%" class="cellPadd"><a href="product.php?id=' . $getID . '"><div class="latest"><div class="latestTop">';
	echo "<img class='latestImg' src='images/listings/" . $imgName . "'  border='0' width='100%' />";
	echo '</div><div class="latestBottom">';
	echo $rows["make"];
	echo $rows["name"];
	echo '</div></div></a></td>';


    // set the $last_cat to the value of $current_cat at the end of the loop
    $last_cat = $current_cat;
	
    } 
echo "</tr>";

    // Close the last table after while loop ends
    echo "</table>";

	?>

and i am trying to incorporate;

 $cnt = 0;


// IF COUNT IS MORE THAN 5 MAKE A NEW ROW
  if($cnt % 5 == 0) echo "CODE HERE FOR NEW ROW!";

  $cnt++;

can anyone help me as its giving me a headache now. ;(

 

Many thanks

[benanamen]

I wont get into the mess that is your code. You are using obsolete code that has been completely removed from Php.

 

You need to use PDO.

https://phpdelusions.net/pdo

Edited August 8, 2016 by benanamen

[Barand]

Easiest way is to read the results into an array and then use array_chunk() with a chunk size of 5. Output each chunk as a row.

[mac_gyver]

you will also want to JOIN the image table, whatever its name is, with the products table, so that you can run just ONE query. and did you really name the database column catagory, rather than category?

 

your code should look like this - 

$items_per_row = 5; // number of items per output row

$query = "SELECT p.id, p.catagory as category, p.name, p.make, i.name as imgName
 FROM products p
 JOIN $table3 i ON p.id = i.insert_id
 ORDER BY p.catagory, p.make, p.name";

// execute query using PDO, $pdo contains an instance/connection to the msyql database server
$stmt = $pdo->query($query);

// pre-process the data and index it by category, creating an array of arrays with the main index being the category
$data = array();
foreach($stmt as $row)
{
    $data[$row['category']][] = $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><td><span class='catTitle'>$category</span></td></tr>\n";
    
    // output the rows of data
    $chunks = array_chunk($arr, $items_per_row);
    foreach($chunks as $chunk)
    {
        echo "<tr>"; // start a new row
        foreach($chunk as $row)
        {
            // Write new <td> in table with the data of the title
            echo '<td width="20%" class="cellPadd"><a href="product.php?id=' . $row['id'] . '"><div class="latest"><div class="latestTop">';
            echo "<img class='latestImg' src='images/listings/" . $row['imgName'] . "'  border='0' width='100%' />";
            echo '</div><div class="latestBottom">';
            echo $row["make"];
            echo $row["name"];
            echo "</div></div></a></td>\n";
        }
        // complete a partial row
        $count = count($chunk);
        if($count < $items_per_row)
        {
            echo str_repeat("<td> </td>\n", $items_per_row - $count);
        }
        echo "</tr>\n"; // end the row
    }
    echo "</table>"; // end the table
}

 

[jacko_162]

 

you will also want to JOIN the image table, whatever its name is, with the products table, so that you can run just ONE query. and did you really name the database column catagory, rather than category?

 

THANK YOU FOR THE CODE, 

 

i only just realised the "catagory" mistake.

 

its actually not catagory its "category".

 

here are the 2x tables;

 

relationship between the two tables are products.id and images.insert_id 

 

Images:

http://clip2net.com/clip/m513573/4d3b6-clip-98kb.png?nocache=1

 

Products:

http://clip2net.com/clip/m513573/4b6a0-clip-38kb.jpg?nocache=1

 

so i tried your code and nothing is getting displayed, so for the time being i edited out the PDO and converted to depreciated code, as for now i'm just trying to code the last page and a few bits, then i can edit the whole site and change/learn to PDO.

 

here is the code i have now which i edited it slightly.

$items_per_row = 5; // number of items per output row

$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
ORDER BY products.category, products.make, products.name";

// execute query
 $result = mysql_query($query);

// pre-process the data and index it by category, creating an array of arrays with the main index being the category
$data = array();
foreach($result as $row)
{
    $data[$row['category']][] = $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><td><span class='catTitle'>$category</span></td></tr>\n";
    
    // output the rows of data
    $chunks = array_chunk($arr, $items_per_row);
    foreach($chunks as $chunk)
    {
        echo "<tr>"; // start a new row
        foreach($chunk as $row)
        {
            // Write new <td> in table with the data of the title
            echo '<td width="20%" class="cellPadd"><a href="product.php?id=' . $row['id'] . '"><div class="latest"><div class="latestTop">';
            echo "<img class='latestImg' src='images/listings/" . $row['imgName'] . "'  border='0' width='100%' />";
            echo '</div><div class="latestBottom">';
            echo $row["make"];
            echo $row["name"];
            echo "</div></div></a></td>\n";
        }
        // complete a partial row
        $count = count($chunk);
        if($count < $items_per_row)
        {
            echo str_repeat("<td> </td>\n", $items_per_row - $count);
        }
        echo "</tr>\n"; // end the row
    }
    echo "</table>"; // end the table
}

i tested the SQL query and it gave me this;

so its pulling data i just need to LIMIT the 'id' to only show 1 as some products can contain upto 4 images per id.

 

can you check whys it not echo anything?

Edited August 9, 2016 by jacko_162

[mac_gyver]

add GROUP BY products.id right before the ORDER BY .... term, to consolidate the rows having the same id into a single row in the result set.

[jacko_162]

add GROUP BY products.id right before the ORDER BY .... term, to consolidate the rows having the same id into a single row in the result set.

 

ok thats sorted my results out in phpmyadmin when i run the query,

 

 

but when i run the code on my server with the code above i just get a blank page with nothing being echo'd

[mac_gyver]

do you have php's error handling setup to report and display all the errors that are detected?

 

the pdo specific method of looping over the result from the query won't work with the ancient mysql extension and would be throwing a php error about an invalid argument being supplied to the foreach loop.

 

to fetch data from the result of a query using the mysql extension, you would need to use a while(){} loop, like in your originally posted code.

[jacko_162]

do you have php's error handling setup to report and display all the errors that are detected?

 

the pdo specific method of looping over the result from the query won't work with the ancient mysql extension and would be throwing a php error about an invalid argument being supplied to the foreach loop.

 

to fetch data from the result of a query using the mysql extension, you would need to use a while(){} loop, like in your originally posted code.

 

i changed:

$data = array();
foreach($result as $row)

to:

$data = array();
while($row = mysql_fetch_assoc($result))

and now i'm getting some data being echo'd 

 

but its not formatting in rows of 5, however i have no idea what to change the other foreach() loops to for it to work.

 

here is what i get on page after i changed the above while();

 

 

looks like the first chunk is displayed but its not showing the next chunk on a new line underneath??

Edited August 9, 2016 by jacko_162

[jacko_162]

aaah i fixed it, i must of copy+paste the wrong code and it put "catagory" again..... once changed to "category" it worked fine

 

<Derp> <Derp> 

 

marking topic now SOLVED 

Edited August 9, 2016 by jacko_162

[Psycho]

This is unnecessary

 

// complete a partial row
$count = count($chunk);
if($count < $items_per_row)
{
    echo str_repeat("<td> </td>\n", $items_per_row - $count);
}

 

All you need is this

 

// complete a partial row
echo str_repeat("<td> </td>\n", $items_per_row -  count($chunk));

 

If the chunk contains 5 items (or the same number as $items_per_row, the multiplier will be 0 and no empty cells will be output.