CODEWITHSUNDEEP
phpmysqlsqldatabase
InterestingGuy
InterestingGuy

Reputation: 51

I am trying to compare items in two different DB tables

I'm having trouble getting this to work correctly on my classifieds website, but basically what I am doing is taking all from an Item table LIMIT 40 and displaying it on page. Now for the hard part, I am taking all from a category table, which contains the names of the categories (each category name has a relative id). I need to print out the name of the category WHEN its id is equal to the id of the item. Hopefully the code will help clarify:

$sql = mysql_query("SELECT * FROM table_item ORDER BY creationtime DESC LIMIT 40");
$sql2 = mysql_query("SELECT * FROM table_category");
$ad_count = mysql_num_rows($sql);
$row2 = mysql_fetch_array($sql2);

if($ad_count > 0){
  while($row = mysql_fetch_array($sql)){
    $item_categoryId = $row["cid"];
    $categoryId = $row2["id"];
    $categoryName = $row2["name"];
    while($item_categoryId == $categoryId){
      $catName = $categoryName;
    }
    echo $catName;
  }
}

Now, there is a little more to the code then what I put up, I tried to keep this short and sweet. Instead of echoing the category name, its actually being put into an HTML table row and there is also a lot more information being put in as well. Everything works fine when I don't do anything with the category name, but when I try to build something to access and compare it, then everything goes to shit.

Also, I should mention that this nested while loop seemed to be the best way to go about this (I have tried many ways) and the error I am getting for this is that "there is an unexpected '}'".

Upvotes: 0

Views: 73

Answers (1)

user4035
user4035

Reputation: 23749

Use joins instead of nested loops:

SELECT
    *
FROM
    table_item,
INNER JOIN
    table_category
ON
    table_item.cid=table_category.id
ORDER BY
    creationtime DESC
LIMIT 40

If not every item has a corresponding category, use LEFT JOIN instead of INNER JOIN.

Upvotes: 1

Related Questions