How to create and array from two column that referent the first column


#1

here i have this columns

SO WO
0096522 0165946
0096522 0165947
0096522 0165975
0096522 0165984
0096703 0167312
0096703 0167316

i want to create this array from those columns with php

0096522{ 0165946, 0165947, 0165975, 0165984}

0096703{ 0167312, 0167316}

can someone can help me to achieve this please


#2

What have you tried, and with what results? Show us the code you’ve had a go with.


#3

this is my table on mysql

|SalesOrderNo | WorkOrderNo

| 0096522 | 0165946 |
| 0096522 | 0165947 |
| 0096522 | 0165948 |
| 0096522 | 0165949 |
| 0096703 | 0167312 |
| 0096703 | 0167313 |

i run the following php query to try to get my desired array

<?php

try
{
$pdo = new PDO('mysql:host=; dbname=sage; charset=utf8', '', '');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$output = 'Database connection established.';
}
catch (PDOException $e)
{
$output = 'Unable to connect to the database server.' .
$e->getMessage() . ' in ' .
$e->getFile() . ': ' . $e->getLine();
}
$data = array();
$i = 0;
$query = $pdo->prepare("SELECT sage.so404_wolink.SalesOrderNo, sage.so404_wolink.WorkOrderNo FROM sage.so404_wolink WHERE sage.workordermaster.Status IN ('F', 'R') AND sage.workordermaster.PlannerCode > 199 ; ");
$query->execute();
foreach($query as $row){
  $SalesOrder = $row['SalesOrderNo'];
  $WorkOrder = $row['WorkOrderNo'];

  $SO[] = array(
    "SalesOrder" => $SalesOrder );

 $WO[] = array(
    "WorkOrder" => $WorkOrder

  array_push($SO, "WorkOrder => $WorkOrder");

}
echo '{'.'"data":'. json_encode($SO).'}';

the Result of that query i this

{
data[
[{“SalesOrder”:“0096522”}, [{“WorkOrder”:“0165946”},{“WorkOrder”:“0165947”},{“WorkOrder”:“0165948”}{“WorkOrder”:“0165949”}]],
[{“SalesOrder”:“0096703”}, [{WorkOrder":“0167312”},{WorkOrder":“0167316”}]]
]

to summarize all what i want is to get and array like this

{
data[

{ “SalesOrder”:“0096522”
“WorkOrder”:“0165946”
“WorkOrder”:“0165947”
“WorkOrder”:“0165948”
},
{ “SalesOrder”:“0096703”
“WorkOrder”:“0167312”
“WorkOrder”:“0167313”
}]}

so i can dump the data into a table
please can someone have any idea how to achieve this


#4

If you just want to output to a table why are you outputting JSON?


#5

i am using one java table that need json array to populated the table, however if i use an html table show me this

see how populates the same sales order 4 times i just want to have sales orders once and then work order related to that sales order like the next image so i can spam and collapse the row

so i can collapse the rows


#6

If you’re retrieving all the data row by row, why not something like this pseudo-code:

run query
for each row in the result table:
  if the SO has changed create a new group for that value and make it current
  add the WO to the current group
  remember the current SO
  go to start of loop with next row

I’m reasonably sure you could get MySQL to group them for you, but that’s something I’m not familiar with.


#7

Simple data grouping by key would work if don’t need all the extra names

foreach($query as $row){
	$data[$row['SalesOrderNo']][] = $row['WorkOrderNo'];
}

This would results in an array like this

Array
(
    [0096522] => Array
        (
            [0] => 0165946
            [1] => 0165947
            [2] => 0165975
            [3] => 0165984
        )

    [0096703] => Array
        (
            [0] => 0167312
            [1] => 0167316
        )

)

I suppose if you really need text in your values you could so something like this.


foreach($query as $row){ 
	$data['"SalesOrder":"'.$row['SalesOrderNo'].'"'][] = '"WorkOrder":"'.$row['WorkOrderNo'].'"'; 		
}

Result

Array
(
    ["SalesOrder":"0096522"] => Array
        (
            [0] => "WorkOrder":"0165946"
            [1] => "WorkOrder":"0165947"
            [2] => "WorkOrder":"0165975"
            [3] => "WorkOrder":"0165984"
        )

    ["SalesOrder":"0096703"] => Array
        (
            [0] => "WorkOrder":"0167312"
            [1] => "WorkOrder":"0167316"
        )

)