Altering data in mysql db table with form

[kevin66]

Hi, I am extracting data from a mysql db to then edit and update back into the db and use below to list items first -

 

<?php

// Connect to server and select database.
mysql_connect('localhost', 'xxxxxxxxx', 'xxxxxxxxxxxxx') or die("Error: ".mysql_error());
mysql_select_db("xxxxxxxxx");

$sql="SELECT * FROM properties";
$result=mysql_query($sql);
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="400" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="4"><strong>List data from mysql </strong> </td>
</tr>

<tr>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>Lastname</strong></td>
<td align="center"><strong>Email</strong></td>
<td align="center"><strong>Update</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td><? echo $rows['Property_Rating']; ?></td>



<td align="center"><a href="update.php?id=<? echo $rows['ID']; ?>">update</a></td>
</tr>
<?php
}
?>
</table>
</td>
</tr>
</table>
<?php
mysql_close();
?>

I then go next page where I show data and allow editing -
<?php

// Connect to server and select database.
mysql_connect('localhost', 'xxxxxxxxx', 'xxxxxxxxxxxxx') or die("Error: ".mysql_error());
mysql_select_db("xxxxxxxxx");

// get value of id that sent from address bar
$id=$_GET['id'];


// Retrieve data from database
$sql="SELECT * FROM properties WHERE id='$id'";
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<form name="form1" method="post" action="update_ac.php">
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td> </td>
<td colspan="3"><strong>Update data in mysql</strong> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"><strong>Property_Name</strong></td>
<td align="center"><strong>roperty_Rating</strong></td>
<td align="center"><strong>Property_Address</strong></td>
</tr>
<tr>
<td> </td>
<td align="center"><input name="Property_Rating" type="text" id="Property_Rating" value="<? echo $rows['Property_Rating']; ?>"></td>

</tr>
<tr>
<td> </td>
<td><input name="id" type="hidden" id="id" value="<? echo $rows['id']; ?>"></td>
<td align="center"><input type="submit" name="Submit" value="Submit"></td>
<td> </td>
</tr>
</table>
</td>
</form>
</tr>
</table>

<?

// close connection
mysql_close();

?>

And then the update page -

<?php

// Connect to server and select database.
mysql_connect('localhost', 'xxxxxxxxx', 'xxxxxxxxxxxxx') or die("Error: ".mysql_error());
mysql_select_db("xxxxxxxxx");

// update data in mysql database
$sql="UPDATE properties SET Property_Name='$Property_Name' WHERE id='$id'";
$result=mysql_query($sql);

// if successfully updated.
if($result){
echo "Successful";

}

else {
echo "ERROR";
}

?>

Everything seems ok but when I edit the data and submit I get the success message but data not changed in db.

 

Any ideas?

 

Thanks.

 

MOD EDIT: code tags added.

[wildteen88]

First in your second page where you display the data to be edited you are only showing one field to be edited, which is the Property_Rating.

<td align="center"> </td>
<td align="center"><strong>Property_Name</strong></td>
<td align="center"><strong>roperty_Rating</strong></td>
<td align="center"><strong>Property_Address</strong></td>
</tr>
<tr>
<td> </td>
<td align="center"><input name="Property_Rating" type="text" id="Property_Rating" value="<? echo $rows['Property_Rating']; ?>"></td> <-- the only editable field you have

</tr>

You do not have a field for Property_Name or Property_Address. However in the page you update the data in the database you are trying to update the Propety_Name field! You should add in the missing form fields.

 

In your update page the variables $Property_Name and $id are undefined. Before updating your database you should first get the data from your form. That data is stored within the $_POST superglobal. Example

$id = $_POST['id'];
$Property_Name  = $_POST['Property_Name'];
$Property_Address = $_POST['Property_Address'];

[kevin66]

Thanks for your prompt post. As you figured I am very new to php.

 

The code -

<td align="center"> </td><td align="center"><strong>Property_Name</strong></td><td align="center"><strong>roperty_Rating</strong></td><td align="center"><strong>Property_Address</strong></td></tr>

 

Was just text there as I wanted to just update Property_Rating field. I do have other fields but was just testing one. Do I have to list them all?

 

I changed the final code to -

 

<?php

// Connect to server and select database.
mysql_connect('localhost', 'xxx', 'xxxxx') or die("Error: ".mysql_error()); //add your DB username and password
mysql_select_db("xxxxx");//add your dbname

$id = $_POST['id'];
$Property_Name  = $_POST['Property_Name'];

// update data in mysql database
$sql="UPDATE properties SET Property_Name='$Property_Name' WHERE id='$id'";
$result=mysql_query($sql);

// if successfully updated.
if($result){
echo "Successful";

}

else {
echo "ERROR";
}

?>

But still no change in db on final submit.

 

Any ideas?

 

Thanks a million.

 

MOD EDIT: code tags added.

[Pikachu2000]

When posting code, enclose it within

 . . . 

BBCode tags.

[kevin66]

Sorry made a mistake my final code is -

 

 <?php

// Connect to server and select database.
mysql_connect('localhost', 'xxxxxx', 'xxxxxx') or die("Error: ".mysql_error()); 
mysql_select_db("dalyanhotels");
$id = $_POST['id'];
$Property_Rating  = $_POST['Property_Rating'];

// update data in mysql database
$sql="UPDATE properties SET Property_Rating='$Property_Rating' WHERE id='$id'";
$result=mysql_query($sql);

// if successfully updated.
if($result){
echo "Successful";

}

else {
echo "ERROR";
}

?>

[kevin66]

Figured my error I had id instead of ID. Works fine now.

 

Thanks so much.