return multiple arrays from a query

Question

i have 2 tables

1st table called services has id_service, name, date. description 2nd table called services_images has id_img, img_name,id_service

Let's say now that I have to, with one query (if possible), return 2 arrays

1st array with fields from one specific id from table "services" 2nd array with fields from all the images related to the selected id in "services" from table "services_images"

or better yet, only one array with the same data as said in 1, with an array INSIDE called "images" that has all the images listed from the table "services_images"

I need this to process and show data in a html page and this is the only way.

If I cant do it in mysql how can I arrange this in PHP, the only thing I can think of is 2 queries

Plus I'm always trying to improve my skills since I used to make single queries for everything, are there cases where making 1 query it's just impossible? Thanks!

Answer 1

EDIT: i see what you mean now - i mis interpreted the first time.

The only way to do this would be to do more than one query. You would have to do something like:

$sql = "SELECT * FROM services WHERE id_service=$id";
$result = mysql_query($sql);
$services = mysql_fetch_array($result);

$new_sql = "SELECT * FROM services_images WHERE id_service=$id";
$new_result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
     $serivices_images[] = $row;
}

Acctally I change my mind again.. try this:

SELECT services.*,services_images.* FROM services, services_images WHERE services.id_service=services_images.id_service AND services.id_service=$id

now when you do while($row = mysql_fetch_array($sql_query_result)) you might return ALL the rows... but I dunno. Just a guess.

Answer 2

For eaxmple with PDO:

$db = new PDO($dsn, $user, $pass);

$sql = 'SELECT services.id_service, services.name, services.date, services.description, services_images.id_image, services_images.img_name
FROM services, services_images
WHERE services.id_service = services_images.id_service
ORDER BY services.id_service';

$services = array();

foreach($db->query($sql) as $row)
{
   $serviceId = $row['id_service'];

   if(!array_key_exists($serviceId, $services))
   {
      $services[$serviceId] = array(
          'name' => $row['name'],
          'date' => $row['date'],
          'description' => $row['description'],
          'services_images' => array()
      );
   }

   $imgId = $row['id_img'];
   $services[$serviceId]['services_images'][$imgId] => array(
      'id_image' => $imgId,
      'img_name' => $row['img_name']
   ); 
}

print_r($services);

Beware though.. if the two tables youre joining (in this case services and services_images) have any columns with the same name you will only get the value for the last one retrieved in the row unless you alias them or exclude them form your select statement.

Additionally, if the size of your results is big you may have to use two queries similar to what Thomas has suggested because you may not have enough memory to hold the complete array structure.