0
\$\begingroup\$

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?

\$\endgroup\$
  • 2
    \$\begingroup\$ 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 \$\endgroup\$ – Elias Van Ootegem Oct 2 '14 at 10:20
1
\$\begingroup\$

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

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.