filter json results based on value with url

Question

I am running a Mysql query in a php file and parsing it as json as follow:

$json_response = array();

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $row_array['artist'] = $row['artist'];
    $row_array['song'] = $row['song'];

    //push the values in the array
    array_push($json_response,$row_array);
}

echo json_encode($json_response);

There are thousands of entries; Is there any way I can filter the JSON results based on a value? something like: mylink.php/artist1 or mylink.php?artist=1

I would really appreciate any sort of ideas.

thanks

What is your MySQL query (SELECT statement), and is this for a website? How does a user interact with this code? — JasonCG, Dec 05 '14 at 22:31
Well, the reason I am doing this is because there are hundreds of artists that each time one will be accessed to return their songs.. And yes it is for a website but later on it will be accessed in an ios app the retreive the necessary info. — airflow, Dec 06 '14 at 03:59

score 0 · Answer 1 · answered Dec 05 '14 at 22:48

0

Well you should filter the result with your SQL, not with PHP...

Try to Google for "MySQL SELECT FROM WHERE" it will easily help you

answered Dec 05 '14 at 22:48

Dennis Weidmann

1,942
1
14
16

score 0 · Accepted Answer · answered Dec 05 '14 at 22:51

0

You can do it so:

// Get artist info
$artist_id = isset($_GET['artist']) ? intval($_GET['artist']) : 0;
$sql = "SELECT * FROM artist";
if($artist_id)
    $sql .= " WHERE artist=$artist_id";
mysql_query($sql);

answered Dec 05 '14 at 22:51

cybersoft

1,453
13
31

score 0 · Answer 3 · edited May 23 '17 at 11:57

First, the MySQL API you are using is deprecated and will eventually be removed. It is recommended you switch to MySQLI or PDO for any new development.

In PHP to get URL parameters user the $_GET superglobal.

$artist = $_GET['artist'];

You must then bind the paramter to your SELECT statement. Here is why it would be best to switch to MySQLI or PDO because the mysql_* functions in PHP do not support prepared statements and parameterized queries. Read more at How can I prevent SQL injection in PHP?

That said, using mysql_* here is a way to pass the artist into your query:

// Query
$query = sprintf("SELECT * FROM mytable WHERE artist='%s'",
            mysql_real_escape_string($artist));

$result = mysql_query($query);


$json_response = array();

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $row_array['artist'] = $row['artist'];
    $row_array['song'] = $row['song'];

    //push the values in the array
    array_push($json_response,$row_array);
}

echo json_encode($json_response);

filter json results based on value with url

3 Answers3