Resolved php echo data from db [Archive]

john0611

11-30-2009, 12:50 AM

Hi all I have a bit of a problem I’ve been trying to suss out?

Alright, I have a simple mysql database consisting of 4 tables.

I have 1 table with a foreign key to another table. Rough example:
Table 1

category (tbl)

id (pk auto inc.) category_id (fk)

1 Arks

3 Furniture

2 Poultry Houses

6 Sheds

Table 2
products (tbl)

id (fk - index) - name - description - filename - thumbnail
Arks - Broody Coop - Some content - img.jpg - thumb1.jpg
Poultry Houses -Trio House - Some Text - img2.jpg - thumb2.jpg
Sheds - Apex Shed - Some Text - img3.jpg - thumb3.jpg
Sheds - Large Shed - Some Text - img4.jpg - thumb4.jpg

the other 3 are normal.

Question is how can I echo the data from both tables 1 & 2? Here is what I got so far:

<?php include ("include/head.php"); ?>

<body>

<?php include ("include/header.php"); ?>

<div class="wrapper">

<?php include ("include/topnav.php"); ?>


<div class="datadiv">

<div class="datadivright">

<div class="datadivrightcontent">

<img src="img/broody.coop.jpg" width="176" height="136" alt="" />

<img src="img/broody.coop.jpg" width="176" height="136" alt="" />

</div>

</div>

<div class="datadivleft">

<?php
// linked result from navigation query
if (isset($_GET['category_id'])) {
$sql = "SELECT category_id FROM `category` WHERE category_id = '" . mysql_real_escape_string($_GET['category_id']) . "'";
if ($result = mysql_query($sql)) {
if (mysql_num_rows($result)) {
$row = mysql_fetch_assoc($result);
echo "<h2>" . $row['name'] . "</h2>";
} else {
echo "Something is wrong!";
}
}
}
?>
<div class="datadivleftcontent">

<?php

$sql = "SELECT id.category, id.products FROM category, products WHERE category_id.category = id.products";
if ($result = mysql_query($sql)) {
if (mysql_num_rows($result)) {
$row = mysql_fetch_assoc($result);
echo "<h2>" . $row['category_id'] . "</h2>";
} else {
echo "Something is wrong!";
}
}

?>

</div>

</div>

</div>


<?php include ("include/showcase.php"); ?>

<?php include ("include/btmcontent.php"); ?>

</div>

<?php include ("include/footer.php"); ?>

</body>
</html>

Hope this all makes sense?
Any help and suggestion is greatly appreciated. Thanks.

djr33

11-30-2009, 02:27 AM

There are two ways: you could attempt to get the data by doing a complex mysql query, but the data returned from this would be hard to manage in php, probably. There are ways that you can merge data together within mysql from two tables while you return it, but that is probably not too worthwhile here.

The easiest way is just to do what you have, then do another query to get the corresponding info in the other table. Use another variable to store the result and then just echo both as needed.