CODEWITHSUNDEEP
phpmysqlmktime
ilnur777
ilnur777

Reputation: 1145

php + mysql = selecting and displaying rows with different date

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

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

DATE: 16.04.2011

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

DATE: 17.04.2011

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

Upvotes: 1

Views: 1477

Answers (4)

elitalon
elitalon

Reputation: 9447

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.

Upvotes: 2

satnhak
satnhak

Reputation: 9871

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'];

Upvotes: 0

Philip
Philip

Reputation: 5917

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.

Upvotes: 0

Jakob Alexander Eichler
Jakob Alexander Eichler

Reputation: 3056

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 . ');';

Upvotes: 0

Related Questions