phpmyadmin Question posted in
The official documentation can be found here.

Two while loops to single array? – Phpmyadmin

I’m trying to take parts number from two different tables and put them in the same array, then rearrange them.

Strangely it gives parts number twice, but when I run the same query in phpmyadmin it gives each part number once. I spend whole day but could not correct this.

//first query

$finalData = array(); $sql="SELECT
    jobc_parts_p.part_no,
    SUM(jobc_parts_p.issued_qty) AS sale_qty  FROM
    `jobc_parts_p`  WHERE DATE_FORMAT(jobc_parts_p.date_time,'%Y-%m-%d') BETWEEN '".$f."' AND '".$t."'

GROUP BY jobc_parts_p.part_no";


$result = $conn->query($sql);
while($row = $result->fetch_assoc()){   
 $finalData[$row['part_no']][] = $row;
}

//second query 

  $sql2="SELECT
        jobc_consumble_p.part_no,
        SUM(jobc_consumble_p.issued_qty) AS csale_qty
    FROM
        `jobc_consumble_p`  
     WHERE DATE_FORMAT(jobc_consumble_p.date_time,'%Y-%m-%d') BETWEEN '".$f."' AND '".$t."'
    GROUP BY
    jobc_consumble_p.part_no";

$result = $conn->query($sql);
while($row = $result->fetch_assoc()){   
 $finalData[$row['part_no']][] = $row;
}

/// rearanging data…… 

$rearrangedFinalData = array();
foreach($finalData AS $first) {
    foreach($first AS $data) {
        $temp = array();
        $temp['part_no'] = $data['part_no'];
        $temp['sale_qty'] = isset($data['sale_qty']) ? $data['sale_qty'] : $data['csale_qty'];
        $rearrangedFinalData[] = $temp;
    }
}

//output result

foreach($rearrangedFinalData AS $row) {
         $sr++;


            echo "<tr><td>$sr</td>
            <td colspan='2' >",$row["part_no"],"</td>                       
            <td align='center'>",$row["sale_qty"],"</td>
            </tr>";      
     }

RESULT

1    10R46    2
2    10R46    2
3    10R91    1
4    10R91    1
5    10M95    3
6    10M95    3

What i want:

1    10R46    2
2    10R91    1
3    10M95    3

First query print_r($finalData);

Array ( 
[10R46    ] => Array ( [0] => Array ( [part_no] => 
10R46     [sale_qty] => 1 ) )
[10R91    ] => Array ( [0] => Array ( [part_no] => 
10R91     [sale_qty] => 3 ) ))
Tags:

3

Answers


  1. 0

    Because, you are looping twice:

    Modify

    $rearrangedFinalData = array();
foreach($finalData AS $first) {
    foreach($first AS $data) { // <-- Remove this extra loop.
        $temp = array();
        $temp['part_no'] = $data['part_no'];
        $temp['sale_qty'] = isset($data['sale_qty']) ? $data['sale_qty'] : $data['csale_qty'];
        $rearrangedFinalData[] = $temp;
    }
}

    To:

    $rearrangedFinalData = array();
foreach($finalData AS $first) {
        $temp = array();
        $temp['part_no'] = $first['part_no'];
        $temp['sale_qty'] = isset($first['sale_qty']) ? $first['sale_qty'] : $data['csale_qty'];
        $rearrangedFinalData[] = $temp;
}
    Login or Signup to reply.
  2. 0

    I think your problem lies within the $finalData where you store same results twice. (ofcourse from different tables though).

    the first query creates a result of

    $finalData = array(
'10R46' => array(0 => array('part_no' => 10R46, 'sale_qty' => 2)),
'10R91' => array(0 => array('part_no' => 10R91, 'sale_qty' => 1)),
'10M95' => array(0 => array('part_no' => 10M95, 'sale_qty' => 2))
);

    then you run the second query and ADD to the finaldata again with this $finalData[$row['part_no']][] = $row;

    so the $finalData is now something like

    $finalData = array(
'10R46' => array(0 => array('part_no' => 10R46, 'sale_qty' => 2), 1 => array('part_no' => 10R46, 'csale_qty' => 2)),
'10R91' => array(0 => array('part_no' => 10R91, 'sale_qty' => 1), 1 => array('part_no' => 10R91, 'csale_qty' => 1)),
'10M95' => array(0 => array('part_no' => 10M95, 'sale_qty' => 3), 1 => array('part_no' => 10M95, 'csale_qty' => 3))
);

    That is the reason why you double loop it and also get double results.

    So i would combine the queries.

    $sql="SELECT
jobc_parts_p.part_no,
SUM(jobc_parts_p.issued_qty) AS sale_qty,
SUM(jobc_consumble_p.issued_qty) AS csale_qty ## this part was added
FROM `jobc_parts_p`
LEFT JOIN `jobc_consumable_p` ON (jobc_consumable_p.part_no = jobc_parts_p.part_no) ## this part was added
WHERE DATE_FORMAT(jobc_parts_p.date_time,'%Y-%m-%d') BETWEEN '".$f."' AND '".$t."'
GROUP BY jobc_parts_p.part_no";

    and now your result should look something like this without the secondquery

    $finalData = array(
'10R46' => array(0 => array('part_no' => 10R46, 'sale_qty' => 2, 'csale_qty' => 2)),
'10R91' => array(0 => array('part_no' => 10R91, 'sale_qty' => 1, 'csale_qty' => 1)),
'10M95' => array(0 => array('part_no' => 10M95, 'sale_qty' => 2, 'csale_qty' => 3))
);

    also if you don’t expect there would be two rows with same part_no you can change

    $finalData[$row['part_no']][] = $row; -> $finalData[$row['part_no']] = $row;

    So you don’t need to double loop it.

    Login or Signup to reply.
  3. 0 
    $new_array = array_values(array_unique($rearrangedFinalData));

    Remove the duplicates and rearrange the keys (if needed) before you put your array in the foreach.

    Then you can use the $new_array to generate your html

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search