By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,086 Members | 1,452 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,086 IT Pros & Developers. It's quick & easy.

Using date link from a calender to query table

osward
P: 38
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("SELECT * FROM ".$prefix."_event_cat WHERE date >= CURDATE() ORDER by $sort_order LIMIT $from, $max_results") or die("Error: " . mysql_error());
if($db->sql_numrows($result) == 0){
echo(""._NODATA."");
}[/PHP]and the links of the calendar is

[PHP]// Build Previous and Next Links
$previous_link = "<a href=\"/modules.php?name=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?name=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=\"nonmonthdays\">$day_link</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,$month,$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=\"nonmonthdays\">$day_link</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
Nov 5 '07 #1
Share this Question
Share on Google+
1 Reply


pbmods
Expert 5K+
P: 5,821
Heya, Osward.

I'd see something like this:
Expand|Select|Wrap|Line Numbers
  1. $__targetDate =
  2.     strtotime
  3.     (
  4.         $_POST['year']
  5.         . '-'
  6.         . $_POST['month']
  7.         . '-'
  8.         . $_POST['day']
  9.     );
  10. if( $__targetDate )
  11. {
  12.     $__minDate = "'" . date('Y-m-d', $__targetDate) . "'";
  13. }
  14. else
  15. {
  16.     $__minDate = 'CURDATE()';
  17. }
  18.  
  19. $result =
  20.     $db->sql_query
  21.     ("
  22. SELECT
  23.         *
  24.     FROM
  25.         `{$prefix}_event_cat`
  26.     WHERE
  27.         `date` >= {$__minDate}
  28.     ORDER BY
  29.         `{$sort_order}`
  30.     LIMIT {$from}, {$max_results}"
  31.     )
  32.         or exit('Error: ' . mysql_error());
  33.  
Nov 27 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.