php - Codeigniter/PHP，2 个 Mysql 表，多个查询

Question

我有 3 个表，其中 1 个是 item表，一个是note表，另一个是 note image表。

当用户查看项目详细信息时，会拾取该项目的所有注释(注释表中有一个 item_id 字段)

笔记可以附加多个图像，这些图像存储在平面文件中，但由“笔记图像”表引用。

现在，当显示项目详细信息时，我运行一个查询来获取项目的所有注释...足够简单，然后循环这些结果以将它们输出到页面上。

将图像添加到笔记后现在出现问题，您将如何查询某个项目的所有笔记

SELECT * FROM notes WHERE item = 1

那么你如何循环遍历结果数组来获取注释的所有注释图像

SELECT * FROM note_img WHERE note_img_noteid = 27

这让我有点头疼，因为我无法想象如何获取结果并将其输出到PHP中.

---编辑---

我想我可能明白了，

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

然后我用这个:

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;
    }
}

Answer 1

当你没有在表格中发布你的关系但进行一些假设时很难知道

 $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;


   }