Continued SQL problem..

[Anon]

Hi.

I am having trouble with a query using a join. I have two tables as follows:

PRODUCTS
->id
->title
->author
->isbn
->description
->picture

PRODUCTS_CATEGORIES
->product_id
->category_id

I need to find all items that have a picture and belong to one of multiple categories. This is my query, however, I am getter an unexplained error and no result set 🙁

$sql = "SELECT * FROM products, products_categories WHERE ((products.picture IS NOT NULL) AND (products_categories.category_id ='405' OR products_categories.category_id='416' OR products_categories.category_id='418' OR products_categories.category_id='420' OR products_categories.category_id='430' OR products_categories.category_id='440' OR products_categories.category_id='510' OR products_categories.category_id='580' OR products_categories.category_id='605' OR products_categories.category_id='730' OR products_categories.category_id='792')) ORDER BY rand() LIMIT 4";

Any help is very much appreciated!

Thanks,
Zac

[[deleted]]

First a little tip:
When you are looking for records where one column must have any of a set of values, use the IN clause:

SELECT *
FROM table
WHERE field IN (1,3,657,34,65778,34,2);

Second, your query does not link the products table to the product_categories table. If you do it like this, then every record from the product table will match with every record from the prodcut_categories table, and you'll get a cartesian-product: n time n = thousands of records.

Third, what was the exact error message that mysql_error (or equivalent) gave you?

[Anon]

It was giving me an error 28 by table handle...basically, that the disk is full. I think I have solved that by not selecting evertyhing, just the fields that I need, but I am still getting too many results. Any coding ideas?

[Anon]

Actually, it seems that the query is catching only the ones with pictures, but not even caring about the 'AND' regarding the category id's. Running it at the monitor, I get all the products with pictures, regardless of category with the current code:

SELECT DISTINCT id, title, price, author, picture, description FROM products, products_categories WHERE ((products.picture !='') AND (products_categories.category_id=748 OR products_categories.category_id=753)) ORDER BY rand()

The same results comes when using this version:

SELECT DISTINCT id, title, price, author, picture, description FROM products, products_categories WHERE ((products.picture !='') AND (products_categories.category_id IN (748,753))) ORDER BY rand()

Thanks for any help 🙂

-Zac

[[deleted]]

Like I said, you are not connecting the products table to the categories, so every product will always match for every category.

You must put something in the WHERE clause that says that some column of the product talbe must have the same value as some column in the categories table for them to match.

[Anon]

I see what you are saying now...and thank you for all of the help 🙂

I have alter the query as follows, but now get no data as a result. I know that there is data that should come up from this query, but maybe I have made another SQL error. Thanks again for any suggestions.

$sql = "SELECT DISTINCT id, title, price, author, picture, description FROM products, products_categories WHERE ((products.picture !='') AND (products_categories.category_id IN (405,416,418,420,430,440,510,580,605,730,792)) AND (products.id = products_categories.category_id)) ORDER BY rand()";

[amcgrath]

Outta curiousity, why are you using DISTINCT? Try it without it and lemme know what happens.

[Anon]

I was using it just to make sure not to get any duplicates, but removing it seems to have no effect. ???

Thanks!

[amcgrath]

OK, i gotcha... and you are sure that the ID column on the products table is a foreign key to the category_id on the category table, correct?

[Anon]

no, I am not sure what you mean. I have it set as an index and the id is set as primary on the products table and category_id is set as primary on the products_categories table.

?

Thanks!

[amcgrath]

OK, so your ID field on products is primary, and category_id on products_categories is primary. I don't quite get this, why not have a pk of product_id and a foregin key of cateogry_id? (Or maybe I am just totally misunderstanding you).

$sql = "SELECT id, title, price, author, picture, description FROM products, products_categories WHERE ((products.picture !='') AND (products_categories.category_id IN (405,416,418,420,430,440,510,580,605,730,792)) AND (products.id = products_categories.category_id)) ORDER BY rand()";

[Anon]

I am using MySQL...I was under the impression that forgein keys didn't do anything:

http://www.mysql.com/doc/e/x/example-Foreign_keys.html

[amcgrath]

No, what it says is "You don't need foreign keys to join 2 tables...." which is true, but in your sql, you have "where....products.id = products_categories.category_id ..." and, unless I am totally missing something here, this will not return a result (unless, onthe off chance, the same autoincrement value appears in two tables, but that is still an invalid return). Again, I retiterate, you should have a category_id column on the prodcuts table that is a foreign key to prodcut_categories.category_id.. that way, instead of "where....products.id = products_categories.category_id ..." you could have "where....products.category_id = products_categories.category_id ..." .

$sql = "SELECT id, title, price, author, picture, description FROM products, products_categories WHERE ((products.picture !='') AND (products_categories.category_id IN (405,416,418,420,430,440,510,580,605,730,792)) AND (products.id = products_categories.category_id)) ORDER BY rand()";

[Anon]

Duh! Okay, I think that I fixed part of that problem. In the products_categories table, I have only two fields, the product_id and category_id. I was simply using the wrong one 🙁

So I got this to work like a charm, but without any the IN clause:

$sql = "SELECT id, title, price, author, picture, description FROM products, products_categories WHERE ((products.picture !='') AND (products.id = products_categories.product_id)) ORDER BY rand()";

but with the IN clause, I still get no result set. Also, running it from the command line produces an error which it doesn't define:

$sql = "SELECT id, title, price, author, picture, description FROM products, products_categories WHERE ((products.picture !='') AND (products_categories.category_id IN (405,416,418,420,430,440,510,580,605,730,792)) AND (products.id = products_categories.product_id)) ORDER BY rand()";

Thanks so much. I really appreciate the help.

[gid__]

SELECT * FROM product_categories, products
WHERE product_id=id AND category_id IN (405,416,418,420,430,440,510,580,605,730,792)

Should give you what you need, if you don't receive any results, then double,triple and quadruple check your data and see if you really should be getting anything back.

Like start out with something basic like:

SELECT * FROM product_categories
WHERE category_id IN (405,416,418,420,430,440,510,580,605,730,792)

and see what you get back, looks the product_id of that, and see if you really have products to match it.

[amcgrath]

Your sql doesn't join the two tables together.

[Anon]

Worked like a charm. Thanks for all of your help..I have learned a lot 🙂

Here is the final code, if you're interested:

$sql ="SELECT * FROM products_categories, products WHERE ((products.picture !='') AND (product_id=id) AND (category_id IN (405,416,418,420,430,440,510,580,605,730,792))) ORDER BY rand()";

[amcgrath]

As a last note, you should get in the bait of referenceing the tables, even if the field is unique to that table...

$sql ="SELECT * FROM products_categories, products WHERE ((products.picture !='') AND (products.product_id=id) AND (products_categories.category_id IN (405,416,418,420,430,440,510,580,605,730,792))) ORDER BY rand()";

[amcgrath]

bait = habit, btw... 😉