i am trying to get each category listed in my categories table to show as its own table with movies from my movies table being listed within them. instead of just giving me 1 single table with all the movies listed. so say i was to have 4 categories in my categories table then,
Category 1
movie 4
movie 1
Category 2
movie 7
movie 2
Category 3
movie 5
movie 9
Category 4
movie 6
movie 8
is how its suppose to look.
the categories table does have a FK so the movies know which categories they belong to.
i was told using while loops was the way to go. i had some help on this but im guessing i didnt ask the right questions because the code below is not giving me what im wanting.
<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("mydb", $con);
$result = mysql_query("SELECT a.ID,
a.Title,
b.Category,
a.URL
FROM Movies AS a
JOIN Categories b ON a.Category = b.ID
ORDER BY b.Category");
echo "<table border='1'>
<tr>
<th>Title</th>
<th>Category</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td width='100'><a href='".$row['URL']."'>" . $row['Title'] . "</a></td>";
echo "<td width='150'>" . $row['Category'] . "</td>";
}
echo "</table>";
$result2 = mysql_query("SELECT ID,Category FROM Categories");
while($row2 = mysql_fetch_array($result2))
{
echo "<tr>";
echo "<td>" . $row2['Title'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysql_close($con);
?>
as you can see what this code just gives me a single table (with movies listed) and then right below the table it lists my categories from left to right, odd looking really lol. can someone please help me out on this im way over due and have spent too much time trying to figure out and its like a drug i cannot stop wasting time on this until i get it. im guessing that one of the queries or echos are wrong or both. i have had some tell me the using 2 loops was the way to go and then ive had some tell me that 2 loops were pointless but then fail to fill me in on the details im missing so i have been going back and forth on both but just left with confusion. anyways, if its just a few things i need to switch around please tell me what i need to do or what i need to fill in. thanks.
