MySQL result multiple arrays

Question

i.e : i have 2 tables

Product ( id, name )
Photo ( id, name, photo_id )

And I need to get result in array like this:

array(
 'id' => 1,
 'name' => product,
 'photos' => array(
      array('id' => 1, 'name' => 'photo1')
      array('id' => 2, 'name' => 'photo2')
  )
}

Is it possible in PHP using clear SQL?

I know that is possible to get 2 arrays and connect it but I have many records and I dont want to wase time to quering.

Try using nested queries maybe? You need to provide more information. Your table structure with some sample data along with the query that you've tried so far. — Maximus2012
– Maximus2012, Commented Apr 14, 2015 at 13:28
Why you are having id as well photo_id in your photo table 2 Photo ( id, name, photo_id ) just use product_id_fk instead of photo_id and your are done. — prashant thakre
– prashant thakre, Commented Apr 14, 2015 at 18:07

Dr. Z · Accepted Answer · 2015-04-14 13:37:50Z

1

You have to add a foreign_key in your photo table "product_id".

Then create a method getPhotos() in your Product class with will collect all photos for your product.

answered Apr 14, 2015 at 13:37

Dr. Z

2373 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

charmeleon · Accepted Answer · 2015-04-14 18:01:07Z

0

Is it possible in PHP using clear SQL?

Not in a single SQL call. With a single call, this is the closest you can get:

array(
 'id' => 1,
 'name' => product,
 'photo_id' => 1,
 'photo_name' => 'photo1')
),
array(
 'id' => 1,
 'name' => product,
 'photo_id' => 2,
 'photo_name' => 'photo2')
)

Your only choice for the format you want is to run queries separately or to combine them into the data structure you want.

answered Apr 14, 2015 at 18:01

charmeleon

2,7551 gold badge28 silver badges35 bronze badges

1 Comment

Micchaleq Over a year ago

Hi, all thank you for many answers. In my first post I have make mistake. In Photo table I Have product_id NOT photo_id ... pfff. So if I will use that query "SELECT * FORM product p LEFT JOIN photo ph ON p.id = ph.product_id GROUP BY p.id" I return only one photo for product. So how I have to do it?

moo · Accepted Answer · 2015-04-14 18:04:33Z

0

As mentioned, this is not possible with SQL. SQL is based on the relational model which is a 1-Normal-Form data model. That means, the result relation is also flat (no nested relations in a relation).

However, there are good frameworks which generate intermediary models in your corresponding target language (e.g. Python, Java, ...) that circumvent the impression of a flat data model. Check for example Django.

https://docs.djangoproject.com/en/1.8/topics/db/models/

Moo

answered Apr 14, 2015 at 18:04

moo

5268 silver badges23 bronze badges

Collectives™ on Stack Overflow

MySQL result multiple arrays

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related