Hi, I am at a dead end with something and was hoping maybe I can get help...
I have a MySQL database with a table called 'reviews2_category' that contains a column called 'id' which contains numbers (the id of the category) and a second colum called 'category' which contains the names of the categories. In other words for example category music is id=1, category book has id=2 etc...
Then I have another table in the same database that is called 'reviews2_reviews'. This table has several columns. The ones that interest me are called 'id' (the id number of the review), 'id_category' (the category in which this reviews is filed, i.e. if it was a music review id_category would be set to 1), 'artist' (the name of the artist that has been reviewed).
I have a PHP file that creates an html table that displays the name of the artist and provides links to delete and edit the reviews.
The code for this is:
while ( $res = mysql_fetch_object($ctrl) ) {
echo '
<tr>
<td><a href="edit.php3?id='.$res->id.'">Edit</a></td>
<td><a href="delete.php3?id='.$res->id.'">Delete</a></td>
<td>'.$res->artist.'</td>
</tr>
';
This generates a table in which there are three columns, the first one with links to the edit page with an id sent in the url to know which review to edit, the second one with links to the delete page with an id sent in the url, and the third one with the name of the artist whose review you're about to edit/delete.
Originally the MySQL query for this was:
$sql = ' SELECT id, artist, work_title FROM reviews2_reviews ORDER BY id DESC ';
if ( !( $ctrl = mysql_query($sql, $connection) ) ) {
echo "DATABASE ERROR";
exit;
}
So here is my problem: I now want to have a column in the html generated table that contains the NAME of the category this review is filed under (ie music, books etc), so I have changed the query to:
$sql = ' SELECT reviews2_reviews.id as id,
artist,
work_title,
id_category,
reviews2_category.category as CAT,
reviews2_category.id as CAT_ID
FROM reviews2_reviews, reviews2_category
WHERE reviews2_category.id = reviews2_reviews.id_category
ORDER BY id DESC';
and of course I have added this to the html:
<td>'.$res->CAT.'</td>
Unfortuantely what I am getting is a list of reviews where every review is duplicated and showed multiple times (to be precise as many times as there are categories in the reviews2_category MySQL table, which is to say as many times as there are values in the reviews2_category.id MySQL table colum).
How can I avoid that? What am I doing wrong?
I am new to MySQL and I don't quite understand how to write a query where I tell MySQL to only spit out the reviews whose category NAME matches the category assigned to the id whose value is stored in id_category column of the reviews2_reviews table.
I hope I haven't confused you guys...
Thanks for reading up to this point...
eternally thankful for your help...
Marc
