PHP MySQL merge rows with same column value

Question

I have a table looks like this:

year | url                | view
-------------------------------
2016 | /document/item.pdf | 21
2015 | /document/item.pdf | 35
2014 | /document/item.pdf | 41

and I want build another table looks like this :

 url                | 2014 | 2015 | 2016
---------------------------------------
 /document/item.pdf | 41   | 35   | 21

I don't really now how I have to write my mysql query to do that. Thanks for your help :)

Please don't make us guess your requirements. And please provide some insights to what you have tried so far. As it stands, your question looks suspiciously like a "do my homework" question (which typically get closed rather quickly) — Burki
– Burki, Commented Feb 28, 2017 at 10:28

Vikas Umrao · Accepted Answer · 2017-02-28 10:13:10Z

1

You can try this:

 SELECT  url,
            MAX(IF(`year` = 2014 , view, NULL)) '2014',
            MAX(IF(`year` = 2015, view, NULL)) '2015',
            MAX(IF(`year` = 2016, view, NULL)) '2016'
    FROM    pub
    GROUP   BY url

answered Feb 28, 2017 at 10:13

Vikas Umrao

2,5801 gold badge17 silver badges23 bronze badges

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

Comments

zhm · Accepted Answer · 2017-02-28 09:57:53Z

1

Query all data from your first table to PHP array, the array would be like:

$data = [[2016, "/document/item.pdf", 21],
    [2015, "/document/item.pdf", 35],
    [2014, "/document/item.pdf", 41]];

Traverse $data, make another associated array $result, whose key is url, value is an array of data with year and view:
```
$result = array();
foreach ($data as $value) {
    $result[$value['url']][$value['year']] = $value['view'];
}
```
Traverse the new array, and write data back to MySQL

edited Feb 28, 2017 at 9:57

answered Feb 28, 2017 at 9:49

zhm

3,6814 gold badges39 silver badges57 bronze badges

1 Comment

Falco Over a year ago

I was focused on a mysql query but it works fine with an array. Thanks Martin.

Mahmoud Saad · Accepted Answer · 2017-02-28 09:50:42Z

0

you must make two table -> first to save files , second to save visits like this

Files Table

url_id | url
------------------------
1      |/document/item.pdf 
2      |/document/item.pdf

Visits Table (url_id) is forign key from files table

url_id | time
---------------
1       | 987907872     
2       | 987907872
2       | 987978272
2       | 987907872
2       | 987907872
1       | 987907872
1       | 987907872
1       | 987907872

========================

then you can check time by year in visits table to view number of visits of each URL

answered Feb 28, 2017 at 9:50

Mahmoud Saad

345 bronze badges

Comments

Mithu CN · Accepted Answer · 2017-02-28 10:18:43Z

0

May be it will help you

SELECT 
    url, 
    sum( if( year= '2014', view, 0 ) ) AS 2014,  
    sum( if( year= '2015', view, 0 ) ) AS 2015, 
    sum( if( year= '2016', view, 0 ) ) AS 2016 
FROM 
    pub
GROUP BY 
    url;

answered Feb 28, 2017 at 10:18

Mithu CN

5955 silver badges11 bronze badges

Collectives™ on Stack Overflow

PHP MySQL merge rows with same column value

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related