How to convert sql result to multidimensional array dynamically?

Question

Here is the query string.

$query = "SELECT t.id, t.assignee, t.owner, d.code, d.status, d.target_completion_date, d.target_extension_date, d.submission_date, d.approval_date, d.revision_start_date, d.revision_completion_date, d.message, ty.name, f.orig_name, f.new_name, b.payment_date, b.discount, b.total_cost, b.amount_payed, b.edit_level, b.billing_type, b.pages, b.words FROM tasks t INNER JOIN details d ON t.detail_id = d.id INNER JOIN billing b ON t.billing_id = b.id INNER JOIN TYPE ty ON d.document_type_id = ty.id INNER JOIN files f ON t.file_id = f.id WHERE t.assignee = 'argie1234'"; 

And this is an array to which I would like to receive the result of the request.

 $user = array('allTask'=>array(array('taskid' => 1, 'assignee'=>'argie1234', 'owner'=>'austral1000', 'details' => array( 'code' => 'E', 'status'=>'TC', 'targetCompletionDateUTC'=>'1379401200', 'targetExtentionDateUTC'=>'1379401200', 'submissionDateUTC'=>'1379401200', 'approvalDateUTC'=>'1379401200', 'revisionStartDateUTC'=>'1379401200', 'revisionCompletionDateUTC'=>'1379401200', 'messageToEditor'=>'Please work on it asap.', 'documentType' => 'Thesis'), 'file' => array('orig_name' =>'originalname.docx', 'new_name' => 'newname.docx'), 'billing'=>array('paymentDate'=>'July 26,2013 12:40', 'discount' => '0', 'totalRevisionCharge' => '$20.00', 'totalAmountPayed' => '$20.00', 'revisionLevel' => '1', 'chargeType'=> '1', 'numPages' => '60', 'numWords' => '120,000' ) ), array('taskid' => 12, 'assignee'=>'argie1234', 'owner'=>'usaroberto', 'details' => array( 'code' => 'E', 'status'=>'TC', 'targetCompletionDateUTC'=>'1379401200', 'targetExtentionDateUTC'=>'1379401200', 'submissionDateUTC'=>'1379401200', 'approvalDateUTC'=>'1379401200', 'revisionStartDateUTC'=>'1379401200', 'revisionCompletionDateUTC'=>'1379401200', 'messageToEditor'=>'Please work on it asap.', 'documentType' => 'Thesis'), 'file' => array('orig_name' => 'originalname.docx', 'new_name' => 'newname.docx'), 'billing'=>array('paymentDate'=>'July 26,2013 12:40', 'discount' => '0', 'totalRevisionCharge' => '$20.00', 'totalAmountPayed' => '$20.00', 'revisionLevel' => '1', 'chargeType'=> '1', 'numPages' => '60', 'numWords' => '120,000' ) ), 'account' => array( 'username' => 'marooon55', 'emailadd' => ' [e-mail] ', 'firstname' => 'Maroon', 'initial' => 'E', 'lastname' => 'Young', 'country' => 'Australia', 'gender' => 'M', 'password' =>'360e2801190744a2af74ef6cbfdb963078b59709', 'activationDate' => '2013-09-13 14:30:34') ); 

How can I create the above array? I know exactly how to define a multidimensional array, unfortunately, although it is difficult for me to create this complex array dynamically. As a beginner, I don’t even know where to start.

Answer 1

Here is an example that might help you. Try to start with simple multidimensional arrays, as soon as you master it, you can move on to complex construction projects. Then you will find that the array you want to build is not very difficult than you originally thought.

$ mycomplexarray = array ('key1' => array ('val1', 'val2'), 'key2' => array ('val3', 'val4' => array ('val5', 'val6')));

Answer 2

You can create an array in the same way as here. I'm not going to write all this, but something like this ...

  $result = $mysqli->query($query); // however you query the db is up to you. $row = $result->fetch_assoc(); //same as query use your prefered method to fetch $user = array('allTask'=>array(array('taskid' => $row['id'], 'assignee'=>$row['assignee'], 'owner'=>$row['owner'], 'details' => array( 'code' => $row['code'], 'status'=>$row['status'], 

... etc. Hope this makes sense to you.

Answer 3

First set up an array of structure that determines which columns will be stored in the additional array, e.g.

 $struc=array('Id'->0, 'assignee'->0, 'owner'->0, 'code'->'detail', 'status'->'detail', 'target_completion_date'->'detail', 'target_extension_date'->'detail', 'submission_date'->'detail', 'approval_date'->'detail', 'revision_start_date'->'detail', 'revision_completion_date'->'detail', 'message'->'detail', 'name'->'file', 'orig_name'->'file', 'new_name'->'file', 'payment_date'->'billing', 'discount'->'billing', 'total_cost'->'billing', 'amount_payed'->'billing', 'edit_level'->'billing', 'billing_type'->'billing', 'words'); 

In your while ($a=mysqli_fetch_assoc($res)) you can now use this structure to decide whether you want to store the element directly in the target array or whether you want to put it in a subarray named in this structure array. how

 $res=mysqli_query($con,$sql); $arr=array(); while($a=mysqli_fetch_assoc($res)) { // within result loop: $a is result from mysqli_fetch_assoc() $ta=array(); // temp array ... foreach ($a as $k => $v){ if ($struc[$k]) $ta[struc[$k]][$k]=$v; else $ta[$k]=$v; } $arr[]=$ta; // add to target array } 

This is complete code, no longer required. It was printed on my iPod, so it has not been tested yet.

The generated array should be equivalent to your $user['allTask'] array.