How to convert mysql_fetch_assoc array to [column,column] Format in PHP

Question

This is my function

$dataArray = array();
$results = mysqli_query($mysqli, ("SELECT CONCAT ('US-',  medicare_provider_charge_inpatient_drg100_fy2011.`Provider State`) AS `Provider State`,
                                        sum(ROUND(medicare_provider_charge_inpatient_drg100_fy2011.`Total Discharges`, 2)) AS `Total Discharges`
                                        FROM medicare_provider_charge_inpatient_drg100_fy2011
                                        WHERE medicare_provider_charge_inpatient_drg100_fy2011.`Provider Name` LIKE '%" . $hospital_name . "'
                                        GROUP BY CONCAT ('US-',  medicare_provider_charge_inpatient_drg100_fy2011.`Provider State`)"));
while ($row = mysqli_fetch_assoc($results)) {
    $dataArray[] = $row;
}

I want to display data in following format

[Provider State, Total Discharges],
[US-AL,2051],
[US-TN,6982]

the dump of $dataArray gives me

   Array
(
[0] => Array
    (
        [Provider State] => US-AL
        [Total Discharges] => 2051.00
    )

[1] => Array
    (
        [Provider State] => US-TN
        [Total Discharges] => 6982.00
    )

)

iamien · Accepted Answer · 2013-12-31 15:05:54Z

1

while ($row = mysqli_fetch_assoc($results)) {
    $dataArray[] = $row["Provider State"]. "," .$row["Total Discharges"];
}

That should make your data into a single-dimensional-array.

answered Dec 31, 2013 at 15:05

iamien

614 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Barroso · Accepted Answer · 2013-12-31 15:02:13Z

0

You just need to work with the data to display the way you want. Print first the column names and then the data. I made this example to work for any number of fields:

// Print the first row with column names
$firstRow = array_keys(reset($dataArray));
$output = array();
foreach($firstRow as $val) {
    $output[] = $val;
}
echo '['.implode(',',$output).']'."\n";

// Print all the data
foreach($dataArray as $row) {
   $output = array();
   foreach($row as $col) {
       $output[] = $col;
   }
   echo '['.implode(',',$output).']'."\n";
}

answered Dec 31, 2013 at 15:02

Alex Barroso

8596 silver badges15 bronze badges

2 Comments

noobie-php Over a year ago

array_keys() expects parameter 1 to be array, boolean given

Alex Barroso Over a year ago

you should put my code after your while and it should work if $dataArray is not empty

Fanda · Accepted Answer · 2013-12-31 15:06:29Z

0

If you need such structure of array, use this (but it is strange):

$newStructure = array();
$newStructure[] = "Provider State, Total Discharges";

while ($row = mysqli_fetch_assoc($results)) {
    $newStructure[] = $row['Provider State'] . ',' . $row['Total Discharges'];
}

If you want to just display data, use this:

echo "Provider State, Total Discharges";

while ($row = mysqli_fetch_assoc($results)) {
    echo = $row['Provider State'] . ',' . $row['Total Discharges'];
}

answered Dec 31, 2013 at 15:06

Fanda

3,8166 gold badges42 silver badges57 bronze badges

3 Comments

noobie-php Over a year ago

it may be strange for few but this actually is ajax response that will be parsed by google visualization library for displaying data.

noobie-php Over a year ago

unable to get column names as top level/1st array

Fanda Over a year ago

Try to remove spaces from result column names.

Collectives™ on Stack Overflow

How to convert mysql_fetch_assoc array to [column,column] Format in PHP

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related