PHP MySQL calendar

[wrathican]

hi there, i followed the basic event calendar tutorial located on this website;

http://www.phpfreaks.com/tutorials/144/4.php

 

i get this sql error;

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/fhlinux179/l/ls12style.co.uk/user/htdocs/test/calendar.php on line 24

 

this is my line 24;

if (mysql_num_rows($sql) > 0) {

 

i added some code to print the mysql error and i got this;

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(event_date) AS day, COUNT(event_id) FROM calendar_events WHERE

 

any suggestions on what's going wrong?

my database has been populated with some basic events so my query shouldnt fail.

 

thanx in advance.

 

[dooper3]

It's usually a typo in the query. Please show the mysql_query bit.

[wrathican]

oh sorry, i did mean to put that in...

here ya go:

 

$sql = mysql_query("SELECT DAY(event_date) AS day, COUNT(event_id) FROM calendar_events WHERE MONTH(event_date) = '$month' AND YEAR(event_date) = '$year' GROUP BY day"); 

[dooper3]

hmmm, nothing obvious there and i compared it to the tutorial aswell. Perhaps there's a typo elsewhere in your document. Can you print the whole page as you have it please?

[wrathican]

well i copy and pasted the whole code at the end of the tutorial, to be honest i didnt think to check for typos but here it is, ill have a look myself and see if i can see any

<?php
// Database connection variables 
// Be sure to set these to your own connection 
$HOST = 'host'; 
$USER = 'user'; 
$PASS = 'pass'; 
$NAME = 'ls12style'; 

// Connect to MySQL database 
$con = mysql_connect($HOST, $USER, $PASS); 
if (!$con) { 
  // Since the entire script depends on connection, die if connection fails 
  die("Error connecting to MySQL database!"); 
} 
mysql_select_db($NAME, $con); 

function getEventDays($month, $year) { 
  $days = array(); 
  $sql = mysql_query("SELECT DAY(event_date) AS day, COUNT(event_id) FROM calendar_events WHERE MONTH(event_date) = '$month' AND YEAR(event_date) = '$year' GROUP BY day"); 
if(!$sql)
{
echo "theres has been an error: " . mysql_error();
}
  while ($row = mysql_fetch_array($sql)) $days[] = $row['day']; 
  } 

  return $days; 
} 

function drawCalendar($month, $year) { 
  // set variables we will need to help with layouts 
  $first       = mktime(0,0,0,$month,1,$year); // timestamp for first of the month 
  $offset      = date('w', $first); // what day of the week we start counting on 
  $daysInMonth = date('t', $first); 
  $monthName   = date('F', $first); 
  $weekDays    = array('Su', 'M', 'Tu', 'W', 'Th', 'F', 'Sa'); 
  $eventDays   = getEventDays($month, $year); 
   
  // Start drawing calendar 
  $out  = "<table id=\"myCalendar\">\n"; 
  $out .= "<tr><th colspan=\"7\">$monthName $year</th></tr>\n"; 
  $out .= "<tr>\n"; 
  foreach ($weekDays as $wd) $out .= "<td class=\"weekDays\">$wd</td>\n"; 
   
  $i = 0; 
  for ($d = (1 - $offset); $d <= $daysInMonth; $d++) { 
    if ($i % 7 == 0) $out .= "<tr>\n"; // Start new row 
    if ($d < 1) $out .= "<td class=\"nonMonthDay\"> </td>\n"; 
    else { 
      if (in_array($d, $eventDays)) { 
        $out .= "<td class=\"monthDay\">\n"; 
        $out .= "<a href=\"?year=$year&month=$month&day=$d\">$d</a>\n"; 
        $out .= "</td>\n"; 
      } else $out .= "<td class=\"monthDay\">$d</td>\n"; 
    } 
    ++$i; // Increment position counter 
    if ($i % 7 == 0) $out .= "</tr>\n"; // End row on the 7th day 
  } 
   
  // Round out last row if we don't have a full week 
  if ($i % 7 != 0) { 
    for ($j = 0; $j < (7 - ($i % 7)); $j++) { 
      $out .= "<td class=\"nonMonthDay\"> </td>\n"; 
    } 
    $out .= "</tr>\n"; 
  } 
   
  $out .= "</table>\n"; 
     
    return $out; 
} 

$year  = isset($_GET['year']) ? $_GET['year'] : date('Y'); 
$month = isset($_GET['month']) ? $_GET['month'] : date('m'); 

echo drawCalendar($month, $year); 

// Previous month link 
$prevTS = strtotime("$year-$month-01 -1 month"); // timestamp of the first of last month 
$pMax = date('t', $prevTS); 
$pDay = ($day > $pMax) ? $pMax : $day; 
list($y, $m) = explode('-', date('Y-m', $prevTS)); 
echo "<p>\n"; 
echo "<a href=\"?year=$year&month=$m&day=$pDay\">« Prev</a> |\n"; 

// Next month link 
$nextTS = strtotime("$year-$month-01 +1 month"); 
$nMax = date('t', $nextTS); 
$nDay = ($day > $nMax) ? $nMax : $day; 
list($y, $m) = explode('-', date('Y-m', $nextTS)); 
echo "<a href=\"?year=$y&month=$m&day=$nDay\">Next »</a>\n"; 
echo "</p>\n"; 

// Calendar is done, let's list events for selected day 
$sql = mysql_query("SELECT * FROM calendar_events WHERE event_date = '$year-$month-$day'"); 
if (mysql_num_rows($sql) > 0) { 
  while ($e = mysql_fetch_array($sql)) { 
    echo "<p>$e[event_title]</p>\n"; 
  } 
} else { 
  echo "<p>No events today</p>\n"; 
} 
?>

[wrathican]

well i found a missing { on a while statement but that seems to be the only thing i found...

i really need this to be done asap, its for a client

thanks for your replies though

[wrathican]

bump?

[AndyB]

Have you tried your query directly in phpMyAdmin (or whatever you use to diddle your database)?

[wrathican]

yeah i still get the same error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(event_date) AS day, COUNT(event_id) FROM calendar_events WHERE

 

i took the query right from the tutorial

[teng84]

dude im not sure with this but is this correct?

DAY(event_date) AS day

 

i dont see any day function in my  mind ?

[Barand]

If you have an old mysql version, try DAYOFMONTH instead of DAY

[wrathican]

woohoo, thank you thank you thank you

that worked, now the only problem i have is that when i select a day to view no events on my calendar appear...