Correct MySQL DB Query and processing

Question

I ve got a table called EXP it looks like this

The table contains

• REG_ID - (id of the registred users)
• TRA_TYPE - (type of transportation the user chose 1-export 2-import)
• PRO_ID - (product id in rande [1-16])

When a user registers he can choose transportation type EXPORT and products for EXPORT or import AND PRODUCTS for IMPORT or BOTH

Then his data (name, password) is inserted into REGISTRATION table and (id form registration tabl, type/s of transportation and selected products into EXP table)

I need to retrieve an array to process the data, I expect to get this array

Array
(
    [0] => Array
        (
            [id] => 1
            [tra_type] => 1
            [prod_import] => Array
                (
                    [0] => 2
                    [1] => 6
                    [2] => 10
                )

        )


)

I use this block of code to get it

    //get all the rows containing choosen products
    $mas = array(2,6,10);
    $implode_mas = implode(',',$mas);
    $g = $qw->query("select * from exp where pro_id in ($implode_mas) and tra_type = '1';");
    while($o = $g->fetch(PDO::FETCH_ASSOC)){
        $k[] = $o;
    }

   //extract only IDs
    for($hk=0;$hk<count($k);$hk++){
        $O[] = $k[$hk]['reg_id'];
    }

    //removing equal IDs 
    $O = array_values(array_unique($O));

    //define an empty array
    $Ty = array();

    //run for loop
    for($ep=0;$ep<count($O);$ep++){

        // getting current ID
        $cId = $O[$ep];

        //add id to array
        $Ty[$ep]['id'] = $cId;

         ///add tra_type to array
        $Ty[$ep]['tra_type'] = $k[$ep]['tra_type'];


        for($hj=0;$hj<count($k);$hj++){
         // if the array IDS if equal IDs from $O array  add its pro_id to array $Ty
         if($k[$hj]['reg_id'] == $cId )
           $Ty[$ep]['prod'][]= $k[$hj]['pro_id'];
        }


    }

    print_r($Ty);

Could give me advice how to do the same in an easier way?

Answer 1

Change SQL Query may help you in someway, like this,

SELECT reg_id, tra_type, GROUP_CONCAT(pro_id SEPARATOR ',') AS pro_ids 
FROM exp 
WHERE pro_id in ($implode_mas)
AND tra_type = '1'
GROUP BY reg_id;

Result will be like this

reg_id    tra_type    pro_ids
1         1           2,6,10
4         1           10

Then you can split it to array