CODEWITHSUNDEEP
phpmysqlduration
JulianJ
JulianJ

Reputation: 1317

Only show data for a determined duration with php

I have a page that shows posts stored in a mysql database. When the post is created the user can select how long they want the post to be visible for and I'm trying to figure how to only show posts for a determined duration. Here is some of my code (that hopefully shows the logic I'm trying).

//Query database

$sql = <<<SQL
    SELECT *
    FROM `posts`
SQL;

if(!$result = $db_connection->query($sql)){
    die('There was an error running the query [' . $db_connection->error . ']');
}

while($row = $result->fetch_assoc()){

    //The date the post was made
    $date_of_post = $row['date_of_post'];
    //The duration of the post in days eg 7.
    $duration = $row['duration'];
   //Attempting to add duration to date
    $newdate = strtotime($duration, $date_of_post);
    //Only show posts that are still valid, eg date + duration is less than today's date
    if($newdate > now()){
        echo '<h2>Post Title</h2>';
        echo '<p>Date of Posted:'.$date.'</p>';
    }
}

Upvotes: 0

Views: 46

Answers (1)

RToyo
RToyo

Reputation: 2877

You can use a where clause and a date_add function to apply this filter directly in your SQL query. Simply add the duration days to the date_of_post value, and compare it against NOW().

Note that because you're storing your duration value as a varchar instead of an int, you'll need to convert the duration value to a signed int.

Here is an example, with the date_add expanded out to make it clearer to understand what is happening.

select
    *
from
    posts
where
    date_add
    (
        date_of_post,
        INTERVAL convert(duration, SIGNED INT) DAY
    ) > NOW()

As a side note, you should always try to filter your data in your query, and not in your PHP script. Don't just select your entire table into your script - let SQL do as much work as possible. The RDBMS is far more efficient than PHP, and you'll save a lot on overhead (eg amount of data sent over the network, and how much RAM has to be used to store the results for PHP to work with, etc).

Upvotes: 1

Related Questions