'AND' 'OR' not working together

Databases
21

okay and then what does that do in terms of the database query?

22

you need to rethink the query – what it’s for and what it’s supposed to show

for example, if you’re not going to show all ingredients for a product, why show a random one?

23

I’ve just tried to create the new table but got an error:

#1215 - Cannot add foreign key constraint

Also, I’m so sorry to be a pain but what should got in the product_ingredients table, at the moment there’s one row for each ingredient in each product, should I jus have a row for each product and then put the ingredient id’s, separated by a comma (or not separate) in the ingredient_id column?

24

leave out the foreign key stuff until you understand how to do them

the product_ingredient table might show this –

product ingredient qty 23 1015 2 23 1033 500gm 23 1099 250ml 44 1024 5 44 1033 1000gm
one row for each ingredient in a product

the “quantity” column is just a suggestion, an example of attribute data stored in a many-to-many relationship table

you can find out lots more by googling “many-to-many relationship table”

25

Okay I understand what you’re saying - have one talk with the product id and then all of the other attributes like flavour, ingredients etc. Am I able to combine tables to do this or do I have to do it manually? There’s tens f thousands of results so doing it manually would take a while.

Thanks again for helping me with this.

26

not sure i can help you any further, because this discussion has moved to very general terms

best i can suggest is that you learn about Third Normal Form

27

Sorry I didn’t mean to stray from the main subject. I’ve managed to combine all of the tables into one table that I’ve called attributes and now have one INNER JOIN and am using table IN ('1', '12', '30') and to add each set of attributes if they’re ticked but I’m still getting zero results.

I’ve been through the database and the following query should return at least 5 results but as I say I don’t get any at all.

SELECT
	product_name, weight, weight_id, serving, price, brand_name, protein_source, protein_100, fat_100, calories_100, carbs_100, aw_deep_link, product_feed_id, image_url, best_seller
from
	feeds 
INNER JOIN 
	attributes ON product_feed_id=product_id 
WHERE 
	weight_id IN ('0') 
	and
	timing_id IN ('6', '4', '0') 
	and 
	flavour_id IN ('38', '0')
	and 
	source_id IN ('1', '2', '0') 
	and 
	ingredient_id IN ('30', '25', '4', '31', '0')
	and 
	sweetener_id IN ('2', '0')
	and 
	category_id IN ('4', '1', '0')
	and 
	price >=0
	and 
	price <=250
	and 
	enabled=1
	and 
	stock=1
	and 
	deleted=0 
GROUP BY 
	product_name
ORDER BY 
	best_seller 
DESC LIMIT 20

I really do appreciate you helping and am sorry for veering off the subject before.

28

i’m sorry, i can neither confirm nor dispute this claim without also seeing your data

would it be too much to ask you to create a test set?

because as things stand, i’m betting that all your ANDs taken in combination are eliminating everything that you think is supposed to be returned

p.s. why are you still using GROUP BY???

29

I’m more than happy to do anything but I don’t quite understand what you mean about creating a test set. Do you mean create a mini file that of the database where the query would return results based on the query I’m using?

Oops my bad, I’d forgotten to remove the GROUP BY but it’s definitely gone now :slight_smile:

30

yes, a test set – consisting of one or more CREATE statements, plus enough INSERT statements to load representative data

that way we can test the query, and inspect the data, to confirm if it’s working properly

31

Okay I think I’ve done that, these are two tables from the database, one which has the product and then the other that contains the attributes.Archive.zip (3.1 KB)

32

thank you for this… i’m a bit busy but i’ll get back to you

33

Thank you

34

okay, i could’ve done this earlier, i guess i was expecting some sort of complexity

here’s your feeds data –

937×105 22 KB

and here’s your attributes data –

937×491 102 KB

the reason your query returns no rows is because there is no row which satisfies ~all~ the WHERE conditions

35

i said earlier you may need to redesign your tables (learn about Third Normal Form)

i stand firmly behind that advice today

36

Thank you so much for helping, I’m really grateful. I’m happy to change the structure of the database however is necessary but to be honest I don’t know how to. I’m happy to pay if you could me get this working as soon as possible please.

Thanks again

37

here’s what you need –

keep your feeds table for the products

you’ll have to ditch the attributes table altogether, but don’t delete it yet because you might need to to populate the other new tables

then create new tables, one for each attribute type, if you don’t have these already

also create new relationship tables, one for each attribute type

see post #20 but forget about the foreign keys just yet

1 Like
38

I think I understand what you’re saying, I’ve now got tables for each attribute as well as what I’m guessing are the relationship tables. I hope you don’t mind but I’ve attached examples of both of those to make sure I’ve got that right. Presuming I’ve got that right will I need to change the query itself?Archive.zip (2.5 KB)

39

yes, you’re on the right track

but i would change this –

CREATE TABLE IF NOT EXISTS `dietary` ( `id` int(11) NOT NULL AUTO_INCREMENT, `product_id` int(11) NOT NULL, `dietary_id` int(11) NOT NULL, PRIMARY KEY (`id`) )
to this –

CREATE TABLE IF NOT EXISTS `dietary` ( `product_id` int(11) NOT NULL, `dietary_id` int(11) NOT NULL, PRIMARY KEY (product_id,dietary_id) )

as far as the query is concerned, yes, you sure will ahve to change it

40

okay so I need to have one enter per product id, do the dietary ids need to be separated by a comma or can I just have:

INSERT INTO dietary (product_id, dietary_id) VALUES (9038, ‘6 10’);