user3502802 user3502802 - 3 months ago 17
MySQL Question

mysql, 1 table with where condition joined with another table with different where condition

First of all, my apologies for crude terminology in the title.

<? if ($thisguy == "noob") { echo("bear with me"); } ?>

I have two tables:

one "Rating" (rows: product, user, rating, added) WHERE
user = $thisuser[id]

and one "Products" (id, name) WHERE = rating.product

Ive read extensively on join (inner, outer, left, right...) and I am stuck like chuck on this.

I need all info WHERE = ratings.product and user = $thisuser[id]
in an array.

I'm using this:

$result = mysql_query("SELECT ratings.product, ratings.user, ratings.rating, ratings.added FROM ratings INNER JOIN products ON = ratings.product AND user = $THISUSER[id]");
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$product = $row['product'];
$user = $row['user'];
$rating = $row['rating'];
$added = $row['added'];
$pid = $row['id'];
$pname = $row['name'];
print("$user, $rating, $added, $THISUSER[id], $pname, $pid"); }

This produces all in the ratings table (but only one instance) and nothing from the products table... I've tried this a 100 different ways to no avail. Any help would be greatly appreciated! Go easy on me. *winces


Ensure the Database Name that you have provided in the query are correct since your description contains the table names as different from the code what you have made.

Note: You have selected only the rating table alone and no selection from the product table and in my code i have selected the product name from the product table.

You have to use ON Condition along with the WHERE so that it will be perfect when you use JOIN query. Since the Syntax of the Join QUERY is resulting as such.

You can provide the comparison value in the ON and checking value in the WHERE so that your data looks crimped to what you expect.

The JOIN QUERY will look like this in your case.

$query = "SELECT rating.product,rating.user,rating.rating, FROM rating INNER JOIN products ON = rating.product WHERE rating.user ='".$thisuser['id']."'";