php + mysql = selecting and displaying rows with different date

Question

guys! I've never met with the selecting definite rows between dates and sorting them properly. Any help would be very useful to understand how should I organize the php + mysql script.

I have a table that contains rows with date column in mktime() format. This table stores information of clicks to advertisement on my website.

This is the structure of my clicks_log table: 1) ID - unique id of the row with auto increment. 2) DATE - date in mktime format when somebody clicked on the link. 3) IP - ip address of the visitor who clicked on the link.

I want to select all information from the clicks_log table and display on the page like this:

DATE: 15.04.2011

IP ADDRESS: 192.168.0.1. DATE: 08:45, 15.04.2011
IP ADDRESS: 192.168.0.2. DATE: 13:22, 15.04.2011
IP ADDRESS: 192.168.0.3. DATE: 18:58, 15.04.2011

DATE: 16.04.2011

IP ADDRESS: 192.168.0.1. DATE: 04:14, 16.04.2011
IP ADDRESS: 192.168.0.2. DATE: 09:35, 16.04.2011
IP ADDRESS: 192.168.0.3. DATE: 14:11, 16.04.2011

DATE: 17.04.2011

IP ADDRESS: 192.168.0.1. DATE: 21:56, 17.04.2011
IP ADDRESS: 192.168.0.2. DATE: 23:04, 17.04.2011
IP ADDRESS: 192.168.0.3. DATE: 23:13, 17.04.2011

elitalon · Accepted Answer · 2011-04-17 11:55:38Z

2

You should use a ORDER BY clause to order your results by a date:

SELECT *
FROM `clicks_log`
ORDER BY `date` ASC;

And then you can use the PHP date function passing the value of the date column as the second parameter:

// Get the first date
$dateSection = ...;
while ($row = mysql_fetch_assoc($result)) {
    ...

    // Check here if the date header has changed to begin a new section
    $dateHeader = date("d.m.Y", $row['date']);
    if ( $dateHeader !== $dateSection ) {
        // Build new section header here
        ...
        $dateSection = $dateHeader;
    }

    ...
    echo $row['ip'];
    ...
    echo date("H:i, d.m.Y", $row['date']);
    ...
}

I have omitted the code to get the $dateSection and $result variables, but there are a lot of examples in PHP documentation.

edited Apr 17, 2011 at 11:55

answered Apr 17, 2011 at 11:39

elitalon

9,46710 gold badges53 silver badges86 bronze badges

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

Comments

Jakob Alexander Eichler · Accepted Answer · 2011-04-17 11:40:03Z

You have two possibilities, either you fetch your data in one query (what I'd suggest) or you use 3 queries.

Following the one query strategy you just fetch your data and order it by the time. in an additional row you fetch the timestamp representing the date using the sql function UNIX_TIMESTAMP()

having this timestamp, than you can devide your result set into 3 result sets using php.

The other way would be generating a query for each date. to do so you can use the php funktion mktime to get the time for $a = (0:00 15.04). and $b=(23:59 15.04). in the where condition of your mysql query you just add a between statement. the selected date must be $a and $b

where tbl.`date` between FROM_UNIXTIME( ' . $a . ') AND FROM_UNIXTIME( ' . $b . ');';

Philip · Accepted Answer · 2011-04-17 11:41:24Z

0

for ($i = 15; $i <= $current_day_of_month; $i++) {
        // SELECT * FROM clicks_log WHERE DATE LIKE '% $i.04.2011' ORDER BY DATE ASC;
        // print the result
}

It's usually better to store the date as a Unix timestamp. This allows easier fine-grained control using SELECT.

answered Apr 17, 2011 at 11:41

Philip

5,9753 gold badges40 silver badges69 bronze badges

Comments

satnhak · Accepted Answer · 2011-04-17 11:43:15Z

0

You are going to need to select the data ordered in ascending order by date:

 $sql = "SELECT ip, date FROM clicks_log table ORDER BY date ASC";

And then process the data in PHP to split it into days for the output:

 $date = date_parse($row['date']);
 $year = $row['year'];
 $month = $row['month'];
 $day = $row['day'];

answered Apr 17, 2011 at 11:43

satnhak

9,9707 gold badges68 silver badges87 bronze badges

Collectives™ on Stack Overflow

php + mysql = selecting and displaying rows with different date

DATE: 15.04.2011

DATE: 16.04.2011

DATE: 17.04.2011

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

DATE: 15.04.2011

DATE: 16.04.2011

DATE: 17.04.2011

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related