[SOLVED] Query database date field for specific month

[neogemima]

Hello,

 

I need help searching and displaying results from a database with a date field in the format YYYY-MM-DD.

 

I have a database where anyone can post News at any time and is kept track of by a unique id and the date in the above format.

 

I would like to query the database to return only the rows with a specific month (i.e. only news from month of April for ex.).  How would I go about doing this?

 

Here is my incorrect sample code:

<?php
//begin may09 news doc

@ $db = mysqli_connect('database', 'xxxxx', 'xxxxx', 'xxxxx');

if (mysqli_connect_errno($db)) {
	echo 'Error: Could not connect to database.  Please try again later.';
}

mysqli_select_db($db, 'xxxxx');
$result = mysqli_query($db, "SELECT date FROM Newsposting");

//I think this is where I am having trouble.  I don't know what kind of loop, if any, to use to display each row.

$i = $result;

while($i = date("Y-04-d")) {
	$sql = "SELECT id, date, title FROM Newsposting WHERE date = date('Y-04-d')";
	$queryresult = mysqli_query($db, $sql);
	$rowresult = mysqli_fetch_array($queryresult, MYSQLI_ASSOC);

	//printf ("<u>(%s) %s</u>\n", $rowresult["date"], $rowresult["title"]);
	$id = $rowresult['id'];

	echo '<a href="article.php?id='.$id.'">'.$rowresult['date'].'     '.$rowresult['title'].'</a>';
	echo "<br>";
	}

?>

 

I am definitely a noob at this and appreciate the help.

[alphanumetrix]

What date format are you storing in the database?

[neogemima]

The standard date format YYYY-MM-DD

[PFMaBiSmAd]

If you literally want all April dates (independent of the year), use the mysql MONTH() function http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_month -

 

WHERE MONTH(date) = 4

 

If you in fact want April dates of a specific year, use the mysql EXTRACT() function with the YEAR_MONTH parameter  http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_extract -

 

WHERE EXTRACT(YEAR_MONTH FROM date) = 200904

[neogemima]

That worked perfectly.  I did need to reference the year as well.  This is the code I used for April:

 

for($i = $num_rows; $i >= 0; $i--) {
	$sql = "SELECT id, date, title FROM Newsposting WHERE EXTRACT(YEAR_MONTH FROM date) = 200905 AND id = $i";
	$queryresult = mysqli_query($db, $sql);
	$rowresult = mysqli_fetch_array($queryresult, MYSQLI_ASSOC);

	//printf ("<u>(%s) %s</u>\n", $rowresult["date"], $rowresult["title"]);
	$id = $rowresult['id'];

	echo '<a href="article.php?id='.$id.'">'.$rowresult['date'].'     '.$rowresult['title'].'</a>';
	echo "<br>";
	}

 

Thanks for the help.