Codeigniter / PHP, 2 Mysql Tables, Multiple Queries

Question

I have 3 tables 1 is an item table, one is a note table and the other is a note image table.

When a user views item details, all notes are picked up for that item (there is a item_id field in the note table)

The notes can have multiple images attached to them these are stored in flat file but are referenced by the "note image" table.

Now when displaying item details I run a query to get all notes for a item... simple enough, then these results are looped through to output them onto the page.

Problem now arises after adding images to notes, how would you go about querying all notes for a item say

SELECT * FROM notes WHERE item = 1

then how would you loop though the result array getting all note images for a note say

SELECT * FROM note_img WHERE note_img_noteid = 27

Its hurting my head a little because I can't visualize how to get the results and output them in PHP.

---EDIT---

Think I may of got it,

SELECT
d.door_note_id,
d.door_note_doorid,
d.door_note_timestamp,
d.door_note_editedtime,
d.door_note_text,
u.user_name AS created_by,
e.user_name AS edited_by,
i.door_img_id AS img_id,
i.door_img_url AS img_url

FROM
user u,
door_note d

LEFT JOIN
user e
ON
user_id = d.door_note_editeduserid
LEFT JOIN 
door_img i 
ON 
door_img_noteid = d.door_note_id
WHERE
d.door_note_doorid = 214
AND
u.user_id = d.door_note_userid

Then I use this:

foreach ($result->result() as $row){
    if(!isset($my_items[$row->door_note_id])){ //the note id becaoms a key
        //here you set up an array for all the note details
        $my_items[$row->door_note_id] = array('door_note_id'=>$row->door_note_id, 
        'door_note_doorid'=>$row->door_note_doorid, 
        'door_note_timestamp'=>$row->door_note_timestamp, 
        'door_note_editedtime'=>$row->door_note_editedtime, 
        'door_note_text'=>$row->door_note_text, 
        'created_by'=>$row->created_by, 
        'edited_by'=>$row->edited_by, 
        'images'=>array());
    }
    //if the note has any images add them to the images array for that note.
    if(isset($row->img_url)){
        $my_items[$row->door_note_id]['images'][] = $row->img_url;
    }
}

I am thinking I might need to do 2 queries because of the fact images on notes are optional but I don't know :S. — Someone
– Someone, Commented Jun 4, 2013 at 9:50

Elijan · Accepted Answer · 2013-06-03 18:23:17Z

1

Its very hard to know when you haven't post your relationships in a table but taking some assumptions

 $query = "SELECT items.id as item_id,  items.name as item_name, notes.id as note_id,
    notes.description as note_description, note_image.image as note_image  from notes 
    LEFT JOIN  notes  ON items.id = notes.item_id 
    LEFT JOIN  note_image  ON notes.id = note_image.note_img_noteid";

//this wil fetch all you items with description, notes and images, because item can have multiple notes, your result wil have multiple entires of the item. so you have to index correctly to use in views

$result = $this->db->query($query)

$my_items = array();

foreach ($result->result() as $row){

     if(!isset($my_items[$row->item_id])){ //you item it becaoms a key
        //here you set up an array for all your items
        $my_items[$row->item_id] = array('item_name'=>$row->item_name, 'notes'=>array());
      }
     //here you stroe all images fro a note
     if(!isset($my_items[$row->item_id]['notes'][$row->note_id])){ 

           $my_items[$row->item_id]['notes'][$row->note_id] = array('note_description'=>$row->note_description, 'images'=>array());

       }

       $my_items[$row->item_id]['notes'][$row->note_id]['images'][] = $row->note_image;


   }

answered Jun 3, 2013 at 18:23

Elijan

1,4161 gold badge8 silver badges11 bronze badges

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

1 Comment

Someone Over a year ago

This looks like it should work, I will need to change the names up a bit etc but I will post back when I test it out, just sorting out another issue I found with my image upload script >< thanks!

Collectives™ on Stack Overflow

Codeigniter / PHP, 2 Mysql Tables, Multiple Queries

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related