PHP creating array of values with keys

Question

Im wanting to create a table to display a list of data from the database. But the values aren't stored in rows in a table but instead are separated by each value having its own column.

So my sample Data:

ID  Email           Value    
1   [email protected]     ABC    
1   [email protected]     DEF    
1   [email protected]     GHI    
2   [email protected]  ABC    
2   [email protected]  DEF    
2   [email protected]  GHI

Im wanting to display it as:

ID  Email           Value1  Value2  Value3    
1   [email protected]     ABC     DEF     GHI    
2   [email protected]  ABC     DEF     GHI

My Code:

$query1 = "SELECT wp_users.ID, user_email, VALUE FROM wp_users LEFT JOIN wp_cimy_uef_data on wp_users.ID = wp_cimy_uef_data.USER_ID";
$listEmail = array();
$listValues = array();

// Start the Load
$statement = $db->query($query1);
if ( $statement->rowCount() == 0 )
{
    echo '<strong>List is Empty</strong>';
} else foreach($statement as $row):

    $ID = htmlspecialchars($row['ID']);
    $email = htmlspecialchars($row['user_email']);
    $value = htmlspecialchars($row['VALUE']);

    $listEmail = array_fill_keys($ID, $email);
    $listValues = array_fill_keys($ID, $value);

endforeach; 
mysqli_close($db);

print_r($listEmail);

Yes, you could do it in PHP but the best way would be through changing the query. I had to do something similar in Oracle, and ended up using their PIVOT function. I'm assuming there's a similar feature in MySQL... — user1578653
– user1578653, Commented Jun 7, 2013 at 14:39
Sadly, MySQL doesn't have any functionality like Oracle's PIVOT and UNPIVOT.... perhaps next release — Mark Baker
– Mark Baker, Commented Jun 7, 2013 at 15:21

edwardmp · Accepted Answer · 2013-06-07 14:38:21Z

3

SELECT wp_users.ID, wp_users.user_email, 
GROUP_CONCAT(wp_users.value) 
FROM wp_users LEFT JOIN wp_cimy_uef_data on wp_users.ID = wp_cimy_uef_data.USER_ID 
GROUP BY wp_users.user_email

Indeed use GROUP_CONCAT to concat the value column's and GROUP BY to group on user_email.

answered Jun 7, 2013 at 14:38

edwardmp

6,6316 gold badges53 silver badges80 bronze badges

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

3 Comments

John Ayers Over a year ago

this does work and i thank you, however i was hoping to do it in php because i want to be able to remove values from this down the road. Say if one of the values was a the 3rd string in the values list id like to remove it. ALSO so i can display each row with a header to make it easier to read and sort.

edwardmp Over a year ago

Can't you just delete that value for that user id? E.g. DELETE 'ABC' FROM table WHERE ID = '1'

John Ayers Over a year ago

No unfortunately not this is for a report so the data must remain.

Chris Hanson · Accepted Answer · 2013-06-07 14:39:28Z

2

You could structure your loop like this:

$table_rows = array();

foreach($statement as $row) {
    $ID = htmlspecialchars($row['ID']);
    $email = htmlspecialchars($row['user_email']);
    $value = htmlspecialchars($row['VALUE']);

    if (empty($table_rows[$ID])) {
        $table_rows[$ID] = array(
            'email' => $email,
            'values' => array(),
        );
    }

    $table_rows[$ID]['values'][] = $value;

}

Or you could use a group by in your query.

answered Jun 7, 2013 at 14:39

Chris Hanson

2,0833 gold badges16 silver badges27 bronze badges

1 Comment

John Ayers Over a year ago

Using this method how could i print out the contents in a formated matter? foreach ($table_rows as $value) { echo $value . "<br />"; }

Collectives™ on Stack Overflow

PHP creating array of values with keys

2 Answers 2

3 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Related