banana_man — 2013-01-24T07:30:52-05:00 — #1
I have been working with PHP and MySQL for some time now but have always only ever need to do fairly straight forward queries. Now however i have a larger relation database and i am finding the queries are getting a bit more complicated. I'm trying to figure out how best to deal with this. The best option is probably to sit down and learn how to write more complex MySQL queries, option 2 is to add more direct relational fields to my tables but then i am duplicating content, or the third option is to break down my MySQL queries into a few straight forward PHP queries.
In the example below i am trying to print the table_1_title but only when the table_3_country is set to 'ireland' for example. Table 1 is not directly connected to table 3 however. Instead Table 1 connects to Table 2 with a foreign key in Table 1. Table 2 is then connected to Table 3 by a foreign key and it is here that i check what country this user is located in.
Can anyone tell me how a MySQL query would be constructed for something like this?
I figured out a similar issue with a different query yesterday using INNER JOINS but even then i had another more complex query where another table was also needed after the INNER JOIN and i was stuck again.
Getting more and more confused trying to even figure out in my head what i want to achieve the more i get into it
r937 — 2013-01-24T07:46:45-05:00 — #2
for sure the first option is best... option 2 is horrible, and option 3 works real well occasionally but will often be lacking in performance
a simple join will do it
ON table2.table_2_id = table1.FK_table_2_id
ON table3.table_3_id = table2.FK_table_3_region
AND table3.table_3_country = 'ireland'
banana_man — 2013-01-24T10:42:27-05:00 — #3
Thanks! That's got it working now. I had seen two INNER JOINs being used in one query but didnt know how to set it up.
banana_man — 2013-01-24T10:51:55-05:00 — #4
. When i try to order the results it doesnt seem to have any effect.
I am using:
but if i change it to DESC it outputs in the same order. Should i be using some different syntax to achieve this?
r937 — 2013-01-24T11:21:46-05:00 — #5
sorry, i can't help you as long as we're talking about fictitious tables
show your real table design and your real query, and maybe it will make more sense