Why is this MySQL Join Statement not Working?

Question

Here is my code:

$query2 = mysql_query("SELECT * FROM categories WHERE parent = $id JOIN SELECT * FROM posts WHERE main_nav_page = '$idTwo'");

                    while ($row2 = mysql_fetch_assoc($query2)) {
                        $id   = $row2['id'];
                        $name = $row2['name'];
                        $slug = $row2['slug'];
                        $subMenuOrder = $row2['sub_menu_order'];

                        echo "<tr>\n";
                        echo "<td>&nbsp; -- $name</td>\n";
                        echo "</tr>\n";
                    }

Is my syntax wrong?

EDIT:

the error message is:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/studentw/public_html/new_nav.php on line 30

The given error message is one error, but not the first. It tells you the `mysql_query` call failed. Examine the output of `mysql_error` to see why. — outis, May 04 '10 at 02:05
As for `SELECT *`, read: http://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select — outis, May 04 '10 at 02:08

score 4 · Answer 1 · edited May 04 '10 at 02:31

4

Instead of that you probably want something more like:

SELECT * 
FROM 
       categories c 
    INNER JOIN 
       posts p ON c.categoryid = p.categoryid 
WHERE 
       c.parent = $id 
       AND p.main_nav_page = '$idTwo';

Note that the tables are joined, rather than select statements. Also, joins are specified in the FROM clause.

Try this:

$results = mysql_query("query here") or die(mysql_error());

edited May 04 '10 at 02:31

outis

75,655
22
151
221

answered May 04 '10 at 01:34

John Boker

82,559
17
97
130

is there a mysql error happening? can you run this query in phpmyadmin or some other tool to see the ouput? – John Boker May 04 '10 at 01:38
Running it in phpmyadmin doesn't work since those columns you reference in your code don't look like any I have in my table. – Timmy May 04 '10 at 01:42
Yeah, doing that with my query says I have an error in my sql syntax, so I'm wondering is my original syntax wrong? – Timmy May 04 '10 at 02:01
i've never seen a query like that, i would guess it's wrong. What is your table structure and what are you trying to get, maybe we can help. – John Boker May 04 '10 at 02:05
@Timmy: Note that `or die(mysql_error())` should only be used to tell us what's going wrong. It should never be used in production code, and rarely used in development code due to its problems with stability and security (http://www.phpfreaks.com/blog/or-die-must-die, http://msdn.microsoft.com/en-us/library/ms995351.aspx#securityerrormessages_topic2). Better to handle errors properly from the get-go. – outis May 04 '10 at 02:07
@Timmy: as for syntax, the original query is indeed invalid. You join tables, not selects. – outis May 04 '10 at 02:09
@outis Ok I see, I should join tables not selects. This was my attempt at doing that but it didn't work: SELECT * FROM categories WHERE parent = '$id' JOIN main_nav_page = '$idTwo' – Timmy May 04 '10 at 02:27
@Timmy: take a closer look at John Boker's answer. It shows you the correct syntax (edited to make this more obvious). Joins are part of the table clause. If you're ever in doubt as to syntax, check the manual (e.g. http://dev.mysql.com/doc/refman/5.1/en/select.html) rather than guessing. – outis May 04 '10 at 02:31

score 1 · Answer 2 · answered May 04 '10 at 01:34

1

I think there is a small syntax error. Looks like you missed the single quotes (' ') around "$id" in the WHERE parent-clause. Should be like this, I'm guessing:

SELECT * FROM categories WHERE parent = '$id' ...

answered May 04 '10 at 01:34

Lars Andren

8,601
7
41
56

Actually $id is a numeric value so no single quotes are nessecary. $idTwo is not so I included it there. – Timmy May 04 '10 at 01:38

Why is this MySQL Join Statement not Working?

2 Answers2