Query 2 tables join

Question

I want to get all the films whose genre is equal to ID_GENRE = 8. This is the query that I'm doing:

http://l4c.me/fullsize/2-tablas-1434140362.png

$query_GetSimilar = sprintf("SELECT * FROM z_movie,z_movie_genre ORDER BY z_movie.visits DESC WHERE z_movie_genre.id_genre = 8 LIMIT 18");
$GetSimilar = mysql_query($query_GetSimilar, conect::dbconect()) or die(mysql_error());
$row_GetSimilar = mysql_fetch_assoc($GetSimilar);
$totalRows_GetSimilar = mysql_num_rows($GetSimilar);

But I jump the next error

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 'WHERE z_movie_genre.id_genre = 8 LIMIT 18' at line 1

Answer 1

Note:

• WHERE should be first before ORDER BY
• You can use INNER JOIN to get two connecting tables, assuming that they have connecting id/column.

For example, both z_movie and z_movie_genre table has the column id_genre to connect each other, you can try this:

$query_GetSimilar = sprintf("SELECT * FROM z_movie
                               INNER JOIN z_movie_genre ON z_movie.id_genre = z_movie_genre.id_genre
                               WHERE z_movie_genre.id_genre = 8
                               ORDER BY z_movie.visits DESC
                               LIMIT 18");

Answer 2

Make a simple search, and see if you can put 'order by' before 'where'.

Answer 3

ok, now my query is:

$query_GetSimilar = sprintf("SELECT * FROM z_movie,z_movie_genre WHERE z_movie_genre.id_genre=8  ORDER BY z_movie.visits DESC LIMIT 18");
$GetSimilar = mysql_query($query_GetSimilar, conect::dbconect()) or die(mysql_error());
$row_GetSimilar = mysql_fetch_assoc($GetSimilar);
$totalRows_GetSimilar = mysql_num_rows($GetSimilar);

but I do not have the expected results