PHP MySQL select issue

Question

i have a database called emplyees structured like this:

id name   entrydate    totalhours
1  test   11/11/2015      8
2  test2  11/11/2015      7
3  test   11/12/2015      8
4  test2  11/12/2015      9

And i want to turn it in something like this

id name 11/11/2015  11/12/2015 
1  test      8          8
2  test2     7          9

I tried several methods but no result how can i do this? Thanks in advance!

You might want to read [How do I ask a good question](http://stackoverflow.com/help/how-to-ask), which enhances the probability for getting a useful answer _drastically_. You might find [ESR](https://en.m.wikipedia.org/wiki/Eric_S._Raymond)'s excellent essay [How To Ask Questions The Smart Way](http://catb.org/~esr/faqs/smart-questions.html) helpful, too. As of now, I am voting to close this question as it lacks to show any effort to solve this yourself. SO is not a free coding service. — Markus W Mahlberg, Nov 17 '15 at 11:45
@Strawberry I'm devastated to hear that. I belive that the OP wants a monthly report with dates being shown in the columns, not in the rows. That's called pivoting or cross tabulated query. The topic I linked provides a solution to this problem. Obviously, that solution has to be tailored to this specific issue. — Shadow, Nov 17 '15 at 11:55
It has to convert the entrydate into colums and the data from totalhours to be put into the entry date rows based on data . — Radu Campian, Nov 17 '15 at 11:58
@Shadow While it was never my intention to wreak devastation upon your disposition, I think that where a presentation layer is provided (PHP in the OP's case) it might as well be utilised. — Strawberry, Nov 17 '15 at 12:03
may be this one helps http://stackoverflow.com/questions/10925445/mysql-select-dynamic-row-values-as-column-names-another-column-as-value — Ajay Makwana, Nov 17 '15 at 12:10
@Strawberry I agree that the issue can be solved both on db level and application level. However, this question has been asked and answered so many times in SO, that I wanted to point it out, that it is a duplicate question in this format. — Shadow, Nov 17 '15 at 12:15
Read about pivot table like: http://stackoverflow.com/questions/7674786/mysql-pivot-table — Grzegorz Brzęczyszczykiewicz, Nov 17 '15 at 12:56

score 1 · Accepted Answer · answered Nov 17 '15 at 12:46

Once you fix your dates, something like this should work in PHP...

<?php

require('path/to/connection/stateme.nts');

$query = "
SELECT name
     , entrydate
     , totalhours
  FROM my_table
 ORDER
    BY name;
";

$result = mysqli_query($db,$query);

$array = array();

while($row = mysqli_fetch_assoc($result)){
$array[] = $row;
}


foreach ($array as $element)
    $newArray[$element["name"]][] = array($element["entrydate"]=>$element["totalhours"]);

print_r($newArray);

?>

outputs:

Array
(
    [test] => Array
        (
            [0] => Array
                (
                    [2015-11-11] => 8
                )

            [1] => Array
                (
                    [2015-11-12] => 8
                )

        )

    [test2] => Array
        (
            [0] => Array
                (
                    [2015-11-11] => 7
                )

            [1] => Array
                (
                    [2015-11-12] => 9
                )

        )

)

...which can easily be spat out to a table or json

i tried and didn't worked :| – Radu Campian Nov 17 '15 at 13:09 — Radu Campian, Nov 17 '15 at 13:09

PHP MySQL select issue

1 Answers1