Merge arrays based on duplicate values php

Question

I'm just printing values from the data obtained from mysql

$sql = "SELECT * FROM tablename";
$res = mysql_query($sql);   
$rs = getAll($res);

for($k = 0; $k < count($rs); $k++)
{
    $email = $rs[$k]['emailid'];
    $name = $rs[$k]['name'];
    $content = "<table><tr><td>Name</td><td>Email</td></tr><tr>      <td>'.$name.'</td><td>'.$email.'</td></tr></table>";
}

Now the The $content looks like this.

--------------
Name    Email
--------------
n1      email1


--------------
Name    Email
--------------
n2      email2


--------------
Name    Email
--------------
n3      email1

Now I want to merge the html table row based on common email id. So that it should look like

--------------
Name    Email
--------------
n1      email1
n3      email1


--------------
Name    Email
--------------
n2      email2

name n1 and n3 are merged based on 'email1'. How do I achieve this?

score 2 · Accepted Answer · answered Feb 10 '16 at 07:01

option 1) You can order by email and then loop results with php filling some array, verify if email change from prev, and so on like Rajdeb explains in other answer:

SELECT * FROM tablename ORDER BY EMAIL;

you get:

n1        email1
n2        email2
n3        email1

option 2) You can use GROUP_CONCAT to retrieve names glued by email. Then in a php loop, for each distinct email, you get names separated by commas. The only task to perform here is an array explode to get the names in an array and put in table cell, loop only one time for each email, with less php work. Order by email is optional.

SELECT GROUP_CONCAT(NAME) AS NAME, EMAIL 
FROM tablename GROUP BY EMAIL ORDER BY EMAIL;

you get:

n1,n3     email1
n2        email2

If you want to change comma symbol to separate names, with "-" for example, you need to add SEPARATOR to previous query:

SELECT GROUP_CONCAT(NAME SEPARATOR '-') AS NAME, EMAIL 
FROM tablename GROUP BY EMAIL ORDER BY EMAIL;

you get:

n1-n3     email1
n2        email2

Hope this help you! :)

score 1 · Answer 2 · edited May 23 '17 at 11:59

First of all, add an ORDER BY clause to your SELECT query like this:

$sql = "SELECT * FROM tablename ORDER BY emailid";

And after getting the result set using $rs = getAll($res); process it like this:

// your code

$rs = getAll($res);

$prevEmail = '';
$content = '<table>';
for($k=0; $k < count($rs); $k++){
    $email = $rs[$k]['emailid'];
    $name = $rs[$k]['name'];
    if(empty($prevEmail)){
        $content .= '<tr><td>Name</td><td>Email</td></tr><tr><td>'.$name.'</td><td>'.$email.'</td></tr>';
        $prevEmail = $email;
    }else{
        if($prevEmail == $email){
            $content .= '<tr><td>'.$name.'</td><td>'.$email.'</td></tr>'; 
        }else{
            $content .= '</table><table><tr><td>Name</td><td>Email</td></tr><tr><td>'.$name.'</td><td>'.$email.'</td></tr>';
            $prevEmail = $email;
        }
    }
}
$content .= '</table>';
echo $content;

Sidenote: Don't use mysql_* functions, they are deprecated as of PHP 5.5 and are removed altogether in PHP 7.0. Use mysqli or pdo instead. And this is why you shouldn't use mysql_* functions.

Merge arrays based on duplicate values php

2 Answers2