Speed mysql query inside foreach loop

[pietrofxq]

I have a table called "playlists", and a table called "musics". In the musics table, there is a column playlist_id which references the playlist that each music is contained.

 

I'm using api calls to display information on the site with JavaScript, so I need to return a JSON.

 

I need the json with the following structure:

 

[

  Playlists: [

                  {

                    Name: "etc",

                    musics: [

                                   {

                                     name: "teste.mp3"
                                  },

                                  { 

                                    name: "test2.mp3"

                                   }

                                 ]

                     },

                     ...

                 ]

]

 

And this is my code: 

 

$query = $con->prepare("SELECT * FROM playlists WHERE user_id = :id");

 

$query->execute(array("id" => $userID));

$playlists = $query->fetchAll(PDO::FETCH_ASSOC);

 

foreach ($playlists as &$key) {

   $query = $con->prepare("SELECT * FROM musics WHERE playlist_id = :id");

   $query->execute(array("id" => $key['ID']));

   $songs = $query->fetchAll(PDO::FETCH_ASSOC);

   $key['musics'] = $songs;

 }

 

There's a way to avoid this loop?

Edited December 3, 2014 by pietrofxq

[mikosiko]

There's a way to avoid this loop?

Yes and No..Well, it is way to eliminate the extra prepare/execute/query inside the loop.. use a JOIN between the 2 tables, get the resultset of that JOIN and traverse (with a loop) it to produce your JSON in the format that you want/need

[Psycho]

1. Don't use "SELECT *" if you don't need ALL the fields. Select only the fields you need. It puts overhead on the server that is unnecessary.

 

2. Lear to use JOINs - that is the whole point of a relational database.

 

3. Don't run queries in loops. There are very few instances where you need to run loops. Until you know when those are, look for a non looping solution (hint, this usually requires a JOIN).

 

This should be all you need

 

$sql = "SELECT m.*
        FROM musics m
        JOIN playlists p ON m.playlist_id = p.id
        WHERE p.user_id = :id";
$query = $con->prepare($sql);
$query->execute(array("id" => $userID));
$songs = $query->fetchAll(PDO::FETCH_ASSOC);
$key['musics'] = $songs;

[pietrofxq]

 

1. Don't use "SELECT *" if you don't need ALL the fields. Select only the fields you need. It puts overhead on the server that is unnecessary.

 

2. Lear to use JOINs - that is the whole point of a relational database.

 

3. Don't run queries in loops. There are very few instances where you need to run loops. Until you know when those are, look for a non looping solution (hint, this usually requires a JOIN).

 

This should be all you need

$sql = "SELECT m.*
        FROM musics m
        JOIN playlists p ON m.playlist_id = p.id
        WHERE p.user_id = :id";
$query = $con->prepare($sql);
$query->execute(array("id" => $userID));
$songs = $query->fetchAll(PDO::FETCH_ASSOC);
$key['musics'] = $songs;

 

Sorry, but i didn't understand. I still don't see how i can create a property "musics" for each entry on the $playlists array with the songs of the playlist. This sql query returns me all the musics from all the playlists of the logged user, but how i'd do the loop without the query inside it?

[Psycho]

You want to loop over the results of the query, you don't want to run queries in loops as you were previously doing. So, you will want to iterate through the results of the query and create an array in the format you need, then convert to JSON.

 

Based on your first post, this may be what you need:

 

 

$sql = "SELECT m.NAME_OF_FIELD_THAT_HOLDS_THE_FILENAME
        FROM musics m
        JOIN playlists p ON m.playlist_id = p.id
        WHERE p.user_id = :id";
$query = $con->prepare($sql);
$query->execute(array("id" => $userID));
$results = $query->fetchAll(PDO::FETCH_ASSOC);
 
//Create multidimensional array of songs with keys
$music = array();
foreach($results as $songTitle)
{
    $music[] = array('name' => $songTitle);
}
 
//Create playlist array
$playlist = array(
    'Playlists' => array(
        'Name' => 'etc',
        'musics' => $music
    )
);
 
//convert to JSON
$output = json_encode($playlist);