Update not working!

[scitech]

I'm trying to pass search results to text bxes in a form, which I can then change and then update the database. I cannot see where my error is! It is when I try to update that it goes wrong

error message:- 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 '1' at line 1

<?PHP
include 'config.php';
include 'opendb.php';
//create search form, 
?>
<form method="POST" action="<?php echo $_SERVER['PHP_SELF'];?>">
Enter First Name:-<INPUT NAME="FirstName" TYPE="TEXT" id="FirstName" size="50" maxlength="50"><br>
Enter Surname:-<INPUT NAME="SurName" TYPE="TEXT" id="SurName" size="50" maxlength="50"><br>
<INPUT TYPE="SUBMIT" NAME="Search" id="SUBMIT" VALUE="Search"> 
</FORM>

<?PHP
$queryItem = array();
if(isset($_POST['Search']))
{
	if ($_POST['FirstName'])
	{
   $queryItem[] = "FirstName='".mysql_escape_string($_POST['FirstName'])."'";
	}
	if ($_POST['SurName'])
	{
   $queryItem[] = "SurName='".mysql_escape_string($_POST['SurName'])."'";
	}
	if ($_POST['Postcode'])
	{
   $queryItem[] = "Postcode='".mysql_escape_string($_POST['Postcode'])."'";
	}	
//now you create your query statement (I assume AND)
$query1 = "SELECT * FROM Addressbook WHERE ".implode(" AND ", $queryItem)  or die(mysql_error());
$result1 = mysql_query($query1) or die(mysql_error());

// get the entry from the result
	while($row = mysql_fetch_array($result1)) // Print out the contents 
		{
		$ud_ID  = $row['ID'];
		$ud_FirstName = $row['FirstName'];
		$ud_SurName = $row['SurName'];
		
		echo $row['ID']." "."<br>";
		echo $row['FirstName']." ".$row['SurName'];
		echo"<br>";
		echo $row['Address1']." ".$row['Address2']." ".$row['Address3'];
		echo"<br>";
		echo $row['Town']." ".$row['Postcode'];
		echo"<br>";
		echo "<em><strong>E-Mail:-</em></strong>"." ".$row['email'];
		echo"<br>";
		echo "<em><strong>Phone:-</em></strong>"." ".$row['Phone'];
		echo"<p></P>";
		}
echo "<br>";
}
/*The variable is passed from the array found by result and placed in the text box,
 the value to be changed and then the update passed to the database
*/
?>
<form method="POST" action="<?php echo $_SERVER['PHP_SELF'];?>">
ID Number :-<input name="ID"  value="<?PHP echo "$ud_ID";?>"><br>
First Name:-<input name="ud_FirstName" value="<?PHP echo "$ud_FirstName";?>"><br>
Surname:-<input name="ud_SurName" value="<?PHP echo "$ud_SurName";?>">
<INPUT TYPE="SUBMIT" NAME="Update" id="SUBMIT" VALUE="Update"> 	
<?PHP
if(isset($_POST['Update']))
{
		$ud_ID = $_POST['ud_ID'];
		$ud_FirstName = $_POST['ud_FirstName'];
		$ud_SurName = $_POST['ud_SurName'];

  $query2 = "UPDATE Addressbook SET FirstName ='$ud_FirstName' Where ID = '$ud_ID' ";
  $result2 = mysql_query($query2) or die(mysql_error()); 

if(mysql_query($result2))
	{
	echo("<P> The address and all Information has been added<?P>");
	}
else
	{
	echo("<P>Error adding Address information:<br>". mysql_error() ."</P>");
	}
}
?>

<?PHP
include 'close.php';
?>

 

[kenrbnsn]

What's in your query when it issues the error?

Change

$result2 = mysql_query($query2) or die(mysql_error());

to

$result2 = mysql_query($query2) or die([red]'Error with query: ' . $query2 . '<br>' . [/red]mysql_error());

That should give you a hint as to what is wrong.

Ken

 

[scitech]

Hi
Thanks for that error checking syntax,
the errors the same I have been getting in all my variations the

$query2 = "UPDATE Addressbook SET FirstName ='$ud_FirstName' Where ID = '$ud_ID' ";

the $ud_ID variable has no value, I'm confused because it has a correct value passed to the second form, so it is not being passed to the Update statement, why not?
All I am changing at the moment is the FirstName variable, I don't intend to have the ID variable changed (slowly developping)

 

[kenrbnsn]

Your ID is blank because in the form you have

ID Number :-<input name="ID"  value="<?PHP echo "$ud_ID";?>"><br>

but in your code you use

$ud_ID = $_POST['ud_ID'];

Either change the name in the form to be "ud_ID" or the index to "ID".

Ken

 

[scitech]

Hi
I don't understand,
$ud_ID is supposed to be an integer passed from $row['ID']
It displays corectly in the second form, when I change the Firstname textbox and click update, the changed variable is passed to the query so why not the unchanged $ud_ID?, the codeing is the same for both as far as i can see.
As a Thought does it know that it is an INTeger!,

 

[scitech]

Just saw what you meant and have corrected my syntax, now the $ud_ID value is being passed to the query, but it is still not working, same error message as before

 

[scitech]

Sorry about this
It does update Hurray!!!!!
But i get the "error adding address message" followed by
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 '1' at line 1

anyideas??

 

[kenrbnsn]

Your code is wrong here:

  $result2 = mysql_query($query2) or die(mysql_error());

if(mysql_query($result2)) //[red]<---[/red]
    {
    echo("<P> The address and all Information has been added<?P>");
    }
else
    {
    echo("<P>Error adding Address information:<br>". mysql_error() ."</P>");
    }

Your trying to do a query on the result of the query.

Try

  $result2 = mysql_query($query2) or die(mysql_error());
  echo 'Query: ' . $query2 . ' executed.<br>';
if($result2)
    {
    echo('<P> The address and all Information have been added</P>');
    }
else
    {
    echo('<P>Error adding Address information:<br>'. mysql_error() .'</P>');
    }

Ken

 

[scitech]

Thanks Ken
I saw it eventually
Do you have any tips for syntax error checking?
Like that last one I had been looking for half an hour before i saw it
charlie