Search code examples
mysqlmultiple-select

MySQL multiple table select with WHERE not comparing 2 tables

I have the beginnings of a query:

SELECT tf_threads.*, tf_posts.* FROM tf_threads, tf_posts WHERE tf_threads.thread_id=54

Of course, it's just the start of one - I'm moving one step at a time so it will become much better as time passes. What I'm stuck on at the moment, however, is how to get a WHERE clause to compare a column in one table with a PHP variable, not another column.

The PHP I have is this:

SELECT $tf_threads.*, $tf_posts.* FROM $tf_threads, $tf_posts WHERE $tf_threads.thread_id=$tf_this_forum

The important bit here is $tf_threads.thread_id=$tf_this_forum. When I try this it simply returns no rows, with MySQL saying: Impossible WHERE noticed after reading const tables. Surely this should work fine? Obviously, it doesn't, so can anyone help out here please?

Thanks very much,

James

EDIT: I have also tried doing the query in pure MySQL like this:

SELECT tf_threads.*, tf_posts.* FROM tf_threads, tf_posts WHERE tf_threads.thread_id=54

Both WITH quotes round the 54 and without, with the same no rows result.

Solution

  • Your query is implicitly performing a CROSS JOIN and it appears to contain too many $ signs.

    I'd suggest the following extra changes:

    • Use ANSI-92 joins (the JOIN keyword) instead of ANSI-89 joins (the comma).
    • Use intval applied to $tf_this_forum to ensure that it is an integer.
    • Consider using bind parameters instead of constructing the query from strings dynamically.
    • Only use $ when you are referring to a PHP variable.

    Regarding the error Impossible WHERE noticed after reading const tables this means that no rows matched. Check that the row you are searching for actually exists in your database. It could be that it doesn't.