Mysql Time type in php

[abch624]

Hi There,

 

I am trying to run an sql statement that gets input from a form.

 

The form code is

<table border="0" cellpadding="0" cellspacing="0" width="600" align="center">
	<tr>
		<td>
			<form class="form" method="post" action="automatedemaildo.php" align="center">
				<table border="0" cellpadding="0" cellspacing="0" width="600" align="center">
		   		  	<tr>
     				  	  	<td width="250" class="formEdit">To:</td>
     					  	<td width="300"><input name="to" value="<?php echo $to_new_email;?>" style="WIDTH: 300px; HEIGHT: 22px" size="34"></td>
     				 	</tr>
					<tr>
     				  	  	<td width="250" class="formEdit">Subject:</td>
     					  	<td width="300"><input name="subject" value="<?php echo $subject_new_email; ?>" style="WIDTH: 300px; HEIGHT: 22px" size="34"></td>
     				 	</tr>
					<tr>
     				  	  	<td width="250" class="formEdit">When:</td>
     					  	<td><input name="whenDate" value="<?php if ($whenDate_new_email=="0000-00-00") {echo "YYYY-MM-DD";} else {echo $whenDate_new_email;}?>" style="WIDTH: 100px; HEIGHT: 22px" size="34"><input name="whenTime" value="<?php if ($whenTime_new_email=="00:00:00") {echo "HH:MM";} else {echo $whenTime_new_email;}?>" style="WIDTH: 100px; HEIGHT: 22px" size="34"></td>
     				 	</tr>
					<tr>
     				  	  	<td width="250" class="formEdit">Repeat:</td>
     					  	<td width="300">
							<select name="repeat">
								<option value="once" <?php if ($repeat_new_email == "once") { echo 'selected=""';} ?>>Once</option>
								<option value="daily" <?php if ($repeat_new_email == "daily") { echo 'selected=""';} ?>>Daily</option>
								<option value="weekly" <?php if ($repeat_new_email == "weekly") { echo 'selected=""';} ?>>Weekly</option>
								<option value="monthly" <?php if ($repeat_new_email == "monthly") { echo 'selected=""';} ?>>Monthly</option>
								<option value="yearly" <?php if ($repeat_new_email == "yearly") { echo 'selected=""';} ?>>Yearly</option>
							</select>
						</td>
     				 	</tr>
					<tr>
     				  	  	<td width="250" class="formEdit" valign="top">Message:</td>
     					  	<td colspan="2" width="400"><textarea style="WIDTH: 300px; HEIGHT: 85px" rows="5" cols="35" name="body"> <?php echo $body_new_email;?></textarea></td>
     				 	</tr>
					<tr>
						<td class="formEdit"></td>
						<td align="left"><input type="submit" value="Save"></td>
     				 	</tr>
  					</table>
			</form>
		 </td>
	</tr>
</table>

The sql statements which is

$sql_automatedemail_update="UPDATE $tbl_automatedmessages SET to='$to', whenDate='$whenDate', whenTime='$whenTime', repeat='$repeat', subject='$subject', body='$body' WHERE automatedMessagesID='$automatedMessagesID'";

 

The whenDate is of type Date in the Database and the whenTime is of type Time in the Database. When I reach the sql statement on the php script then I get the 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 'to='Email Address', whenDate='YYYY-MM-DD', whenTime='HH:MM:00', repeat='once', s' at line 1

 

Any kind of help - Cheers

[br0ken]

I don't really know a lot about Database time/date columns as I usually use my own date format in a CHAR(14) column but anyway, nobody else had replied so I thought I'd try and help!

 

You have the following in your query...

 

whenDate='YYYY-MM-DD'

 

But should it be something like...

 

whenDate='2008-12-02'

 

Just a thought.

[abch624]

Nice spot but that was my mistake. I have tried this with the correct format but I still get the same eeroor....

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 'to='Email Address', whenDate='2009-01-20', whenTime='20:25:00', repeat='once', s' at line 1

[br0ken]

Ah, figured it out. You have a field named 'to' however 'to' is a reserved word in MySQL similar to SELECT or INSERT etc. Reserved words are reserved and therefore cannot be used for column or table names as they are always interpreted as a MySQL statement.

 

If you change the field name everything should work fine.

 

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

[abch624]

Nice one,

That did not come to my mind at all..

Thanks a lot. Works now.

[PFMaBiSmAd]

For mysql syntax errors, the part right after the single-quote in - ...the right syntax to use near '... is where mysql found syntax it could not figure out.