query based on date

[adamhhh]

Hello, i was wondering if this was possible as I cant quite think how (maybe too early in the morning)

 

I need to run a query based on the last 6 months of entries.

 

In my table i have the usual headers e.g. name and body of text etc, but also a date_created column with stuff like 2005-12-20 15:44:37 in it.

 

Any ideas?

[adamhhh]

<?
//$document_id = $_REQUEST['document_id'];
//echo $document_id;
$document_id = 110;

$host = "";
$user = "";
$password = "";
$database = "";

$connection = mysql_connect($host,$user,$password)
or die ("Couldn't connect to server.");
$db = mysql_select_db($database, $connection)
or die ("Couldn't select database.");

$query = "SELECT * FROM sections WHERE document_id = $document_id";	

$result = mysql_query($query)
or die ("This entry hasn't been found.");

    while($row = mysql_fetch_array($result))
{
$section_heading = $row['section_heading'];
$section_id =  $row['section_id'];

//echo "<strong> $section_heading,</strong>";
//echo $section_id;

$query1 = "SELECT * FROM fragments WHERE section_id = $section_id ORDER BY date_created";	

$result1 = mysql_query($query1)
or die ("This entry hasn't been found.");

while($row1 = mysql_fetch_array($result1))
{
$fragment_heading = $row1['fragment_heading'];
$date_created = $row1['date_created'];
//echo "<a href=\"index.php?section_id=$section_id&document_type_id=2\">$fragment_heading</a>";
//echo "<br />";
//echo $date_created;

$dateArray=explode('-',$date_created);

// $dateArray[0]= 2007
// $dateArray[1] = 02
// $dateArray[2] = 05

$start_date = date('Y-m-d', mktime(0, 0, 0, $dateArray[1], $dateArray[2], $dateArray[0])); 

echo $start_date;

$test = $dateArray[1];

//echo $test;
$end_date = $test - 6;
if ($end_date < 1)
{
$new_end_date = $end_date + 12;
$dateArray[0] --;
//echo $new_year;
//echo $end_date;
echo "<br />";
//echo $new_end_date;
$new_end_date2 = "0" . $new_end_date;
//echo $new_end_date2;
$date1 = $dateArray[1] . $new_end_date2 . $dateArray[0];
echo "<br />";

//echo $date1;
//$today = date("j, n, Y")
$new_date = date('Y-d-m', mktime(0, 0, 0, $dateArray[1], $new_end_date2, $dateArray[0]));
// $new_date is the end date we use
echo $new_date;
}
$new_date = $new_date1;
$start_date = $start_date1;
$query2 = "SELECT * FROM fragments WHERE section_id = $section_id AND date_created BETWEEN $new_date1 AND $start_date1";	
echo $query2;
$result2 = mysql_query($query2)
or die ("This entry hasn't been found.");

while($row2 = mysql_fetch_array($result2))
{
$fragment_heading = $row2['fragment_heading'];
$date_created = $row2['date_created'];
//echo "<a href=\"index.php?section_id=$section_id&document_type_id=2\">$fragment_heading</a>";
echo "<br />";
echo $fragment_heading;
} 
} 
}
?>

 

hmm at the moment its nearly working, however im a bit confused as in $query2 im loking at the $section_id (of which there are multiple) and $new_date and $start_date (1 of each). Ive retrieved the $new_date and $start_date from the database so they are i think 2007-09-26 and 2006-03-26. However I cannot get the query to run so its multiple section ids based on one of each $start_date and $new_date. Any ideas??

[tauchai83]

there maybe a mysql internal function that can perform the same job. But using PHP, it could be done as well, but the code is longer. You need to ask fenway who is a mysql sifu here. You should post in mysql topic there. Thanks.

[adamhhh]

thanks but whats sifu 

[taith]

lol... martial arts term... means "master"