3 column table dynamic from database

Question

I'm trying to create a dynamic table with 3 columns width. The input is automatically collected from the database.

The code I have:

$var_product_list = mysql_query("SELECT c.link AS clink,s.section_id,s.link AS slink,s.name AS sname FROM category c,section s WHERE c.link='$_GET[category]' AND s.category_id=c.category_id ORDER BY s.name ASC", $db);
    while($row_product_list = mysql_fetch_array($var_product_list))
    {
        $nbCols = 3;
        $nbRows = count($row_product_list['section_id'])/$nbCols;
        for($row=0; $row<$nbRows; $row++) {
            array_push($arr_product_list, "<tr>");
            for($i=0; $i<$nbCols; $i++) {
                $var_product_count = mysql_query("SELECT COUNT(prod_id) FROM products WHERE section_id='$row_product_list[section_id]' GROUP BY section_id", $db);
                $row_product_count = mysql_fetch_array($var_product_count);
                $nr_of_products = $row_product_count['COUNT(prod_id)'];
                if(empty($nr_of_products)){$nr_of_products = 0;}

                $index = $indexes[$row + ($i*$nbRows)];
                array_push($arr_product_list, "<td><a href=\"$bswConfig_live_site/browse/$row_product_list[clink]/$row_product_list[slink]\">$row_product_list[sname]</a> ($nr_of_products)</td>");
            }
            array_push($arr_product_list, "</tr>");
        }
    }

When I get the output, it's duplicated 3 times on each row.

Example:

Other | Other | Other
House | House | House
Garage| Garage| Garage

Instead of:

Other | House | Garage
Item4 | Item5 | etc..

Nice [SQL injection attack](http://bobby-tables.com) holes. Enjoy having your server pwn3d. — Marc B, Apr 28 '13 at 15:29
XSS is also possible; use `htmlspecialchars` when outputting data to a web page. — Marcel Korpel, Apr 28 '13 at 15:33
[Please, don't use mysql_* functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) in new code. They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [red box](http://uk.php.net/manual/en/function.mysql-connect.php)? Learn about [_prepared statements_](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which. — TNK, Apr 28 '13 at 15:39

score 1 · Answer 1 · answered Apr 28 '13 at 15:40

I didn't rebuild your loop, because I don't think that it is 100% the issue. You should be using a join instead of 1,000 queries it is faster and usually solves the problem. Since I am not really sure how your database is setup, with 1 query you can get all of your information.

Now you can use PHP to build your rows, be I don't know how that data is coming back to really build a table.

On an unrelated note, you should be using PDO or Mysqli, mysql is deprecated.

<?php

$cat   = mysql_real_escape_string($_GET["category"]);
$prods = mysql_query("
    SELECT c.link AS clink,s.section_id,s.link AS slink,s.name AS sname, count(s.prod_id) prod_count
    FROM category c
    INNER JOIN section s ON s.category_id = c.category_id 
    INNER JOIN products p ON s.section_id = p.section_id
    WHERE c.link='$cat'
    group by s.section_id
    ORDER BY s.name ASC", $db);
while($row = mysql_fetch_array($prods)){
    print_r($row);
}

3 column table dynamic from database

1 Answers1