[SOLVED] UpDate MySql from a form (ANOTHER PROBLEM!)

[TheStalker]

Ive had some help from some kind people on this forum before but ive come across another problem. I cant get an UPDATE to work i only want to update some of the records in the table for the time being Surname, Forename, AddressLine1, AddressLine2 and Postcode.

 

This is the code from the form that i already had help with building. This links to another page called insertEditCustomer.php once the update button is clicked. I dont get any errors with the code but nothing changes in the database? Im guessing there must be something wrong with the query some where? or the values are not being posted? any ideas would be great.

 

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');

$dbname = 'randv';
mysql_select_db($dbname);
?> 

<center>
<form action="<?php echo $_SERVER['PHP_SELF']?>" method="POST" name="names">
<table border='0' width='320'>
<tr>
<td><p>Select A CustomerID:</p></td>
<td>
<SELECT NAME='results' onchange="this.form.submit();">
<?php	


	$query = "SELECT CustomerID FROM customers"; 
	$result = mysql_query($query);

	while ($line = mysql_fetch_array($result))
	{
	 echo "<OPTION value='$line[0]'>$line[0]</OPTION>";
	}?>
	</SELECT>
</td>
</tr>

<?php 

if($value=$_POST['results']){

$result = mysql_query("SELECT * FROM customers WHERE CustomerID = '$value'");

$customerID = mysql_result($result,0,'customerID');
$surname = mysql_result($result,0,'surname');
$forename = mysql_result($result,0,'forename');
$addressLine1 = mysql_result($result,0,'addressLine1');
$addressLine2 = mysql_result($result,0,'addressLine2');
$postcode = mysql_result($result,0,'postcode');
}
?>
</form>
<form action="insertEditCustomer.php" onsubmit ="return validate_form(this)" method="post">
<tr>
<td><br></td>
<td><br></td>
</tr>
<tr> 
<td>Customer ID: </td>
<td><input type="text" name="customerID" value="<?php echo $customerID ?>" /></td>
</tr>
<tr> 
<td><p>Surname:</td>
<td><input type="text" name="surname" value="<?php echo $surname ?>" /></td>
</tr>
<tr>
<td><p>Forename:</td>
<td><input type="text" name="forename" value="<?php echo $forename?>"/></td>
</tr>
<tr>
<td><p>Adress Line 1:</td>
<td><input type="text" name="addressLine1" value="<?php echo $addressLine1?>"/></td>
</tr>
<tr>
<td><p>Adress Line 2:</td>
<td><input type="text" name="addressLine2" value="<?php echo $addressLine2?>"/></td>
</tr>
<tr>
<td><p>Postcode:</td>
<td><input type="text" name="postcode" value="<?php echo $postcode?>"/></td>
</tr>
<tr>
<td></td>
<td><br><input type="submit" value ="Update"></td></td>
</table>
</form>

 

 

 

insertEditCustomer.php

 

<?php 



// page to update and insert data into the customers table into database "randv"
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("randv", $con);
  

  

$sql=("UPDATE customers SET Surname = '$_POST[surname]', Forename = '$_POST[forename]', AddressLine1 = '$_POST[addressLine1]', 		AddressLine2 = '$_POST[addressLine2]', Postcode = '$_POST[postcode]'
WHERE Surname ='$_POST[customerID]'");


if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

mysql_close($con);

  ?>

 

[MadTechie]

change

$sql=("UPDATE customers SET Surname = '$_POST[surname]', Forename = '$_POST[forename]', AddressLine1 = '$_POST[addressLine1]', 		AddressLine2 = '$_POST[addressLine2]', Postcode = '$_POST[postcode]'
WHERE Surname ='$_POST[customerID]'");

to

$sql= "UPDATE customers SET Surname = '{$_POST['surname']}', Forename = '{$_POST['forename']}', AddressLine1 = '{$_POST['addressLine1']}', 		AddressLine2 = '{$_POST['addressLine2']}', Postcode = '{$_POST['postcode']}'
WHERE Surname ='{$_POST['customerID']}' ";
echo $sql; //debug code

it should atleast print "UPDATE customers SET Surname =" to the page.. 

if it works you can remove "echo $sql; //debug code"

 

[JD*]

Also, sometimes it's easier to get rid of the if statement and just do

 

mysql_query($sql, $con) or die("Error: ".mysql_error());

 

 

[anon_login_001]

Despite other things about this code, should this line truly read as such?:

 

WHERE Surname ='$_POST[customerID]

 

?

 

 

[JD*]

Despite other things about this code, should this line truly read as such?:

 

WHERE Surname ='$_POST[customerID]

 

?

 

 

 

To be safe, I usually structure mine as

WHERE Surname = '".$_POST['customerID']."'

that way if I have to do any extra checking (yeah, sometimes I do it inline because I'm crazy), my statement is all ready to go with the proper quotes.

[TheStalker]

Despite other things about this code, should this line truly read as such?:

 

WHERE Surname ='$_POST[customerID]

 

?

 

 

 

cant believe i didnt see that, thats all it was it works now. Think i need to take a break from looking at a computer for a few mins lol

[TheStalker]

i want to add another table for it to update, is this the right way of doing it?

 

$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }mysql_select_db("randv", $con);
  

$sql=("UPDATE customers SET Surname = '$_POST[surname]', Forename = '$_POST[forename]', AddressLine1 = '$_POST[addressLine1]', 		AddressLine2 = '$_POST[addressLine2]', Postcode = '$_POST[postcode]'
WHERE customerID ='$_POST[customerID]'");

	if (!mysql_query($sql,$con))
	{
	die('Error: ' . mysql_error());
	}
	$custId = mysql_insert_id();

	$sql=("UPDATE accounts SET Surname = '$_POST[surname]',
	WHERE customerID ='$_POST[customerID]'");


  
  

  if (!mysql_query($sql,$con))
  {
  die('Error: Could not insert account when inserting customer: ' . mysql_error());
  }
?>

[JD*]

Yes, and you can condense your code to read like the following:

 

$con = mysql_connect("localhost","root","");
if (!$con) { die('Could not connect: ' . mysql_error());}

mysql_select_db("randv", $con);

mysql_query("UPDATE customers SET Surname = '$_POST[surname]', Forename = '$_POST[forename]', AddressLine1 = '$_POST[addressLine1]', AddressLine2 = '$_POST[addressLine2]', Postcode = '$_POST[postcode]' WHERE customerID ='$_POST[customerID]'", $con) or die('Error: '.mysql_error());

$custId = mysql_insert_id();

mysql_query("UPDATE accounts SET Surname = '$_POST[surname]' WHERE customerID ='$_POST[customerID]'", $con) or die('Error: Could not insert account when inserting customer: ' . mysql_error());
?>

[TheStalker]

spot on. Thanks

[JD*]

Don't forget to mark it solved!