CODEWITHSUNDEEP
phpmysqlarrays
Micchaleq
Micchaleq

Reputation: 433

MySQL result multiple arrays

i.e : i have 2 tables

  1. Product ( id, name )
  2. 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.

Upvotes: 0

Views: 111

Answers (3)

moo
moo

Reputation: 526

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

Upvotes: 0

charmeleon
charmeleon

Reputation: 2755

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.

Upvotes: 0

Dr. Z
Dr. Z

Reputation: 237

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.

Upvotes: 1

Related Questions