MYSQL JOIN

[scraptoft]

This is my first time working with MYSQL JOINS.

I have two tables

[u]Content:[/u]
title|parent_id

[u]Categories:[/u]
category_id|title

I want to query CONTENT and get TITLE and PARENT_ID. I then want to query the CATEGORIES table and get the TITLE where category_id = parent_id.

Heres what I have:

[quote]$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)){
extract($row);
}[/quote]

The error:
[quote]Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in join.php on line 7
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1[/quote]

[utexas_pjm]

The SQL looks ok.  I would check the contents of $parent_id.  My guess would be that it is not being set correctly.  So you're ending up with a SQL query like:

[code]
SELECT t1.title, t2.title FROM content as t1 join categories as t2 on t1.parent_id = t2.category_id where t2.category_id =
[/code]

and throwing an error.

Best,

Patrick

PS

It's usually a good idea to enclose variables in single quotes such that null input will not cause a MySQL error, i.e.,


[code]
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'
[/code]

[scraptoft]

You are right on it utexas_pjm your input was a massive help.

I have checked and every record in 'content' has a PARENT_ID.

I tried replacing the '$parent_ID' with a parent_id number i.e '20' and it worked.

I can't work out why it isn't working when I use the variable '$parent_id'.  What I am trying to do is get the last 10 records in by ID ASCENDING order.


ps. sorry phpfreaks I just realised there is a MYSQL specific forum so this could be in the wrong place.

[utexas_pjm]

You'll need to post more code for us to help you figure out why $parent_id isn't being set.

[code]
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'
ORDER BY t1.parent_id ASC
LIMIT 10
[/code]

[scraptoft]

utexas_pjm I have been trying to solve this for days and you have helped me within 10 minutes - I can't thank you enough for your generous support.

The way you have broke it down using several lines has also made me actually fully understand the code I was working with - it's not guess work anymore!

I was getting too far ahead of myself - the WHERE clause wasn't nercersarry for what I wanted so I just removed it and it's working great.

Thank you.