You can read about doing a join [dev.mysql.com] on the MySQL website. That is exactly want you want to do :-)

JAG

Thank you justageek, atleast now I know what I need to do. I read the JOIN syntax on the mysql reference manual several times but it keeps going over my head.

I have experienced this stump before in my self-teaching mysql + php venture - I find making these extra steps difficult.

I have searched for a simple tutorial using JOINS but I am having no luck and time is ticking away - could anyone post a tutorial or even write a short example for me?

Cheers.

A join simply connects one set of data with another using a common denominator. In your case the denominator is 'parent_id' in one table and 'id' in another.

So...by doing something like the following:

select t1.name, t2.c_name from content as t1 join categories as t2 on t1.parent_id = t2.id where t2.id = $parent_id

You should get what you want.

By saying 'as t1' and 'as t2' it just sets up an alias for the table names so you can just use t1.* and t2.* in the rest of the query.

By using the join with 'on t1.parent_id = t2.id' tells the query to only worry about the records that have the same id in both tables.

The 'where t2.id = $parent_id' further tells the query to just bring the results of the 2 tables back where the ids match your variable.

I haven't tested but it should work...I think :-)

JAG

Extremely helpful post there JAG, thankyou.

Thanks to your example above, I think I am almost there. I have changed the DB field names slightly so they are more clear for me.

So here is what I have so far:

$join=mysql_query("SELECT t1.title, t2.title FROM content as t1 join categories as t2 on t1.parent_id = t2.category_id where t2.category_id = $parent_id");
while($row=mysql_fetch_array($join)){

I am getting a mysql_fetch_array(): supplied argument is not a valid MySQL result resource.

It must only be a small thing, could you jag or anyone else shed some light?

I'll keep at it none the less..

Happy new year!

Echo out a mysql_error() and it will say what the problem is :-)

JAG

The problem was that I had thought about this way too hard and got ahead of myself; I was trying to use a WHERE clause when it wasn't needed.

I now need to get the parent_id for category_id - is this possible?

After doing the above join that gets an article and its parent category, I need the parent of that category both categories relate within the same table.

CATEGORIES
category_id¦title ¦parent_id
1 ¦example1¦2
2 ¦example2¦99

My goal is to have mysql echo out my url structure e.g:

www.domain.com/parent_title/parent2_title/article_title/

Is there an easier way of using these joins to do this or am I on the right track? I'm struggling alot with this.