Correct MySQL DB Query and processing

Question

I ve got a table called EXP it looks like this

enter image description here

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?

• Elias Van Ootegem: Use prepared statements, your first query is vulnerable to injection. Avoid using SELECT * whenever you can (which is 99.99% of the time), too, and only SELECT what you need

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