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.

Short answers: Yes and yes. Long answers: Add a column to the table which can store the link so that you can include it in an anchor tag in the HTML. For the image, it is usually better to store the actual image on the file system and store the filepath in the database. If you need to store the image in the database, look at base64 encoding and blob columns. By the way: Ditch the mysql* functions and start using mysqli* functions or PDO. — GarethL
– GarethL, Commented Jan 15, 2015 at 14:29
Thanks Gareth, I'm aware of how to add the link and the image filepath to the DB but how do i read this in to the table all in the one cell? — Steven Trainor
– Steven Trainor, Commented Jan 15, 2015 at 14:32
Change your query to SELECT category, link, filepath FROM listings and you will find the link and filepath in the $row['link'] and $row['filepath'] variables. Your table cell becomes <td><a href="$row['link']">$row['category']</a><img src="$row['filepath']" /></td> (obviously you'll need to sort this out into a proper PHP string etc. but the gist is there) — GarethL
– GarethL, Commented Jan 15, 2015 at 14:38

Fenistil · Accepted Answer · 2015-01-15 15:24:19Z

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.

edited Jan 15, 2015 at 15:24

answered Jan 15, 2015 at 14:43

Fenistil

3,8111 gold badge29 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Steven Trainor Over a year ago

Tried that but i am getting the error: Query was empty SELECT distinct listings.category, listings_links.link, listings_links.image_path FROM listings LEFT JOIN listings_links on (listings.category=listings_links.category) ORDER BY 1; I have populated the listings_links table with data.

Fenistil Over a year ago

@StevenTrainor Can you show me the content of your tables? Or at least a few rows?

Steven Trainor Over a year ago

Sure, not sure if theres a handier way than doing it here? Just have some rows of dummy data atm. listings table has listingid, title, description, category, subcategory, userid with data for one record: 1, chandalier, brand new light, electrical, lighting, 1 listings_links table has the format u suggested and has the data: 1, electrical, c2share.com/index.php, img/profilepics/2ad14cd7af.png

Fenistil Over a year ago

@StevenTrainor Do you have PhpMyAdmin or any other SQL client where you can test the query? The query shouldn't be emtpy because It uses LEFT JOIN which means, that if something doesn't exist in the second table, you get NULL values in the link and image_path field, but there is the row.

Steven Trainor Over a year ago

Yes im using MySQL Workbench and when I query the following: SELECT distinct listings.category, listings_links.link, listings_links.image_path FROM listings LEFT JOIN listings_links on (listings.category=listings_links.category) ORDER BY 1; It gives me the result but in PHP i have to add ; after each of these lines otherwise the page crashes and when i do i get the query empty result :/

|

Jason Bassett · Accepted Answer · 2015-01-15 14:45:53Z

0

Try something like this:

<?php
$query = "SELECT distinct category,link,image FROM listings";
$result = mysql_query($query);
$catNo = 1;

echo "<table> <tr>";

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

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

answered Jan 15, 2015 at 14:45

Jason Bassett

1,2918 silver badges19 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related