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);

Answer 1

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.

Answer 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.