I am trying to query mysql by date, I have a variable of $today = date("Y-m-d");
I want to use this to get 'post_content' out of my 'posts' table for each previous year. I will then be echoing out this data using php.
How in the world would I do this? Maybe a while loop changing the date and querying that? I found "select fields from tables order by dateField" and would imagine a statement like that would exist with a parameter letting we query my posts table by year...
To clarify I am looking to query for posts annually, much like time-hop.
I have been running circles with this for about two weeks now, and help is much appreciated!
**edit current code
$sql = "SELECT * FROM posts
WHERE date > CURDATE() - INTERVAL 1 YEAR
ORDER BY date DESC";
// SELECT * FROM conversions WHERE date = CURRENT_DATE() - INTERVAL 1 DAY AND `user_id` = '$user_id';
$result = $dbc->query($sql);
if ($result->num_rows > 0) {
$i=1;
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<li>";
echo "<p>".$row['post_date']."</p>";
// $clientid.$i =
echo "<h3>".$row["post_content"]."</h3>";
echo "</li>";
}//end while
$i++;
} else {
echo "0 results found in user database!";
}
Your php code seems to be confusing the column names date (which appears in your WHERE clause) and post_date (which appears as a column name resulting from your fetch_assoc() clause. Do you actually have both date and post_date columns?
Also, it looks like the inequalities in your WHERE clauses might be off-by-one.
If you want to get the most recent year from today, it's
WHERE date >= CURDATE() - INTERVAL 1 YEAR
For the year prior to that, it's
WHERE date >= CURDATE() - INTERVAL 2 YEAR
AND date < CURDATE() - INTERVAL 1 YEAR
If it's the present calendar year you want, use this. It's a little complicated because it allows the use of an index on the date column. The DATE_FORMAT() expression turns the present date, e.g. 2015-03-10, into 2015-01-01.
WHERE date >= DATE_FORMAT(CURDATE(), '%Y-01-01')
Similarly, the calendar year prior to the present one is done like this.
WHERE date >= DATE_FORMAT(CURDATE(), '%Y-01-01') - INTERVAL 1 YEAR
AND date < DATE_FORMAT(CURDATE(), '%Y-01-01')
From the additional information in your comments, it sounds like you want to solve it in SQL using a YEAR interval, and probably order by descending date.
SELECT * FROM conversions
WHERE post_date > CURDATE() - INTERVAL 1 YEAR
ORDER BY post_date DESC