CODEWITHSUNDEEP
phpmysql
poulokopri
poulokopri

Reputation: 75

How to write this mySQL query to show many data per one column?

My mySQL table is as below

id - hash - page - timestamp
1 - ig35sdf89 - page1 - 2015-05-06 12:03:54
2 - ig35sdf89 - page2 - 2015-05-06 12:06:54
3 - ig35sdf89 - page1 - 2015-05-06 12:08:54
4 - xgd98rgf - page1 - 2015-05-06 12:10:54
5 - aaaat43gr - page3 - 2015-05-06 12:12:54

My question is what is a mySQL query to use so as to show something like where I will show the path of each hash order by his timestamp:

ig35sdf89  - page1 - 2015-05-06 12:08:54
           - page2 - 2015-05-06 12:03:54
           - page1 - 2015-05-06 12:06:54

xgd98rgf  - page1 - 2015-05-06 12:10:54

aaaat43gr  - page3 - 2015-05-06 12:12:54

I am using PHP.

Upvotes: 1

Views: 129

Answers (4)

Akhil
Akhil

Reputation: 2602

SELECT IF(@tempHash = `hash`, '', @tempHash := `hash`) `hash`, page, `timestamp` 
FROM myTable
JOIN (SELECT @tempHash := '')s
ORDER BY `hash`, `timestamp`

Is it satisfying your conditions?

EDIT : Changed order by fields Added hash also

Upvotes: 0

amdixon
amdixon

Reputation: 3833

mysql supports group_concat. Which supports exactly this use case

So if your table is defined as

table defn

create table pages
(
  id integer auto_increment primary key not null,
  hash varchar(10) not null,
  pagename varchar(128) not null,
  last_access_time timestamp not null
);

You can use this

query

select 
hash, group_concat(pagename, last_access_time SEPARATOR ';\n')
from
pages
group by hash
;

or alternatively can return json to use with php json_decode :

query output json

select 
concat('{', prty_hash, ',"pages":[', group_concat(json_page SEPARATOR ','), ']}') as json_prt
from
(
select 
concat('"hash":"', hash, '"') as prty_hash,
concat('"page":', pagename) as prty_pagename,
concat('{', '"page":"', pagename, '",', '"last_access_time":"', last_access_time, '"}') as json_page
from
pages
) prty
group by prty_hash
;

this gives

json output

{"hash":"123456789","pages":[{"page":"mypage3","last_access_time":"2015-05-06 09:34:02"},{"page":"mypage2","last_access_time":"2015-05-06 09:34:02"},{"page":"mypage1","last_access_time":"2015-05-06 09:34:02"}]}

{"hash":"999999","pages":[{"page":"mypage4","last_access_time":"2015-05-06 09:34:02"}]}

link to sqlfiddle

This can now be consumed in php something like :

php

<?php

$servername = "localhost";
$username = ...;
$password = ...;
$dbname = ...;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// GET
if ($_SERVER['REQUEST_METHOD'] == "GET")
{
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);

    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
     } 

    $sql = <<<heredoc
      select 
      concat('{', prty_hash, ',"pages":[', group_concat(json_page SEPARATOR ','), ']}') as json_prt
      from
      (
        select 
        concat('"hash":"', hash, '"') as prty_hash,
        concat('"page":', pagename) as prty_pagename,
        concat('{', '"page":"', pagename, '",',       '"last_access_time":"', last_access_time, '"}') as json_page
        from
        pages
      ) prty
      group by prty_hash
      ;
heredoc;

    $dbResult = $conn->query($sql);
    $appResult = array();

    while($row = $dbResult->fetch_assoc()) {
        $json_res_obj = json_decode($row['json_prt']);  
        // do something with the object, render/echo it on the page etc..
    }

    $conn->close();
}
else
{
    die("Invalid request");
}


?>

Note: untested php..

Upvotes: 2

anil
anil

Reputation: 61

Hi you can try this...

For table structure like ==> hash_page(id,hash,page,timestamp)

<?php    
//this sql query groups all rows with same 'hash' value
$sql = "SELECT hash_page.id,hash_page.hash,GROUP_CONCAT(hash_page.page) as hash_pages,GROUP_CONCAT(hash_page.timestamp) as timestamps FROM hash_page GROUP BY hash";

$result = mysql_query($sql);


while($row = mysql_fetch_assoc($result)){

//this will explode comma seperated values to array
$hash_pages_arr = explode(',',$row['hash_pages']);
$timestamps_arr = explode(',',$row['timestamps']);
$count_arr = count($hash_pages_arr);

$page_timestamp_arr = array();
for($i=0; $i<$count_arr; $i++){

    $page_timestamp_arr[] = array('page'=>$hash_pages_arr[$i],'timestamp'=>$timestamps_arr[$i]);
}

$new_result[$row['hash']] = $page_timestamp_arr;
}


echo '<pre>';print_r($new_result);
?>

Upvotes: 0

Antonisin Maxim
Antonisin Maxim

Reputation: 36

Mysql don`t suppor this thing, because have table structure. The most right way is to create PHP function to convert array. For example:

function convertArray($result) {
    $comparator = '';
    foreach($result as $row) {
        if ($comparator != $row['hash']) {
             $comparator = $row['hash'];
             $array[$comparator][] = $row;
        } else {
          $array[$comparator][] = $row;
        }
    } }

Upvotes: 0

Related Questions