来自多个表的MySQLi

Question

I have tried different ways to do this, but... So, my last option, ask for help.

I got 3 DB tables. In OBJECTS table I got stored every website OBJECTS, like filter names, SAM and SAMP names (specific for system). In ITEMS I stored all items for system (these are real world projects with some more options) and OBJECTS_LINKED, I stored every linked elements with OBJECTS and ITEMS.

Then, I got system, where user can choose some option from filter, then I got list of items... Example, in OBJECTS_LINKED I got 3 items with name - filter, these will be shown, but what I need, where is problem. I need to show these items in list like OBJECTS name ordered by ASC.

Right now, everything are working, except ordering. Code is written in PHP. I got SQL query, where I got these item id where name is Filter from objects linked, then there is loop, where I got all objects id where object name is SAM or SAMP and it is linked with item id, I got this from OBJECTS LINKED and OBJECTS with INNER JOIN... But problem is that, when I first got these item id, then they got in some order and in next loop it doesn't count my SQL QUERY ORDER BY, but he is going how loop told... so, I think, this can be made in one query without this loop, but I don't know how, because I am not good with SQL.

---- OBJECTS ----
id    -   content     -     name
1     -   1.2. Text   -     SAM
2     -   1.3. Text   -     SAMP
3     -   1.1. Text   -     SAM
4     -   Filter      -     Filter

---- ITEMS ----
id    -   some columns...
1     -   ...
2     -   ...
3     -   ...

---- OBJECTS_LINKED ----
id    -    obj_id    -     item_id    -    name
id    -       1      -        1       -    SAM
id    -       2      -        1       -    FILTER
id    -       4      -        2       -    SAM
id    -       3      -        3       -    SAMP

    $SQL = "SELECT * FROM OBJECTS_LINKED WHERE obj_id = '$obj_id' AND link_name = 'FILTER'";
    while(){
    $SQL = "SELECT ol.*, o.obj_content, i.item_type, i.item_kartina FROM OBJECTS_LINKED AS ol 
    INNER JOIN ITEMS AS i ON i.item_id = ol.item_id 
    INNER JOIN OBJECTS AS o ON o.obj_id = ol.obj_id
    WHERE ol.item_id = '$item_id' AND if(i.item_type = 'SAM', link_name = 'SAM', link_name = 'SAMP')
    ORDER BY o.obj_content ASC";
       while(){
        ---- list of items (SAM or SAMP) name ----
       }    
    }

Answer 1

I did it, but if someone would like to know how, so I post my solution here.

$SQL = "SELECT ol.*, o.obj_content, i.item_type, i.item_kartina 
FROM OBJECTS_LINKED AS ol 
INNER JOIN ITEMS AS i ON i.item_id = ol.item_id 
INNER JOIN OBJECTS AS o ON o.obj_id = ol.obj_id 
WHERE ol.item_id IN (SELECT item_id FROM OBJECTS_LINKED WHERE obj_id = '$obj_id' AND link_name = 'FILTER') AND if(i.item_type = 'SAM', link_name = 'SAM', link_name = 'SAMP') 
ORDER BY o.obj_content ASC";
    while(){
       /* LIST OF ITEMS ORDERED BY obj_content from OBJECTS table WHERE OBJECTS and ITEMS are linked */
    }