Using date link from a calender to query table

[osward]

Hi everyone,

Background
1. I have a table that consits 400+ rows of data and is growing by day. The table already has paging links at the bottom but I restricted to display rows of data only >= current date. Otherwise, user have to waste time to page up the pages to find the current date

2. I got a script of simple calendar from the web that use mktime() to create links on the calendar

Task
I need to let user view data earlier than today, I have to do either one of the things
a) I am able to show all the database records and when user request the table, I can show them the right page. ie. I am not showing the 1st page of the table but rather the page consits of today's date.

b) I make use of the simple calendar date link as a navigation mechanism and display the rows of data of the request date and later. Personally I think this is cool than (a) and more efficient to browse around the table.
The query to the table is as follows:
[PHP]$result = $db->sql_query("SEL ECT * FROM ".$prefix."_eve nt_cat WHERE date >= CURDATE() ORDER by $sort_order LIMIT $from, $max_results") or die("Error: " . mysql_error());
if($db->sql_numrows($r esult) == 0){
echo(""._NODATA ."");
}[/PHP]and the links of the calendar is

[PHP]// Build Previous and Next Links
$previous_link = "<a href=\"/modules.php?nam e=event&date=";
if($month == 1){
$previous_link .= mktime(0,0,0,12 ,$day,($year -1));
} else {
$previous_link .= mktime(0,0,0,($ month -1),$day,$year);
}
$previous_link .= "\"><< "._PREVMON. "</a>";

$next_link = "<a href=\"/modules.php?nam e=event&date=";
if($month == 12){
$next_link .= mktime(0,0,0,1, $day,($year + 1));
} else {
$next_link .= mktime(0,0,0,($ month +1),$day,$year) ;
}
$next_link .= "\">"._NEXTMON. " >></a>";

// Now we break each key of the array
// into a week and create a new table row for each
// week with the days of that week in the table data

$i = 0;
foreach($weeks AS $week){
echo "<tr>\n";
foreach($week as $d){
if($i < $offset_count){
$day_link = "<a href=\"modules. php?name=event& date=".mktime(0 ,0,0,$month -1,$d,$year)."\" >$d</a>";
echo "<td class=\"nonmont hdays\">$day_li nk</td>\n";
}
if(($i >= $offset_count) && ($i < ($num_weeks * 7) - $outset)){
$day_link = "<a href=\"modules. php?name=event& date=".mktime(0 ,0,0,$month,$d, $year)."\">$d</a>";
if($date == mktime(0,0,0,$m onth,$d,$year)) {
echo "<td class=\"today\" >$d</td>\n";
} else {
echo "<td class=\"days\"> $day_link</td>\n";
}
} elseif(($outset > 0)) {
if(($i >= ($num_weeks * 7) - $outset)){
$day_link = "<a href=\"modules. php?name=event& date=".mktime(0 ,0,0,$month +1,$d,$year)."\ ">$d</a>";
echo "<td class=\"nonmont hdays\">$day_li nk</td>\n";
}
}
$i++;
} [/PHP]
How am I going to send the date link to the query, I know I need to convert the date by d= date("Y-m-d", $date) because my date field in the table is "Y-m-d" format to do the comparison. I just couldn't figure out how I am going to put into the query

Please help me in either way, doing (a) or (b)

Thanks in advance

[pbmods]

Heya, Osward.

I'd see something like this:
[code=php]
$__targetDate =
strtotime
(
$_POST['year']
. '-'
. $_POST['month']
. '-'
. $_POST['day']
);
if( $__targetDate )
{
$__minDate = "'" . date('Y-m-d', $__targetDate) . "'";
}
else
{
$__minDate = 'CURDATE()';
}

$result =
$db->sql_query
("
SELECT
*
FROM
`{$prefix}_even t_cat`
WHERE
`date` >= {$__minDate}
ORDER BY
`{$sort_order}`
LIMIT {$from}, {$max_results}"
)
or exit('Error: ' . mysql_error());
[/code]