CODEWITHSUNDEEP
phpmysqlhtml
Migue Chan
Migue Chan

Reputation: 3

How can I get a single value from a mysql result, if I have several rows with the same value

I have a database called 'employees' with this values:

value1     | value2 | value3
-----------------------------
employee1  |  key1  |  boss1
employee2  |  key2  |  boss1
employee3  |  key3  |  boss1
employee4  |  key4  |  boss2
employee5  |  key5  |  boss2

In php, I'm trying to loop through the result to get all the employees of a boss, but getting the boss name only once, I've tried with GROUP BY, but when looping this only shows the first entry:

$query = "SELECT * FROM employees WHERE value3 = 'boss1' GROUP BY value3";
$result = $db->query($query);
$foreach ($result as $row) {
    echo $row["value1"] . $row["value2"] . $row["value3"];
}

Results in:

employee1  |  key1  |  boss1

Whitout GROUP BY, when looping with foreach, it shows the boss name once for each entry

$query = "SELECT * FROM employees WHERE value3 = 'boss1'";
$result = $db->query($query);
foreach ($result as $row) {
    echo $row["value1"] . $row["value2"] . $row["value3"];
}

Results in:

employee1  |  key1  |  boss1
employee2  |  key2  |  boss1
employee3  |  key3  |  boss1

I'm trying to get a result like:

boss1
    employee1  |  key1
    employee2  |  key2
    employee3  |  key3

Upvotes: 0

Views: 53

Answers (2)

Yan
Yan

Reputation: 884

You can use if() in foreach like this:

foreach ($result as $row) {
    $i++;
    if ($i==1) {echo $row["value3"];}
    echo $row["value1"] . $row["value2"];
}

Than, you can get the result you wanted:

boss1
employee1  |  key1
employee2  |  key2
employee3  |  key3

Upvotes: 1

Abhishekh Gupta
Abhishekh Gupta

Reputation: 6236

You can use GROUP_CONCAT() to get a comma(default) delimited list of value1 and values2 for each boss like this:

SELECT 
  GROUP_CONCAT(e.value1) AS value1,
  GROUP_CONCAT(e.value2) AS value2,
  e.value3 AS boss
FROM employees e
WHERE value3 = 'boss1' 
GROUP BY value3;

This will get you something like for boss1:

employee1,employee2,employee3 | key1,key2,key3 | boss1

Edit 1: As a html list, something like: <ul>boss1 <li>employee1</li> <li>employee2</li> <li>employee3</li> </ul> I don't need the html code, but I need to get the data in a format which I can use as a list in html

SELECT 
  CONCAT('<ul>',GROUP_CONCAT(CONCAT('<li>',e.value1,'<\/li>')),'<\/ul>') AS value1,
  GROUP_CONCAT(e.value2) AS value2,
  e.value3 AS boss
FROM employees e
WHERE value3 = 'boss1' 
GROUP BY value3;

Note:- You should use your server side language to achieve this. And use first query.

Upvotes: 0

Related Questions