How to fix this mySQL code so as to show the correct output?

Question

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

here is the until now code

$sql= mysql_query("SELECT IF(@tempHash = `hash`, '', @tempHash := `hash`) `hash`, page, `timestamp` 
FROM behaviour
JOIN (SELECT @tempHash := '')s
ORDER BY `timestamp`
");

 while($row = mysql_fetch_array($sql)) {
     echo '<tr><td>'.$row[hash].'</td><td>'.$row[page].'</td><td>'.$row[timestamp].'</td></tr>';
 }

The problem is that the output even though it runs, it has a bug. The path (pages) of hash abc are splitted. I mean that it displays a number of pages from abc hash, then a user xxx123 is shown with xxx123 pages and then it continues with user abc. What I want is full list of abc pages, timestamps and then proceed to xxx123 and so on.

I noticed in thatthat the splitting is made because of timestamps.

if user1 has a page timestamp 14:10:12 and the next 14:10:30 and user2 has 14:10:20 it will break the path of user1, show user2 and then continue with user1.

Take a look at that question. This should solve it. https://stackoverflow.com/questions/514943/php-mysql-order-by-two-columns — Alexander Fuchs, May 06 '15 at 15:15

sanir ranta · Accepted Answer · 2015-05-06T16:38:33.917

0

    <?php

$sql= mysql_query("SELECT IF(@tempHash = `hash`, '', @tempHash := `hash`) `hash`, page, `timestamp` 
FROM behaviour
JOIN (SELECT @tempHash := '')s
ORDER BY `hash`
");

    $xhash = "";
     while($row = mysql_fetch_array($sql)) {
        if($xhash != $row['hash']){
           echo '<tr><td>'.$row[hash].'</td><td>'.$row[page].'</td><td>'.$row[timestamp].'</td></tr>';
        }else{
            echo '<tr><td></td><td>'.$row[page].'</td><td>'.$row[timestamp].'</td></tr>';
        }
        $xhash = $row['hash'];
     }

    ?>

edited May 06 '15 at 16:38

answered May 06 '15 at 15:29

sanir ranta

291
2
7

What is the mysql query for this? – poulokopri May 06 '15 at 16:21
you have use same query which in question – sanir ranta May 06 '15 at 16:23
Thank you but isnt the bug found in the sql query? I thought that because it outputs (even kind of wrong) it should be something in the query, not in the php – poulokopri May 06 '15 at 16:29
give me your sql table data – sanir ranta May 06 '15 at 16:32
I have tested your code, unfortunately I do not get any better than the previous. A sample of the sql data is in the question, first table – poulokopri May 06 '15 at 16:35
Now a lot of pages have no hash – poulokopri May 06 '15 at 16:45

How to fix this mySQL code so as to show the correct output?

1 Answers1