SQL query from PHP with array fields

Question

I'm developping a website where there are ajax request to get data from a mySQL database.
To simplify the problem, let's say I have the following database structure :

Table OBJECT
ID_OBJECT (int)
NAME_OBJECT (varchar)

Table IMAGE
ID_OBJECT (int) -> foreign key from OBJECT
URL (varchar)

So every object can have between 0 and n images attached to it. I'm stuck at writing the php script that request the data.

To simplify again, let's say I want to get all objects with all the images they have (but in the future I'll want to get any specific object with all its picture too)

I have written this PHP script :

$sql = "select 
               ID_OBJECT,
               NAME_OBJECT,
               URL
          from 
               OBJECT,
               IMAGE
          where 
               OBJECT.ID_OBJECT  = IMAGE.ID_OBJECT";

$statement=$db->prepare($sql);
$statement->execute();
$result=$statement->fetchAll(PDO::FETCH_ASSOC);
header('Content-Type: application/json; charset=utf-8');
echo json_encode($result);

The problem I have is as soon as I add "URL", "IMAGE" and the where statement in the sql query, it doen"t echo anything.(I have several objects and images in my databases with the propers ids). I don't understand where I'm making a mistake here.

Obviously you have an error in your query Ambigious field... You should set what ID_OBJECT field you want - from first or second table. — u_mulder
– u_mulder, Commented Aug 3, 2014 at 16:01

manuelmhtr · Accepted Answer · 2014-08-03 16:04:02Z

2

ID_OBJECT is in both tables, so MySQl doesn't know which one to show. Try specifying like this:

select 
           OBJECT.ID_OBJECT,
           OBJECT.NAME_OBJECT,
           IMAGE.URL
      from 
           OBJECT,
           IMAGE
      where 
           OBJECT.ID_OBJECT  = IMAGE.ID_OBJECT

answered Aug 3, 2014 at 16:04

manuelmhtr

3563 silver badges5 bronze badges

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

Comments

u_mulder · Accepted Answer · 2014-08-03 16:04:34Z

1

Obviously you have an error in your query. You should set what ID_OBJECT field you want - from first or second table. Rewrite your query like:

select 
       o.ID_OBJECT,
       o,NAME_OBJECT,
       i.URL
from 
       OBJECT o,
       IMAGE i
where 
       o.ID_OBJECT  = i.ID_OBJECT

answered Aug 3, 2014 at 16:04

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

7 Comments

singe3 Over a year ago

That solved part of the problem, thank you, however when an object has several images is it possible to make the URL field to contain an array of URL rather than having a new row for every other URL ?

u_mulder Over a year ago

You can do this way, but it's a bad database design.

singe3 Over a year ago

It's a basic 1-n relation, isn't it ? What's wrong about it

u_mulder Over a year ago

Obvioulsy you will have to write extra code when you'll have to add or update your array of urls.

singe3 Over a year ago

I just want to keep this structure but that mysql returns directly a row formatted like this : {"ID_OBJECT":"1","NAME_OBJECT":"a name","URL_ARRAY":[{"URL":"an url"},{"URL":"another url"}] } Is it possible or do I have to create it myself in php ?

|

Collectives™ on Stack Overflow

SQL query from PHP with array fields

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related