Add multiple MySQL results to one cell in a HTML table using PHP

Question

I am currently using the following code to get data to display the way I want it to in the content section on my homepage:

$query = "SELECT distinct category FROM listings";
$result = mysql_query($query);
$catNo = 1;

echo "<table> <tr>";

while($row = mysql_fetch_array($result)){
    echo "<td>" . $row['category'] . "</td>";
    if ($catNo % 3 == 0) {
       echo "</tr><tr>"
    }
    $catNo++;
}

echo "</tr> </table>";

Which results in this:

| CAT1 | CAT2 | CAT3 |
| CAT4 | CAT5 | CAT6 |

Is it possible for me to store a link in the database which means upon clicking on the cell i am brought to that href? Is it also possible to store an image in the database and display this in the cell with the name of the category?

Any help would be greatly appreciated.

Thanks.

Answer 1

Because you use distinct to get the results, it means for me that there is more rows with the same category in that table. If it's right, then you should create another table, called "listings_links" to store every link and image only once, then join this table to the listings table.

CREATE TABLE listings_links(
id INT AUTO_INCREMENT NOT NULL,
category CHAR(10) NOT NULL, //Use here the exact type you used in the listings table
link VARCHAR(200) NOT NULL,
image_path VARCHAR(200) NOT NULL,
PRIMARY KEY(id),
KEY(category));

Then use this:

<?php
$sql  = "SELECT distinct listings.category, listings_links.link, listings_links.image_path FROM listings ";
$sql .= "LEFT JOIN listings_links on (listings.category=listings_links.category) "
$sql .= "ORDER BY 1;";
$result = mysql_query($sql) or die(mysql_error()."<br>".$sql);
$catNo = 1;

echo "<table> <tr>";

while($row = mysql_fetch_array($result)){
   echo '<td><a href="'.$row['link'].'"><img src="'.$row['image_path'].'" border="0" />'.$row['category'].'</a></td>';
    if ($catNo % 3 == 0) {
    echo "</tr><tr>";
    }
    $catNo++;
 }

echo "</tr> </table>";
?>

For the best performance (if you have a lot of categories) make an index on "category" in your listings table also.

UPDATE: I also recommend to store the images in the file system instead of the database. This way the page will load faster (the browser can download more images in parallel) and you don't have to mess with the loading the images into the database (you should always do it when a category image is refreshed) and the code is more complicated too. But it can be done, but not recommended.

UPDATE 2: You didn't ask for it, but if I were you, I don't store either the link nor the image path in the DB. I recommend to create a PHP that can show what you want by its parameter (for ex.: show_cat.php?cat1) and use this in the link and store all the images the way that I can direcly link to them by the category's name (for ex.: /images/categories/cat1.jpg, /images/categories/cat2.jpg etc.). This way you don't have to alter your DB, and you only have to add the link and the image path to the loop. If you want, you can also check for the image file that it exists or not, and show a "No image available" pic instead.