[SOLVED] script not updating mysql database - php + mysql

agallo · March 6, 2007

I have recently posted about a broader issue but now I have fixed part of the big issue. I am still having some trouble understanding why my script is not updating my database. If anyone can help I really do appreciate it.

I filled in the Id and username variables with text for debugging purposes. The 'id' field in my database is set to auto increment and the 'username' is pulled from the user's login name.

Thanks in advance for any help!

If the entire code of the edit.php I have created is needed please let me know and I will post quickly.


<?
   if ($_POST["$submit"])
   {
$id="test";
$username = "test";
$address_no=$_POST['address_no'];
$address_name=$_POST['address_name'];
$address_suite=$_POST['address_suite']; 
$sq_ft=$_POST['sq_ft']; 
$rent_price=$_POST['rent_price']; 
$bedroom=$_POST['bedroom']; 
$bath=$_POST['bath']; 
$living_room=$_POST['living_room']; 
$dining_room=$_POST['dining_room']; 
$furnished=$_POST['furnished']; 
$pets=$_POST['pets']; 
$pet_deposit=$_POST['pet_deposit']; 
$lease_term=$_POST['lease_term']; 
$security_deposit=$_POST['security_deposit']; 
$key_access=$_POST['key_access']; 
$resident_phone=$_POST['resident_phone']; 
$date_available=$_POST['date_available']; 
$ac=$_POST['ac']; 
$heat=$_POST['heat']; 
$laundry=$_POST['laundry']; 
$fireplace=$_POST['fireplace']; 
$alarm=$_POST['alarm']; 
$ceiling_fan=$_POST['ceiling_fan']; 
$refrigerator=$_POST['refrigerator']; 
$stove=$_POST['stove']; 
$dishwasher=$_POST['dishwasher'];
$microwave=$_POST['microwave']; 
$parking=$_POST['parking'];
$fenced=$_POST['fenced']; 
$pool=$_POST['pool'];
$spa=$_POST['spa']; 
$bills_paid=$_POST['bills_paid'];
$school_district=$_POST['school_district']; 
$school_elem=$_POST['school_elem'];
$school_midd=$_POST['school_midd'];
$school_high=$_POST['school_high'];
$company_name=$_POST['company_name'];
$company_phone=$_POST['company_phone'];
$agent_fname=$_POST['agent_fname'];
$agent_lname=$_POST['agent_lname'];
$agent_phone=$_POST['agent_phone'];
$commission=$_POST['commission'];
$bonus=$_POST['bonus'];
$remarks=$_POST['remarks'];

      $sql = "UPDATE property SET id='$id',username='$username',address_no='$address_no',address_name='$address_name',sq_ft='$sq_ft',rent_price='$rent_price',bedroom='$bedroom',bath='$bath',living_room='$living_room',dining_room='$dining_room',furnished='$furnished',pets='$pets',pet_deposit='$pet_deposit',lease_term='$lease_term',security_deposit='$security_deposit',key_access='$key_access',resident_phone='$resident_phone',date_available='$date_available',ac='$ac',heat='$heat', laundry='$laundry', fireplace='$fireplace',alarm='$alarm',ceiling_fan='$ceiling_fan',refrigerator='$refrigerator',stove='$stove',dishwasher='$dishwasher',microwave='$microwave',parking='$parking',fenced='$fenced',pool='$pool',spa='$spa',bills_paid='$bills_paid',school_district='$school_district',school_elem='$school_elem',school_midd='$school_midd',school_high='$school_high',company_name='$company_name',company_phone='$company_phone',agent_fname='$agent_fname',agent_lname='$agent_lname',agent_phone='$agent_phone',commission='$commission',bonus='$bonus',remarks='$remarks' WHERE address_no=$address_no";

      $result = mysql_query($sql);
      echo "Thank you! Information updated.";
   }
}
?>

pocobueno1388 · March 6, 2007

Let it print out the error:

$result = mysql_query($sql)or die(mysql_error());

trq · March 6, 2007

Post the results of....

$result = mysql_query($sql) or die(mysql_error()."<br /><br />".$sql);

interpim · March 6, 2007

will assigning a value to an auto incrementing value mess you up?

agallo · March 6, 2007

Not good, I do not receive any output by addings this

Any ideas?

willpower · March 6, 2007

In your WHERE statement you have no ' ' around the $address_no

As a result it is failing to find a matching row, but not hitting an error.

so it shoudl read WHERE address_no='$address_no'

trq · March 6, 2007

If the id field is set to autoincrement dont try and insert "test" into it. In fact, as stated, there is no need to try and update it at all.

willpower · March 6, 2007

are you updating or inserting? Thorpe is right...you need not and indeed cant insert a value like that into an auto_increment column if inserting and updating...well then you should only require to update the NEW variables

agallo · March 6, 2007

I changed $id="test" to $id=$_POST['id'];

Also, thanks for pointing out the ' ' missing around the address_no.

So, the database is auto incrementing the id, should I leave those two fields from being updated? If so, how would i approach this?


$sql = "UPDATE property SET id='$id',username='$username',address_no='$address_no', etc.

changed to 

$sql = "UPDATE property SET , ,address_no='$address_no', etc.

??

Just an idea, I am not sure.

Thanks for the help!

willpower · March 6, 2007

first. Is this a NEW record that you are INSERTING or an OLD record that your UPDATING

agallo · March 6, 2007

an old record i am updating.. sorry for leaving that out

willpower · March 6, 2007

then just set the new values...in other words only those that have changed. ID is staying the same right...so

$sql = "UPDATE property SET address_no='$address_no', etc.

in the case where the id and username are staying the same. if the username is changing simply stick it back in.

The point is that you only have to include the fields you want in the UPDAT statement...you dont have to include them all

agallo · March 6, 2007

Say I am not sure of the fields that will be updated, but potientially all except the id and username. Would my update statement work in this case?

I have attached below the entire edit.php file I have updated with all insights to this post.

Again thanks for the help! I really appreciate it. I just don't know where to go from here and have spent an entire day debugging this issue.

I am positive the database connection is working because the address number and id are being queried initially as I can see them before the edit cmd is invoked.


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<h1>Below are the following listings you may edit: </br></h1>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>


<? 


include_once ("../auth.php");
include_once ("../authconfig.php");
include_once ("../check.php");

$username = $check["uname"];


//If cmd has not been initialized
if(!isset($cmd)) 
{
   //display all the property
   $result = mysql_query("select * from property order by id"); 
   
   //run the while loop that grabs all the property scripts
   while($r=mysql_fetch_array($result)) 
   { 
      //grab the address_no and the ID of the property
      $address_no=$r["address_no"];//take out the address_no
      $id=$r["id"];//take out the id
     
 //make the address_no a link
      echo "<a href='edit.php?cmd=edit&id=$id'>$address_no - Edit</a>";
      echo "<br>";
    }
}
?>

<?
if($_GET["cmd"]=="edit" || $_POST["cmd"]=="edit")
{
   if (!isset($_POST["submit"]))
   {
      $id = $_GET["id"];
      $sql = "SELECT * FROM property WHERE id=$id";
      $result = mysql_query($sql);        
      $myrow = mysql_fetch_array($result);
      ?>
  
      <form action="edit.php" method="post">
      <input type=hidden name="id" value="<?php echo $myrow["id"] ?>">
   
      Address # : <input type=haddress_noden name="address_no" value="<?php echo $myrow["address_no"] ?>">
      Address Suite : <INPUT TYPE="TEXT" NAME="address_suite" VALUE="<?php echo $myrow["address_name"] ?>" ><br>
  Address Name : <INPUT TYPE="TEXT" NAME="address_name" VALUE="<?php echo $myrow["address_suite"] ?>" ><br>
  Sq ft : <INPUT TYPE="TEXT" NAME="sq_ft" VALUE="<?php echo $myrow["sq_ft"] ?>" ><br>
  Rent Price : <INPUT TYPE="TEXT" NAME="rent_price" VALUE="<?php echo $myrow["rent_price"] ?>" ><br>
  Bedrooms : <INPUT TYPE="TEXT" NAME="bedroom" VALUE="<?php echo $myrow["bedroom"] ?>" ><br>
  Baths : <INPUT TYPE="TEXT" NAME="bath" VALUE="<?php echo $myrow["bath"] ?>" ><br>
  Living Rooms : <INPUT TYPE="TEXT" NAME="living_room" VALUE="<?php echo $myrow["living_room"] ?>" ><br>
  Dining Rooms : <INPUT TYPE="TEXT" NAME="dining_room" VALUE="<?php echo $myrow["dining_room"] ?>" ><br>
  Furnished : <INPUT TYPE="TEXT" NAME="furnished" VALUE="<?php echo $myrow["furnished"] ?>" ><br>
  Pets : <INPUT TYPE="TEXT" NAME="pets" VALUE="<?php echo $myrow["pets"] ?>" ><br>
  Pet Deposit : <INPUT TYPE="TEXT" NAME="pet_deposit" VALUE="<?php echo $myrow["pet_deposit"] ?>" ><br>
  Lease Term : <INPUT TYPE="TEXT" NAME="lease_term" VALUE="<?php echo $myrow["lease_term"] ?>" ><br>
  Security Deposit : <INPUT TYPE="TEXT" NAME="security_deposit" VALUE="<?php echo $myrow["security_deposit"] ?>" ><br>
  Key Access : <INPUT TYPE="TEXT" NAME="key_access" VALUE="<?php echo $myrow["key_access"] ?>" ><br>
  Resident Phone : <INPUT TYPE="TEXT" NAME="resident_phone" VALUE="<?php echo $myrow["resident_phone"] ?>" ><br>
  Date Available : <INPUT TYPE="TEXT" NAME="date_available" VALUE="<?php echo $myrow["date_available"] ?>" ><br>
  A/C : <INPUT TYPE="TEXT" NAME="ac" VALUE="<?php echo $myrow["ac"] ?>" ><br>
  Heat : <INPUT TYPE="TEXT" NAME="heat" VALUE="<?php echo $myrow["heat"] ?>" ><br>
  Laundry : <INPUT TYPE="TEXT" NAME="laundry" VALUE="<?php echo $myrow["laundry"] ?>" ><br>
  Fireplace : <INPUT TYPE="TEXT" NAME="fireplace" VALUE="<?php echo $myrow["fireplace"] ?>" ><br>
  Alarm : <INPUT TYPE="TEXT" NAME="alarm" VALUE="<?php echo $myrow["alarm"] ?>" ><br>
  Ceiling Fan : <INPUT TYPE="TEXT" NAME="ceiling_fan" VALUE="<?php echo $myrow["ceiling_fan"] ?>" ><br>
  Refrigerator : <INPUT TYPE="TEXT" NAME="refrigerator" VALUE="<?php echo $myrow["refrigerator"] ?>" ><br>
  Stove : <INPUT TYPE="TEXT" NAME="stove" VALUE="<?php echo $myrow["stove"] ?>" ><br>
  Dishwasher : <INPUT TYPE="TEXT" NAME="dishwasher" VALUE="<?php echo $myrow["dishwasher"] ?>" ><br>
  Mircowave : <INPUT TYPE="TEXT" NAME="microwave" VALUE="<?php echo $myrow["microwave"] ?>" ><br>
  Parking : <INPUT TYPE="TEXT" NAME="parking" VALUE="<?php echo $myrow["parking"] ?>" ><br>
  Fenced : <INPUT TYPE="TEXT" NAME="fenced" VALUE="<?php echo $myrow["fenced"] ?>" ><br>
  Pool : <INPUT TYPE="TEXT" NAME="pool" VALUE="<?php echo $myrow["pool"] ?>" ><br>
  Spa : <INPUT TYPE="TEXT" NAME="spa" VALUE="<?php echo $myrow["spa"] ?>" ><br>
  Bills Paid : <INPUT TYPE="TEXT" NAME="bills_paid" VALUE="<?php echo $myrow["bills_paid"] ?>" ><br>
  School District : <INPUT TYPE="TEXT" NAME="school_district" VALUE="<?php echo $myrow["school_district"] ?>" ><br>
  Elem School : <INPUT TYPE="TEXT" NAME="school_elem" VALUE="<?php echo $myrow["school_elem"] ?>" ><br>
  Midd School : <INPUT TYPE="TEXT" NAME="school_midd" VALUE="<?php echo $myrow["school_midd"] ?>" ><br>
  High School : <INPUT TYPE="TEXT" NAME="school_high" VALUE="<?php echo $myrow["school_high"] ?>" ><br>
  Company Name : <INPUT TYPE="TEXT" NAME="company_name" VALUE="<?php echo $myrow["company_name"] ?>" ><br>
  Company Phone : <INPUT TYPE="TEXT" NAME="company_phone" VALUE="<?php echo $myrow["company_phone"] ?>" ><br>
  Agent First Name : <INPUT TYPE="TEXT" NAME="agent_fname" VALUE="<?php echo $myrow["agent_fname"] ?>" ><br>
  Agent Last Name : <INPUT TYPE="TEXT" NAME="agent_lname" VALUE="<?php echo $myrow["agent_lname"] ?>" ><br>
  Agent Phone : <INPUT TYPE="TEXT" NAME="agent_phone" VALUE="<?php echo $myrow["agent_phone"] ?>" ><br>
  Commission : <INPUT TYPE="TEXT" NAME="commission" VALUE="<?php echo $myrow["commission"] ?>" ><br>
  Bonus : <INPUT TYPE="TEXT" NAME="bonus" VALUE="<?php echo $myrow["bonus"] ?>" ><br>
  Remarks:<TEXTAREA NAME="message" ROWS=10 COLS=30><? echo $myrow["remarks"] ?></TEXTAREA><br>
   
      <input type="hidden" name="cmd" value="edit">
   
      <input type="submit" name="submit" value="submit">
   
      </form>
   
   <? } ?>

<?
   if ($_POST["$submit"])
   {

$address_no=$_POST['address_no'];
$address_name=$_POST['address_name'];
$address_suite=$_POST['address_suite']; 
$sq_ft=$_POST['sq_ft']; 
$rent_price=$_POST['rent_price']; 
$bedroom=$_POST['bedroom']; 
$bath=$_POST['bath']; 
$living_room=$_POST['living_room']; 
$dining_room=$_POST['dining_room']; 
$furnished=$_POST['furnished']; 
$pets=$_POST['pets']; 
$pet_deposit=$_POST['pet_deposit']; 
$lease_term=$_POST['lease_term']; 
$security_deposit=$_POST['security_deposit']; 
$key_access=$_POST['key_access']; 
$resident_phone=$_POST['resident_phone']; 
$date_available=$_POST['date_available']; 
$ac=$_POST['ac']; 
$heat=$_POST['heat']; 
$laundry=$_POST['laundry']; 
$fireplace=$_POST['fireplace']; 
$alarm=$_POST['alarm']; 
$ceiling_fan=$_POST['ceiling_fan']; 
$refrigerator=$_POST['refrigerator']; 
$stove=$_POST['stove']; 
$dishwasher=$_POST['dishwasher'];
$microwave=$_POST['microwave']; 
$parking=$_POST['parking'];
$fenced=$_POST['fenced']; 
$pool=$_POST['pool'];
$spa=$_POST['spa']; 
$bills_paid=$_POST['bills_paid'];
$school_district=$_POST['school_district']; 
$school_elem=$_POST['school_elem'];
$school_midd=$_POST['school_midd'];
$school_high=$_POST['school_high'];
$company_name=$_POST['company_name'];
$company_phone=$_POST['company_phone'];
$agent_fname=$_POST['agent_fname'];
$agent_lname=$_POST['agent_lname'];
$agent_phone=$_POST['agent_phone'];
$commission=$_POST['commission'];
$bonus=$_POST['bonus'];
$remarks=$_POST['remarks'];

      $sql = "UPDATE property SET address_no='$address_no',address_suite='$address_suite',address_name='$address_name',sq_ft='$sq_ft',rent_price='$rent_price',bedroom='$bedroom',bath='$bath',living_room='$living_room',dining_room='$dining_room',furnished='$furnished',pets='$pets',pet_deposit='$pet_deposit',lease_term='$lease_term',security_deposit='$security_deposit',key_access='$key_access',resident_phone='$resident_phone',date_available='$date_available',ac='$ac',heat='$heat', laundry='$laundry', fireplace='$fireplace',alarm='$alarm',ceiling_fan='$ceiling_fan',refrigerator='$refrigerator',stove='$stove',dishwasher='$dishwasher',microwave='$microwave',parking='$parking',fenced='$fenced',pool='$pool',spa='$spa',bills_paid='$bills_paid',school_district='$school_district',school_elem='$school_elem',school_midd='$school_midd',school_high='$school_high',company_name='$company_name',company_phone='$company_phone',agent_fname='$agent_fname',agent_lname='$agent_lname',agent_phone='$agent_phone',commission='$commission',bonus='$bonus',remarks='$remarks' WHERE address_no='$address_no'";

      $result = mysql_query($sql) or die(mysql_error()."<br /><br />".$sql);

      echo "Thank you! Information updated.";
   }
}
?>

</body>
</html>

willpower · March 6, 2007

I take it then that it still DOESNT work.

then I cant say for sure.

try echoing out ALL the variables to screen first and check that they are as you'd want. in the html there are some type= without the " " after them....but dont know if that would make the diff.

You can add ALL but the id into your UPDATE if you like. but you CANNOT add the id as we discussed previously

komquat · March 6, 2007

What I would do is add

right under the other hidden value.

then instead of

if ($_POST["$submit"])

use

if ($_POST['update_info'] == "yes")

I have used this and works for me.

agallo · March 6, 2007

I see where you are going with this method and I think it is a good idea

but, I still have no luck with it though

komquat · March 6, 2007

if (!isset($_POST["submit"]))

Try changing the above to this:

if ($_POST['update_info'] != "yes")

redarrow · March 6, 2007

your post needs upper case POST in the form and all your update `need` this ok.

agallo · March 7, 2007

komquat.. thanks for the tip but no luck.

The mysql update is processing now but not updating with the new form fields. Could it be that the variables used for the mysql update are coming from the values pulled initially in the form?

Thanks

agallo · March 7, 2007

Still not having any luck with this script and I have tried everything I know. Help is greatly appreciated at this point.

The script I tried to follow in my code can be found at the following url.

http://www.spoono.com/php/tutorials/tutorial.php?id=23

agallo · March 7, 2007

ok so my debugging always involved changing the addrerss_no field, since it was first.

well the problem was I always chaged the address_no field with every debug attempt but in my mysql update statement I used, WHERE address_no='$address_no'. So everytime I changed the address_no field it couldn't reference the correct field to change. tis why no rows were ever effected.

I tried the typical "or die" and that never triggered anything since no rows were affected and the update went through.

This is what worked:


<?php 
ini_set('display_errors', 1); 
error_reporting(E_ALL); 


$result = mysql_query($sql) or die(mysql_error()."<br /><br />".$sql); 
if(mysql_affected_rows() == 0) 
{ 
   user_error("DEBUG: No rows updated. Query: $sql", E_USER_WARNING); 
} 


?

Sign In

[SOLVED] script not updating mysql database - php + mysql

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information