Get values to table according to 2 row values in multiple columns

Question

i have table in database like this:

 name    date    year
 John    1/5/15  2015
 Maria   3/3/15  2015
 John    7/3/16  2016
 Steve   5/2/16  2016
 Steve   9/7/17  2017
 John    9/1/17  2017

And want to get table like this:

Name    2015    2016    2017
John    1/5/15  7/3/16  9/1/17
Maria   3/3/15
Steve           5/2/16  9/7/17

I tried with this query:

$sql = "SELECT DISTINCT(name),date,year FROM table ORDER BY name ASC";
$query = $conn->query($sql);

Now I don't know what loop to use to get data to look like in second table.

I tried with this code

while($row = $query->fetch_assoc()) {
<td>" . $row["prezime_ime"] . "</td>
<td>" . ($row["date"]) . "</td>

Now I'm stuck here. I'm new to PHP so please can anyone help me. Thanks a lot

I guess you have only one entry for name each year? do you know the years? — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Feb 4, 2019 at 18:14

Juan Carlos Oropeza · Accepted Answer · 2019-02-04 18:17:12Z

1

You can do a group by

SELECT name,
       MAX( CASE WHEN YEAR(date) = 2015
                 THEN date
            END) as 2015,
       MAX( CASE WHEN YEAR(date) = 2016
                 THEN date
            END) as 2016,
       MAX( CASE WHEN YEAR(date) = 2017
                 THEN date
            END) as 2017
FROM yourTable
GROUP BY name

answered Feb 4, 2019 at 18:17

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

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

2 Comments

mEDO Over a year ago

ok, I put that code to my query, but how to get that data to table cell

Juan Carlos Oropeza Over a year ago

run the query on the db, you will get the exact result you request on your question.

Collectives™ on Stack Overflow

Get values to table according to 2 row values in multiple columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related