comparing and converting dates

timmybuck

Hi guys,

im trying to compare two dates, I want to compare a date in the mysql database with the current date.

Now i know i need to convert the mysql date to a unix timestamp in order to make the comparison.

I have table called event and in thsi table there is a column called expired that is of type date

i have this so far

if (($row['Expired'] < time()) || ($row['Total_Seats'] >= $row['Booked_Seats']))

but i think i need to convert the date out of the mysql database first

any ideas

mrhappiness

search http://www.mysql.com/doc/en/Date_and_time_functions.html for UNIX_TIMESTAMP(date)

timmybuck

you rnot making me happy mr happiness. Need more help then that ...lol

Anyone else

mrhappiness

well, show me your SELECT

you should change SELECT Expired FROM table to
SELECT UNIX_TIMESTAMP(expired) Expired FROM table

timmybuck

SELECT UNIX_TIMESTAMP(expired) Expired FROM event;

if (($row['Expired'] < time()) || ($row['Total_Seats'] >= $row['Booked_Seats']))

would these two lines get the date from the mysql database table called event and allow me compare it with the current date

ignore the total seats bit, thats also gonna be part of my query

mrhappiness

depends on the php code between the mysql_query submitting the query and the if you posted :-)

what exactly do you want to do?
maybe you can do all with a single SLECt without the need of some php code checking the results

timmybuck

well what im trying to do is make it so that events that have expired or if the total seats have been fully booked do not get displayed as hyperlinks

here is the rest to of my code

while ($row = mysql_fetch_array($result)) {

$sql1 = "select Total_Seats, Booked_Seats FROM event where EventID =".$row[EventID]."";
$result1 = mysql_query($sql1);

$a = ($a+1)%2;

echo '<tr bgcolor='.$color[$a] . ">\n";

#echo ' <td>'.$row['EventName'].'</td>'."\n";
SELECT UNIX_TIMESTAMP(Expired) AS Expired FROM table WHERE 'Expired'='current date'
if if (($row['Expired'] < time()) || ($row['Total_Seats'] >= $row['Booked_Seats']))
{
echo '<td><a href="tester(1).php?EventName=' . $row['EventName'] .
'">' . $row['EventName'] .'</a></td>';
}

else {
echo '<td>'.$row['EventName'].'</td>';
}

echo ' <td>'.$row['Date'].'</td>'."\n";

echo ' <td>'.$row['Venue'].'</td>'."\n";

echo ' <td>'.$row['Town'].'</td>'."\n";

timmybuck

ok well i have come up with some code that comares current date with date in database

but am still getting errors, have pasted the whole page in case its something else thast the problem

<?php
include("/Program Files\Apache Group\Apache2\htdocs\m82\software_common.inc");

intrahead("E-tickets.co.uk"); //escapes writing the HTML header code
?>

<?php
$today = date("d/m/y");
echo $today;
?>

<table width="63%" height="493" border="0" align="center" cellpadding="2" cellspacing="2" bgcolor="#FFFFFF">
<tr align="left" valign="top" bgcolor="#FFFFFF">
<td align="center" bgcolor="#FFFFFF">
<?php
$categoryID=$POST['categoryID'];
$EventID=$GET['EventID'];
$color[0] = "EFEFEF";

$color[1] = "98AAB1";
mysql_connect("localhost","phpuser"," ") or die ("Database connection error");

mysql_select_db('booking');

$sql = "SELECT * FROM event WHERE categoryID='$categoryID'";
$result = mysql_query($sql);
#while ($row = mysql_fetch_array($result)) {
#echo $row['EventName'] . ',' . $row['Date'] . ',' . $row['Venue'] . " \n";
echo "<table>\n";

echo " <tr>\n";

echo " <th>Event Name</th>\n";

echo " <th>Date</th>\n";

echo " <th>Venue</th>\n";

echo " <th>Town</th>\n";

echo " <th>Availability</th>\n";
echo " </tr>\n";

while ($row = mysql_fetch_array($result)) {

$sql1 = "select Total_Seats, Booked_Seats FROM event where EventID =".$row[EventID]."";
$result1 = mysql_query($sql1);

$a = ($a+1)%2;

echo '<tr bgcolor='.$color[$a] . ">\n";

#echo ' <td>'.$row['EventName'].'</td>'."\n";

$sql = "select Expired from events where expired=CURDATE()";
$result = mysql_query($sql);
while ($row1 = mysql_fetch_array($result)) {

if ($row1['Expired']|| $row1['Total_Seats']>=$row1['Booked_Seats']){
echo '<td><a href="tester(1).php?EventName=' . $row['EventName'] .
'">' . $row['EventName'] .'</a></td>';
}

else {
echo '<td>'.$row['EventName'].'</td>';
}
}

echo ' <td>'.$row['Date'].'</td>'."\n";

echo ' <td>'.$row['Venue'].'</td>'."\n";

echo ' <td>'.$row['Town'].'</td>'."\n";

while ($row1 = mysql_fetch_array($result1)) {

$row[Availabilty]

if( $row1['Total_Seats']>=$row1['Booked_Seats']) {
echo "<td align=center><img src=\"image/happy.jpg\" alt=\"This show is available\" width=\"30\" height=\"30\"></td>";
} else {
echo "<td align=center><img src=\"image/sad.jpg\" alt=\"This show is fully booked\" width=\"30\" height=\"30\"></td>";
#echo ' <td>'.$row['Total_Seats'].'</td>'."\n";
}
}
echo ' </tr>'."\n";

}
echo '</table>';

?>

</td>
</tr>
</table>

<?php
intrafoot(); //escapes writing the HTML footer code

this is my error message

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\Apache Group\Apache2\htdocs\m82\resultstest.php on line 54
9/05/04 NEC Birmingham

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\Apache Group\Apache2\htdocs\m82\resultstest.php on line 44