proper MySQL join solution?

phence

I have the following query that pulls out data to make up a navigation menu.

SELECT id, btn_txt FROM navigation WHERE parent="'.$parent.'";

I would like to condition this further by only pulling out those results that are 'enabled'. The problem is that the my 'status' column holding the information 'enabled' or not is in a different table called 'pages'.

how could I rewrite the above query to accomplish WHERE parent = $parent AND status = "enabled" [although status is in a separate table].

I am using MySQL 4.1.

I looked at a couple tutorials off Google search for mysql join, but i didn't fully understand.

I scrapped together this query

SELECT n.id, n.btn_txt, p.status FROM navigation n, pages p WHERE n.parent="'.$parent.'" AND p.status="enabled";

..but, it repeatedly displayed the results 12 times over... and still displayed results that were not 'enabled'.

Hope you can help.
Thank-you.

Roger_Ramjet

You should start all mysql research in the manual. Read up on JOINS and you will a) understand what a cartesian join is and why you are getting the results you are b) learn how to rewrite your join to get the results you want.

Most of the tutorials for mysql and php out there seem to be very out-of-date and full of bad coding practices, or rather a lot of them. This is probably because they have developed so rapidly that anything will become stale very quickly.

phence

Rodger I am fimilar to look at the manual first... and i have.

Could you at least let me know what kind of join I need?

I was just having a hard time understanding each join and exactly what each one will accomplish.

Not that I'm lazy... more along the lines of incompetent. 🙂

Thanks-you

Sayian

Hello,

what does the variable $parent hold in it? ill need to know this to be able to help you.

Or, what is the key relationship between the two tables?

thanks.

Roger_Ramjet

You need an inner join, the most basic and oft used type of join: it joins tables where there is a matching row in each table. You therefore need to specify which column in each table contains the data to match on: the join condition they talk about in the manual.

What you have is a cartesian join, specified by the use of the comma between the table names: every row in 1 table is joined to every row in the other, so if you had 10 rows in each table you would get 100 rows in the query resultset.

phence

Thanks Sayian for your willingness, and Thank-you Rodger very much for that hint!

SELECT navigation.id, navigation.link_loc, navigation.btn_txt, pages.status FROM navigation INNER JOIN pages ON navigation.link_loc = pages.page_name WHERE navigation.parent="'.$parent.'" AND pages.status="enabled";

That query seemed to do the trick!

Thanks again!