Show the data with mysql_query php

Question

I have 2 tables on database, table 'songs', and 'genre'.

Table 'songs' has 3 columns: id_song (PK), song_title, lyric, id_genre
Table 'genre' has 2 columns: id_genre (PK), genre

in table 'genre', i have:

|| id_genre | genre ||

||.......1.......|..pop...||

||.......2.......|..rock..||

||.......3.......|..jazz...||

||.......4.......|.classic.||

Relation between 2 tables:

song.id_genre FK to genre.id_genre

I have a variable like this:

$search_genre = 'pop';

and I want to search song by $search_genre.

$query = mysql_query("SELECT song.id_song, song.song_title, genre.genre FROM song WHERE genre='$search_genre' INNER JOIN genre ON song.id_genre=genre.id_genre ORDER BY id_song");

But, it doesn't work. What should I do? Thanks for your help.

Question 2: How to show the result in php?

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — Madara's Ghost, May 14 '13 at 07:51

score 7 · Answer 1 · edited Jun 20 '20 at 09:12

First!

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

Second!

If you've already started with mysql_* at the very least escape your parameters! Otherwise you're leaving a gaping SQL injection security vulnerability waiting for the next l33t wannabe hacker to hack into your site!

$search_genere = mysql_real_escape_string($search_genere);
$query = mysql_query("SELECT song.id_song, song.song_title, genre.genre FROM song INNER JOIN genre ON song.id_genre=genre.id_genre WHERE genre.genre='".$search_genre."' ORDER BY id_song");

But please please do read the link in the box above.

The reason you've had this error is because of a syntax error.

can you help me, how to show the result in php? Thanks. – bob May 14 '13 at 08:17 — bob, May 14 '13 at 08:17

score -2 · Answer 2 · answered May 14 '13 at 07:47

Your JOIN query is wrong you use INNER JOIN after FROM

Try this:

 $query = mysql_query("SELECT song.id_song, song.song_title, genre.genre FROM song INNER JOIN genre ON song.id_genre=genre.id_genre WHERE genre='%$search_genre%'  ORDER BY id_song");

If you querying a search you can use wildcards such as %

e.g.

query = mysql_query("SELECT song.id_song, song.song_title, genre.genre FROM song INNER JOIN genre ON song.id_genre=genre.id_genre WHERE genre='%$search_genre%'  ORDER BY id_song");

You can check [this comment](http://stackoverflow.com/questions/16537583/show-the-data-with-mysql-query-php#comment23751924_16537724) for a reason why. — Ja͢ck, May 14 '13 at 08:12

abdul quadir · Answer 3 · 2013-05-14T08:00:23.963

-3

    $query = mysql_query("SELECT song.id_song, song.song_title, genre.genre FROM song INNER JOIN genre ON song.id_genre=genre.id_genre WHERE genre.genre='".$search_genre."' ORDER BY id_song");

    use this.it will work.

if(mysql_num_rows($query) > 0){
   while($row = mysql_fetch_array($query)){
     echo $row['id_song'];//song id
     echo $row['song_title'];//song title
     echo $row['genre'];//song genre
   }
}

edited May 14 '13 at 08:00

answered May 14 '13 at 07:48

abdul quadir

24
1
7

I'm so sorry, i forget to ask 2nd question, how to show the result in php? Thanks for your help. – bob May 14 '13 at 07:54

Show the data with mysql_query php

3 Answers3

First!

Second!