[SOLVED] Upcomming events query problem

[dessolator]

Hi,

 

I'm trying to do a query for an upcoming events script so that only events from the future are displayed (using php and mysql). Firstly I get the date then store it in a variable called $date then i run this query:

$query = "SELECT event_name, event_date, event_time, event_location, event_limit FROM events WHERE (event_date > '$date')";

 

But there are no rows being returned I have 2 dates in the database: 10/01/08 and 10/09/07 so there should only be 1 shown. I have tried the where statement with brackets around it in several different ways but it still won't work.

 

Please could you take a look and see where I have gone wrong.

 

Thanks,

Ian

 

 

 

Full code:

<?php
//Retreives date and stores it in $date variable
$date = date('d/m/y');
echo $date;

$host="localhost"; // Host name
$username="root"; // Mysql username
$password="abc123"; // Mysql password
$db_name="colab_booking"; // Database name
$tbl_name="events"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

//Database - Assigns the below statement into the variable query
$query = "SELECT event_name, event_date, event_time, event_location, event_limit FROM events WHERE (event_date > '$date')";
//$query2 = "SELECT GameID FROM results WHERE (PlayerY= '$first_student' OR PlayerG= '$second_student') AND Game = '$game'";

$result = mysql_query($query);

  echo '<table border=0 align="center">
<tr>
<th align="center" bgcolor="orange">Event</th>
<th align="center" bgcolor="orange">Date</th>
<th align="center" bgcolor="orange">Time</th>
<th align="center" bgcolor="orange">Location</th>
<th align="center" bgcolor="orange">Max Limit</th>
<th align="center" bgcolor="orange">Book a place</th>
</tr>';

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
    echo "<td bgcolor='#CCCCCC' align='center'>".$row[0] . " "."</td>";
  echo "<td bgcolor='#CCCCCC' align='center'>".$row[1] . " "."</td>";  
  echo "<td bgcolor='#CCCCCC' align='center'>".$row[2] . " "."</td>"; 
      echo "<td bgcolor='#CCCCCC' align='center'>".$row[3] . " "."</td>";
  echo "<td bgcolor='#CCCCCC' align='center'>".$row[4] . " "."</td>";  
    echo "<td bgcolor='#CCCCCC' align='center'><a href='/book.php'/>Book Here</a></td>";  
}
  echo "</tr>";
echo "</table>";
?>

[p2grace]

Try changing the date format to this:

 

$date = date("Y-m-d");

 

That was the first thing that stuck out to me.  If it still doesn't work we can go from there.

[revraz]

Yep, or convert it to unix time, which is how I do my event calendar.

[SuperBlue]

Since i've been working alot with date/time lately, i can only agree; I figured it would be easier to use Unix time.

Its much easier to save entries in the database using Unix time, and then convert it using something like "echo date("d/m/Y", $time);".

 

http://dk2.php.net/time

[dessolator]

Thanks for your comments,

 

I changed the data type for event_date to timestamp in the database (but when i put the function as unix timestamp it keeps going back to saying now) and I'm now using the $_SERVER['REQUEST_TIME']  command (which is being stored in the $date variable). It said in this tutorial (http://www.kirupa.com/web/php_unix_timestamp.htm) to put while($row =  mysql_fetch_assoc($result)) instead of fetch_array. But it is returning 2 rows still with blank data in the table.

 

Event Date Time Location Max Limit Book a place

                                                      Book Here

                                                      Book Here

 

I would appreciate it if someone could tell me where I'm messing up.

 

Thanks,

Ian

 

Here is my new code:

<?php
//Retreives date and stores it in $date variable
//$date = date('d/m/y');
$date = $_SERVER['REQUEST_TIME'];
echo $date;

$host="localhost"; // Host name
$username="root"; // Mysql username
$password="abc123"; // Mysql password
$db_name="colab_booking"; // Database name
$tbl_name="events"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

//Database - Assigns the below statement into the variable query
//$query = "SELECT event_name, event_date, event_time, event_location, event_limit FROM events WHERE (event_date > '$date')";

  echo '<table border=0 align="center">
<tr>
<th align="center" bgcolor="orange">Event</th>
<th align="center" bgcolor="orange">Date</th>
<th align="center" bgcolor="orange">Time</th>
<th align="center" bgcolor="orange">Location</th>
<th align="center" bgcolor="orange">Max Limit</th>
<th align="center" bgcolor="orange">Book a place</th>
</tr>';

$sql = "SELECT event_name, event_date, event_time, event_location, event_limit FROM events WHERE (event_date > $date)";
$result = mysql_query($sql);

while($row =  mysql_fetch_assoc($result))
  {
  echo "<tr>";
    echo "<td bgcolor='#CCCCCC' align='center'>".$row[0] . " "."</td>";
  echo "<td bgcolor='#CCCCCC' align='center'>".$row[1] . " "."</td>";  
  echo "<td bgcolor='#CCCCCC' align='center'>".$row[2] . " "."</td>"; 
      echo "<td bgcolor='#CCCCCC' align='center'>".$row[3] . " "."</td>";
  echo "<td bgcolor='#CCCCCC' align='center'>".$row[4] . " "."</td>";  
    echo "<td bgcolor='#CCCCCC' align='center'><a href='/book.php'/>Book Here</a></td>";  
}
  echo "</tr>";
echo "</table>";
?>

[revraz]

You changed to mysql_fetch_assoc but left the Array index as arrays.  So change it back to mysql_fetch_array or change $row[0] to the first field name, $row['event_name'], etc.

[p2grace]

Since you're using mysql_fetch_assoc it's not going to return $row[0] it'll be $row['event_name'].  I would actually suggest writing your while statement like this:

 

while($row =  mysql_fetch_assoc($result)) {
  extract($row);
  echo "<tr>";
    echo "<td bgcolor='#CCCCCC' align='center'>$event_name</td>";
  echo "<td bgcolor='#CCCCCC' align='center'>$event_date</td>";  
  echo "<td bgcolor='#CCCCCC' align='center'>$event_time</td>"; 
      echo "<td bgcolor='#CCCCCC' align='center'>$event_location</td>";
  echo "<td bgcolor='#CCCCCC' align='center'>$event_limit</td>";  
    echo "<td bgcolor='#CCCCCC' align='center'><a href='/book.php'/>Book Here</a></td>";  
}

[revraz]

Also, here is a shorter way to get your date

 

$date =time () ;

[dessolator]

I have changed the code to use the mysql_fetch_assoc code and updated the time and while loop but now its showing nothing in the table (only the table headers).

 

The timestamp values in the event_date column in the db keep reverting back to 0000-00-00 00:00:00 and keeps changing back to now instead of unix_timestamp, is it possible just to store the values in a normal varchar?

 

 

Thanks,

 

Ian

 

 

<?php
//Retreives date and stores it in $date variable
//$date = date('d/m/y');
$date =time () ;
echo $date;

$host="localhost"; // Host name
$username="root"; // Mysql username
$password="abc123"; // Mysql password
$db_name="colab_booking"; // Database name
$tbl_name="events"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

//Database - Assigns the below statement into the variable query
//$query = "SELECT event_name, event_date, event_time, event_location, event_limit FROM events WHERE (event_date > '$date')";

  echo '<table border=0 align="center">
<tr>
<th align="center" bgcolor="orange">Event</th>
<th align="center" bgcolor="orange">Date</th>
<th align="center" bgcolor="orange">Time</th>
<th align="center" bgcolor="orange">Location</th>
<th align="center" bgcolor="orange">Max Limit</th>
<th align="center" bgcolor="orange">Book a place</th>
</tr>';

$sql = "SELECT event_name, event_date, event_time, event_location, event_limit FROM events WHERE (event_date > $date)";
$result = mysql_query($sql);

while($row =  mysql_fetch_assoc($result)) {
  extract($row);
  echo "<tr>";
    echo "<td bgcolor='#CCCCCC' align='center'>$event_name</td>";
  echo "<td bgcolor='#CCCCCC' align='center'>$event_date</td>";  
  echo "<td bgcolor='#CCCCCC' align='center'>$event_time</td>"; 
      echo "<td bgcolor='#CCCCCC' align='center'>$event_location</td>";
  echo "<td bgcolor='#CCCCCC' align='center'>$event_limit</td>";  
    echo "<td bgcolor='#CCCCCC' align='center'><a href='/book.php'/>Book Here</a></td>";  
}
  echo "</tr>";
echo "</table>";
?>

[p2grace]

I'd save it as an integer.

[revraz]

Yep, needs to be a Integer or else your compares won't work.

[dessolator]

Thanks very much for your help its much appreciated. I'm just trying to work out how to convert a drop down menu with the date and time in 6 separate drop downs (Format |Day|Month|Year|    |Hour|Minute|) on a form to the time stamp format so I can insert it into the database but can't find any info on google and I was wondering if anyone could help?

 

Thanks,

Ian

[revraz]

I opted to just display a required format instead.  I weighed the options, and all those pulldowns just looked too messy for me.

 

So I force them to enter the date/time in a MM/DD/YYYY 00:00 format on one line, and if it doesn't match, I don't accept the input.

[p2grace]

You could do it the way revraz suggested and use a calendar script to auto-fill that data.  There's a lot of JS Calendar's out there that support unix timestamps, I'd suggest checking one of those out.  I'd recommend JSCalendar http://sourceforge.net/projects/jscalendar.

[dessolator]

Thanks very much for all your help, its all sorted and running fine

 

Ian

[revraz]

What did you end up doing?

[dessolator]

I used the text box method as I couldn't get the calendar to show the unix timestamp: I used the mktime function to convert it into the timestamp then: $standardDate = date("D d/m/Y H:i:s", $event_timestamp); to convert it back.

 

Thanks again,

 

Ian

[revraz]

Right on