Selecting data from one table based on data from another table

[Lars_]

Hi everyone, hope you can help me with a problem ive got. I wrote this line, but it isnt correct, im not too familiar with php, so i cant see whats wrong.

I have two tables, one with products which contains products id, and images i want to show on my website, and one table named products_to_categories, where i have my categories of products, and my products id. I want to only show images from the category with id 116

This is my code

$result = mysql_query("SELECT * FROM products_to_categories LEFT JOIN products WHERE categories_id='116'");

Why isnt this working?
Thank you very much in advance!
Lars

[Lars_]

Hi i wanted to add these images to help you see what i mean. 🙂

[djjjozsi]

table1
tblID1
field1

table2
tblID2
tbl2_field1
tblID1_FK

example for LEFT JOIN: select evrything from table1 , and each rows from table2 where the connected fields are equal.

select * from table1 LEFT JOIN table2 ON table1.tblID1=table2=tblID1_FK

[Lars_]

From what i understand, that code will give me something like this:

select * from products LEFT JOIN products_to_categories ON products.products_id=products_to_categories=products_id

But it doesnt state anywhere that i only want to display images from category 116

[djjjozsi]

how about:

select * from products 
LEFT JOIN products_to_categories ON products.products_id=products_to_categories=products_id
WHERE categories_id=116

[Lars_]

hehe that makes sense. But even so, i get the error "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /julegaver/index/body.php on line 21"

This is my excact code

$result = mysql_query("select * from products LEFT JOIN products_to_categories ON products.products_id=products_to_categories=products_id WHERE category='116'");

		while($row = mysql_fetch_array($result))
		 {
		echo $row['products_image'];
		 }

[djjjozsi]

is there an existing category field name in your tables? if not, try to change to the real one.

[Bjom]

you didn't retrieve any data. either because of an error or because no data matches the criteria (id=116)

add this to your code:

...or trigger_error(mysql_error(),E_USER_ERROR);

...if ($result) {

...

}

like this:

$result = mysql_query("select * from products LEFT JOIN products_to_categories ON products.products_id=products_to_categories=products_id WHERE category='116'") or trigger_error(mysql_error(),E_USER_ERROR);

if ($result) {
    while($row = mysql_fetch_array($result)) 
                 { 
                echo $row['products_image']; 
                 } 
} else {
    echo 'no data could be found matching the criteria';
}

[Roger_Ramjet]

Perhaps if you got your sql right you would get a result. Have a look at your ON ....

[Lars_]

Thanks everyone, the help was much appreciated, and now its.... WORKING 😃
And i also learned what the whole thing meant, thank you very much.

Here is the working code:

$result = mysql_query("SELECT * FROM products_to_categories LEFT JOIN products ON products_to_categories.products_id = products.products_id WHERE products_to_categories.categories_id=116") or trigger_error(mysql_error(),E_USER_ERROR);

if ($result) {
    while($row = mysql_fetch_array($result))
                 {
                echo $row['products_image'];
                 }
} else {
    echo 'no data could be found matching the criteria';
} 

Thanks!,
Lars