Hi all, pretty new to sql. have made code already for showing, adding and deleting data from database but can't seem to make the update work. any help is much appreciated.

here's what I have:

<html><body><form action="update.php" method="post">
<label>Person ID:<br></label>
<input type="text" name="personID" size="40" value="" maxlength="150" />
<label>Customer Name:<br></label>
<input type="text" name="customer_name" size="40" value="" maxlength="150" />
<br /><label>Billing Address:</label><input type="text" name="billing_address" value=""><br />
<label>Billing Suburb:</label><input type="text" name="billing_suburb" value=""><br />
<label>Billing City:</label><input type="text" name="billing_city" value=""><br />
<label>Same as:</label><input type="text" name="same_as" value=""><br />
<label>Shipping Address:</label><input type="text" name="shipping_address" value=""><br />
<label>Shipping Suburb:</label><input type="text" name="shipping_suburb" value=""><br />
<label>Shipping City:</label><input type="text" name="shipping_city" value=""><br />


<label>Which District is the Job located in?:</label>
<select name="district">
  <option selected value=""></option>
  <option value="North Shore">North Shore ( NORTH )</option>
  <option value="Waitakere">Waitakere ( WEST )</option>
  <option value="Auckland City">Auckland City ( CENTRAL )</option>
  <option value="Manukau">Manukau ( SOUTH )</option>
  <option value="Rodney">Rodney ( NORTH )</option>
  </select><br />
<label>Mobile Phone:</label>
<input type="text" size="40" name="mobile_phone" value="" maxlength="150" />
<br />
<label>Work Phone:</label>
<input type="text" size="40" name="work_phone" value="" maxlength="150" />

<br />
<label>Home Phone:</label>
<input type="text" size="40" name="home_phone" value="" maxlength="150" />
<br />
<label>Email Address:</label>
<input type="text" size="40"name="email_address" value="" maxlength="150" />
<br />
<label>What time is best to contact you?:</label>
<select name="time">
  <option value=""></option>
  <option value="Anytime">Anytime</option>
  <option value="Morning">Morning</option>
  <option value="Lunch">Lunch</option>
  <option value="Afternoon">Afternoon</option>
  <option value="Evening">Evening</option>
  
</select>
<br />
<label>What type of Service do you require?:</label>
<select name="services">
  <option value=""></option>
  <option value="Cleaning">Cleaning</option>
  <option value="Repairing">Repairing</option>
  <option value="Supplying and Installing">Supplying and Installing</option>
  <option value="Various services">Various services</option>
<option value="Find and Resolve leak in building">Find and Resolve leak in building</option>
</select>
<br />
<label>Desired Product?:(If known)</label>
<select name="products">
  <option value=""></option>
  <option value="Colorsteel">Colorsteel</option>
  <option value="PVC Marley">PVC Marley ( Plastic )</option>
  <option value="Galvanised">Galvanised</option>
  <option value="Copper">Copper</option>
  <option value="Continuous">Continuous</option>
  <option value="Brass">Brass</option>
  <option value="Stainless Steel">Stainless Steel</option>
  <option value="Concrete Tiles">Concrete Tiles</option>
  <option value="Duralume">Duralume</option>
  <option value="Steel">Steel</option>
  <option value="Price Driven">Price Driven</option>
  <option value="Not Sure">Not Sure</option>
  <option value="Advise me">Advise me</option>
</select>
<br />
<label>Quote Requests / Job Details:</label>
<textarea rows="10" cols="45" name="comments"></textarea>
<br />
<label>Do you require a Full Replacement of your spouting?:</label>
<select name="respout">
  <option value="Yes">Yes</option>
  <option value="No">No</option>
  <option value="I would like a price please">I would like a price please</option>
</select>
<br />
<label>Is the building 1,2 or multiple levels?:</label>
<select name="level">
  <option value="1 Level">1 Level</option>
  <option value="2 Level">2 Level</option>
  <option value="Multiple levels">Multiple levels</option>
</select>
<br />
<label>How did you find out about us?:</label>
<input type="text" name="lead" size="40" value="" maxlength="150" />
<label>
<input type="submit" value="Submit" />
<input type="reset" value="Clear form">
</form>
<?php
// Load form field data into variables.
$today = date("F j, Y, g:i a"); 
$customer_name = $_REQUEST['customer_name'] ;
$billing_address = $_REQUEST['billing_address'] ;
$billing_suburb = $_REQUEST['billing_suburb'] ;
$billing_city = $_REQUEST['billing_city'] ;
$same_as = $_REQUEST['same_as'] ;
$shipping_address = $_REQUEST['shipping_address'] ;
$shipping_suburb = $_REQUEST['shipping_suburb'] ;
$shipping_city = $_REQUEST['shipping_city'] ;
$ip = $_SERVER["REMOTE_ADDR"];
$district = $_REQUEST['district'] ;
$mobile_phone = $_REQUEST['mobile_phone'] ;
$work_phone = $_REQUEST['work_phone'] ;
$home_phone = $_REQUEST['home_phone'] ;
$email_address = $_REQUEST['email_address'] ;
$time = $_REQUEST['time'] ;
$services = $_REQUEST['services'] ;
$products = $_REQUEST['products'] ;
$comments = $_REQUEST['comments'] ;
$respout = $_REQUEST['respout'] ;
$level = $_REQUEST['level'] ;
$lead = $_REQUEST['lead'] ;

$con = mysql_connect("host","user","pw");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("database", $con);
$sql= "UPDATE my_db SET date_in=\"$today\",customer_name=\"$customer_name\",billing_address=\"$billing_address\",billing_suburb=\"$billing_suburb\",billing_city=\"$billing_city\",same_as=\"$same_as\",shipping_address= \"$shipping_address\", shipping_suburb=\"$shipping_suburb\",shipping_city=\"$shipping_city\" WHERE personID = \"$personID\" ";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
mysql_close($con);
?>

oh and whats happening is the form comes up, i fill it out and then click submit but it doesn't up date the record selected which is "personID" . I tried so many things and have only just got it back to a state where the form loads without displaying any error messages. all help is appreciated.

Just run the query in phpmyadin and check whether it is working or not:-
and enter the values inplace of variables..
And if it is working ,then just echo the value of $personID because i can't see it any where ,where any value is assigned to it.....
You have not assigned any value to $personID ,so how will it work....
Check it...

hey yep mate it worked when i declared the variable as you said. but only updates up to the end of shipping. ip through to lead doesnt update? I'm glad Im getting somewhere

Just check each variable by echoing its value.....and also check running the quey in phpmyadmin...you will definitely find the error...
Just might be some syntax or spelling error....

i made these changes, but still no luck. $select is now the variable for the where clause. note the top input name has also changed to select to give variable. Did work, and now doesn't again for some reason. no error messages, but doesnt update like its meant to yet. would it be better to have the delivery details in a seperate table? although then I would have challenge of selecting the two corresponding records eg #32 out of both tables. could that be an easier way? Id rather have the data in one table if possible

<code><html><body><form action="update.php" method="post">
<label>Person ID:<br></label>
<input type="text" name="select" size="40" value="" maxlength="150" />
<label>Customer Name:<br></label>
<input type="text" name="customer_name" size="40" value="" maxlength="150" />
<br /><label>Billing Address:</label><input type="text" name="billing_address" value=""><br />
<label>Billing Suburb:</label><input type="text" name="billing_suburb" value=""><br />
<label>Billing City:</label><input type="text" name="billing_city" value=""><br />
<label>Same as:</label><input type="text" name="same_as" value=""><br />
<label>Shipping Address:</label><input type="text" name="shipping_address" value=""><br />
<label>Shipping Suburb:</label><input type="text" name="shipping_suburb" value=""><br />
<label>Shipping City:</label><input type="text" name="shipping_city" value=""><br />


<label>Which District is the Job located in?:</label>
<select name="district">
<option selected value=""></option>
<option value="North Shore">North Shore ( NORTH )</option>
<option value="Waitakere">Waitakere ( WEST )</option>
<option value="Auckland City">Auckland City ( CENTRAL )</option>
<option value="Manukau">Manukau ( SOUTH )</option>
<option value="Rodney">Rodney ( NORTH )</option>
</select><br />
<label>Mobile Phone:</label>
<input type="text" size="40" name="mobile_phone" value="" maxlength="150" />
<br />
<label>Work Phone:</label>
<input type="text" size="40" name="work_phone" value="" maxlength="150" />

<br />
<label>Home Phone:</label>
<input type="text" size="40" name="home_phone" value="" maxlength="150" />
<br />
<label>Email Address:</label>
<input type="text" size="40"name="email_address" value="" maxlength="150" />
<br />
<label>What time is best to contact you?:</label>
<select name="time">
<option value=""></option>
<option value="Anytime">Anytime</option>
<option value="Morning">Morning</option>
<option value="Lunch">Lunch</option>
<option value="Afternoon">Afternoon</option>
<option value="Evening">Evening</option>

</select>
<br />
<label>What type of Service do you require?:</label>
<select name="services">
<option value=""></option>
<option value="Cleaning">Cleaning</option>
<option value="Repairing">Repairing</option>
<option value="Supplying and Installing">Supplying and Installing</option>
<option value="Various services">Various services</option>
<option value="Find and Resolve leak in building">Find and Resolve leak in building</option>
</select>
<br />
<label>Desired Product?:(If known)</label>
<select name="products">
<option value=""></option>
<option value="Colorsteel">Colorsteel</option>
<option value="PVC Marley">PVC Marley ( Plastic )</option>
<option value="Galvanised">Galvanised</option>
<option value="Copper">Copper</option>
<option value="Continuous">Continuous</option>
<option value="Brass">Brass</option>
<option value="Stainless Steel">Stainless Steel</option>
<option value="Concrete Tiles">Concrete Tiles</option>
<option value="Duralume">Duralume</option>
<option value="Steel">Steel</option>
<option value="Price Driven">Price Driven</option>
<option value="Not Sure">Not Sure</option>
<option value="Advise me">Advise me</option>
</select>
<br />
<label>Quote Requests / Job Details:</label>
<textarea rows="10" cols="45" name="comments"></textarea>
<br />
<label>Do you require a Full Replacement of your spouting?:</label>
<select name="respout">
<option value="Yes">Yes</option>
<option value="No">No</option>
<option value="I would like a price please">I would like a price please</option>
</select>
<br />
<label>Is the building 1,2 or multiple levels?:</label>
<select name="level">
<option value="1 Level">1 Level</option>
<option value="2 Level">2 Level</option>
<option value="Multiple levels">Multiple levels</option>
</select>
<br />
<label>How did you find out about us?:</label>
<input type="text" name="lead" size="40" value="" maxlength="150" />
<label>
<input type="submit" value="Submit" />
<input type="reset" value="Clear form">
</form>
<?php
// Load form field data into variables.
$today = date("F j, Y, g:i a");
$select = $_REQUEST ;
$customer_name = $_REQUEST ;
$billing_address = $_REQUEST ;
$billing_suburb = $_REQUEST ;
$billing_city = $_REQUEST ;
$same_as = $_REQUEST ;
$shipping_address = $_REQUEST ;
$shipping_suburb = $_REQUEST ;
$shipping_city = $_REQUEST ;
$ip = $_SERVER["REMOTE_ADDR"];
$district = $_REQUEST ;
$mobile_phone = $_REQUEST ;
$work_phone = $_REQUEST ;
$home_phone = $_REQUEST ;
$email_address = $_REQUEST ;
$time = $_REQUEST ;
$services = $_REQUEST ;
$products = $_REQUEST ;
$comments = $_REQUEST ;
$respout = $_REQUEST ;
$level = $_REQUEST ;
$lead = $_REQUEST ;

$con = mysql_connect("host","user","pw");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("database", $con);
$sql = 'UPDATE my_db SET date_in="$today",customer_name="$customer_name",billing_address="$billing_address",billing_suburb="$billing_suburb",billing_city="$billing_city",same_as="$same_as",shipping_address= "$shipping_address", shipping_suburb="$shipping_suburb",shipping_city="$shipping_city" WHERE personID = "$select"' ;

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
mysql_close($con);
?></code>

oops that code tag isnt in there, i mucked up when entering it into the forum

Are you escaping the special characters in IP before attempting to update the value?

The page is cutting off my view of your update statement so I can't see what you're doing there....

You've not execution query.

$query = mysql_query($sql);
if(!$query){
   die('Update failed!. Error:' . mysql_error());
}

This one is more clear format.

$sql = "UPDATE `my_db` SET `date_in` = '$today', `customer_name` ="$customer_name", `billing_address` = '$billing_address', `billing_suburb` = '$billing_suburb', `billing_city` = '$billing_city', `same_as` ='$same_as', `shipping_address` = '$shipping_address', `shipping_suburb` = '$shipping_suburb', `shipping_city` = '$shipping_city' WHERE `personID` = '$select'";

Hope this help.

@zer13-you did an error in writing query:-
instead f this

$sql = "UPDATE `my_db` SET `date_in` = '$today', `customer_name` ="$customer_name", `billing_address` = '$billing_address', `billing_suburb` = '$billing_suburb', `billing_city` = '$billing_city', `same_as` ='$same_as', `shipping_address` = '$shipping_address', `shipping_suburb` = '$shipping_suburb', `shipping_city` = '$shipping_city' WHERE `personID` = '$select'";

use this

$sql = "UPDATE `my_db` SET `date_in` = '$today', `customer_name` ='$customer_name', `billing_address` = '$billing_address', `billing_suburb` = '$billing_suburb', `billing_city` = '$billing_city', `same_as` ='$same_as', `shipping_address` = '$shipping_address', `shipping_suburb` = '$shipping_suburb', `shipping_city` = '$shipping_city' WHERE `personID` = '$select'";
commented: Good spot +7

Well @IIM, thanks for spotting the errors.
@gotboots, use the query string as shown by @IIM. And put the 'mysql_query()' funtion.

$query = mysql_query($sql);
if(!$query){
   die('Update failed!. Error:' . mysql_error());
}

yay works now. can update every field by specifying the person id in first box. choice guys thanks alot, is there a way to only update fields that the user wants to without deleting all the other info? eg if i need to update say only the address details, how would i do it without deleting exisitng data. I think it needs a null thing, not too sure what null is though yet. The existing code is at a functional level, I will put this working update up model soon

echo $sql //check personID value !empty, If PersonId not empty but still dont get the process, do ZER13 suggestion

happy together helped solve this, by echoing the $sql statement. it also didnt help that i didnt have the personID in the form itself. thanks all. just follow the first original code but add a personid text box and declare the variable. sorted. thanks team

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.