[SOLVED] Could not execute query

[gavenf]

Hi there,

 

I have a db that I am trying to poulate using an online form but every time I press the submit button it givesm me this error:

 

Could not execute query : INSERT INTO register (id, name, phone, email, interest, other, type, comments, update) VALUES ('', 'Tom', '0000000', 'abc@abc.com.au', 'Music Performer', 'other things', 'painting', 'none really', 'Y').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 'update) VALUES ('', 'Tom', '0000000', 'abc@abc.com.au', 'Music

 

I am not sure what the problem is.  Can someone have a look and tell me what I need to fix to make this owrk.

 

Here is the code for the add.php page which I use:

<form id="FormName" action="added.php" method="post" name="FormName">
<table width="448" border="0" cellspacing="2" cellpadding="0">
<tr><td width = "150"><div align="right"><label for="name">name</label></div></td>
<td><input id="name" name="name" type="text" size="25" value="" maxlength="255"></td></tr><tr><td width = "150"><div align="right"><label for="phone">phone</label></div></td>
<td><input id="phone" name="phone" type="text" size="25" value="" maxlength="255"></td></tr><tr><td width = "150"><div align="right"><label for="email">email</label></div></td>
<td><input id="email" name="email" type="text" size="25" value="" maxlength="255"></td></tr><tr><td width = "150"><div align="right"><label for="interest">interest</label></div></td>
<td><select id="interest" name="interest" size="1">
<option value="one">first</option>
<option value="two">second</option>
</select></td></tr><tr><td width = "150"><div align="right"><label for="other">other</label></div></td>
<td><textarea id="other" name="other" rows="4" cols="40"></textarea></td></tr><tr><td width = "150"><div align="right"><label for="type">type</label></div></td>
<td><input id="type" name="type" type="text" size="25" value="" maxlength="255"></td></tr><tr><td width = "150"><div align="right"><label for="comments">comments</label></div></td>
<td><textarea id="comments" name="comments" rows="4" cols="40"></textarea></td></tr><tr><td width = "150"><div align="right"><label for="update">update</label></div></td>
<td><input id="update" name="update" type="checkbox" value="Y"></td></tr><tr><td width="150"></td><td>
<input type="submit" name="submitButtonName" value="Add"></td>
</tr></table></form>

 

Here is the added.php page

<a href="index.php">Back to List</a>

<?php

include("connect.php");
$name = $_POST['name'];
$phone = $_POST['phone'];
$email = $_POST['email'];
$interest = $_POST['interest'];
$other = $_POST['other'];
$type = $_POST['type'];
$comments = $_POST['comments'];
$update = $_POST['update'];

$query = "INSERT INTO register (id, name, phone, email, interest, other, type, comments, update)
VALUES ('', '$name', '$phone', '$email', '$interest', '$other', '$type', '$comments', '$update')";

$results = mysql_query($query) or die 
("Could not execute query : $query." . mysql_error());

if ($results)
{
echo "Details added.";
}
mysql_close();
?>

 

and here is the index.php that the added.php refers to:

<a href="add.php">Add entry</a><br>
<br>
<?php


include("connect.php");

$query="SELECT * FROM register ";
$result=mysql_query($query);
$num = mysql_num_rows ($result);
mysql_close();

if ($num > 0 ) {
$i=0;
while ($i < $num) {
$name = mysql_result($result,$i,"name");
$phone = mysql_result($result,$i,"phone");
$email = mysql_result($result,$i,"email");
$interest = mysql_result($result,$i,"interest");
$other = mysql_result($result,$i,"other");
$type = mysql_result($result,$i,"type");
$comments = mysql_result($result,$i,"comments");
$update = mysql_result($result,$i,"update");
$id = mysql_result($result,$i,"id");

echo "<b>name:</b> $name<br>";
echo "<b>phone:</b> $phone<br>";
echo "<b>email:</b> $email<br>";
echo "<b>interest:</b> $interest<br>";
echo "<b>other:</b> $other<br>";
echo "<b>type:</b> $type<br>";
echo "<b>comments:</b> $comments<br>";
echo "<b>update:</b> $update<br>";
echo "<a href=\"update.php?id=$id\">Update</a> - <a href=\"delete.php?id=$id\">Delete</a>";
echo "<br><br>";

++$i; } } else { echo "The database is empty"; }?>

[Illusion]

post connect.php code and your table structure.

[gavenf]

post connect.php code and your table structure.

 

Connect.php

<?php
/// For the following details,
/// please contact your server vendor

$hostname='localhost'; //// specify host, i.e. 'localhost'
$user='circus'; //// specify username
$pass='gallery'; //// specify password
$dbase='circusart'; //// specify database name
$connection = mysql_connect("$hostname" , "$user" , "$pass") 
or die ("Can't connect to MySQL");
$db = mysql_select_db($dbase , $connection) or die ("Can't select database.");
?>

 

 

table structure

CREATE TABLE `register` (
`id` int(6) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`phone` varchar(255) NOT NULL default '',
`email` varchar(255) NOT NULL default '',
`interest` varchar(255) NOT NULL default '',
`other` text NOT NULL default '',
`type` varchar(255) NOT NULL default '',
`comments` text NOT NULL default '',
`update` enum('N','Y') NOT NULL default 'N',
PRIMARY KEY  (`id`),
UNIQUE KEY `id` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

[Illusion]

As you imposed NOT NULL constraint, you can't  insert the value '' ,so change it to ' '.

[gavenf]

Which one should I change to ' ' ?  YOu did not specify which one.  Sorry to ask

 

 

 

As you imposed NOT NULL constraint, you can't  insert the value '' ,so change it to ' '.

[ToonMariner]

you have to put a value in there ' ' may work but if you have set the field in the database to not null then your design will be expecting something tangeble - if you want null in there change your field so it will accept it.

[gavenf]

I sorry to sound like an idiot but you keep saying in there ' ' .  Sorry but whcih line.  In all of them or just one.  I dont know what you mean. What should I put in there?

 

you have to put a value in there ' ' may work but if you have set the field in the database to not null then your design will be expecting something tangeble - if you want null in there change your field so it will accept it.