Help needed to UPDATE Data to MYSQL

Danielinpoland

I am getting a succesful message but the datbase doesn't udpate. Any suggestions?

<head> 
<!--<meta http-equiv="refresh" content="2; URL=retrieve.php"> -->
</head> 
<?php 

$host="localhost";
$username="xxxxx";
$password="xxxx";
$database="xxx";
$table="xxx"; 

// Make a MySQL Connection 
mysql_connect("$host","$username","$password") or die(mysql_error()); 
mysql_select_db("$database") or die(mysql_error()); 

$fname= mysql_real_escape_string($_POST['fname']); 
$lname= mysql_real_escape_string($_POST['lname']); 
$address= mysql_real_escape_string($_POST['address']); 
$month_birth= mysql_real_escape_string($_POST['month_birth']); 
$mobile= mysql_real_escape_string($_POST['mobile']);
$email=mysql_real_escape_string($_POST['email']);
$subscribe=mysql_real_escape_string($_POST['subscribe']);
$welcome=mysql_real_escape_string($_POST['welcome']);

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

// update data in mysql database 
$sql="UPDATE $table SET fname='$fname', lname='$lname', month_birth='$month_birth', mobile='$mobile',  email='$email', subscribe='$subscribe', welcome='$welcome'  WHERE id='$id'"; 
$res=mysql_query($sql) or die ("Error: ". mysql_error(). " with query ". $sql); 
echo "Record Successfully Updated"; 

?>

jorgepinho

check if there are any updated records with mysql_affected_rows()

If you send the same values, MySQL does not change, but there is no error.

Danielinpoland

This is the script I use to get the info to display. Then I change it and run the previous script.
It says updated but it doesn't actually change the database.
Am I missing something between the 2 scripts?

<?php

$host="localhost"; // Host name 
$username="xxx"; // Mysql username 
$password="xxx"; // Mysql password 
$db_name="xxx"; // Database name 
$tbl_name="xxx"; // Table name 


// Make a MySQL Connection
mysql_connect("localhost", "infernoc_daniel", "revesby2212") or die(mysql_error());
mysql_select_db("infernoc_customers") or die(mysql_error());

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


// Retrieve data from database 
$sql="SELECT * FROM $tbl_name 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.php">
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>&nbsp;</td>
<td colspan="3"><strong>Update data in mysql</strong> </td>
</tr>
<tr>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
</tr>
<tr>
<td align="center">&nbsp;</td>
<td align="center"><strong>First Name</strong></td>
<td align="center"><strong>Last Name</strong></td>
<td align="center"><strong>Month Of Birth</strong></td>
<td align="center"><strong>Mobile</strong></td>
<td align="center"><strong>Email</strong></td>
<td align="center"><strong>Subscribe</strong></td>
<td align="center"><strong>Welcome</strong></td>
</tr>
<tr>
<td>&nbsp;</td>
<td align="center"><input name="fname" type="text" id="fname" value="<? echo $rows['fname']; ?>"></td>
<td align="center"><input name="lname" type="text" id="lname" value="<? echo $rows['lname']; ?>" size="15"></td>
<td align="center"><input name="month_birth" type="text" id="month_birth" value="<? echo $rows['month_birth']; ?>" size="15"></td>
<td align="center"><input name="mobile" type="text" id="mobile" value="<? echo $rows['mobile']; ?>" size="15"></td>
<td><input name="email" type="text" id="email" value="<? echo $rows['email']; ?>" size="30"></td>
<td><input name="subscribe" type="text" id="subscribe" value="<? echo $rows['subscribe']? 'Yes' : 'No'; ?>"size="7"></td>
<td><input name="welcome" type="text" id="welcome" value="<? echo $rows['welcome']? 'Yes' : 'No'; ?>" size="7"></td>
<tr>
<td>&nbsp;</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>&nbsp;</td>
</tr>
</table>
</td>
</form>
</tr>
</table>

<?

// close connection 
mysql_close();

?>

johanafm

Danielinpoland;10988349 wrote:
$id=$_GET['id'];

You do not sanitize this input datum, making you vulnerable to SQL injection. If it's supposed to be an interger, then make it so

$id= (int) $_GET['id'];

Also note that unless you have some means of validating that the user is allowed to edit a specific record, or of course that any user able to edit records in this table is allowed to edit all records in the table, they can simply set another id value themselves.

Other than that, do you have a valid id, i.e. one that actually matches a record in the DB. Else, you will not see any changes either.

Danielinpoland

I made that change as suggested and tried again and still get the same result.

I am the only one with access to the database to make changs, so I can change any. The ID is correct in the address bar displaying

"http://xxxx.com.au/edit.php?id=81"

any other suggestions? I am a total newbie at this

jorgepinho

no error doenst mean it works

did you tried mysql_affected_rows() ?

(put it after the UPDATE)

If it was updated it will return 1

Danielinpoland

I got this error

Parse error: syntax error, unexpected T_STRING in /home2/infernoc/public_html/update.php on line 30

here is the script

<head> 
<!--<meta http-equiv="refresh" content="2; URL=retrieve.php"> -->
</head> 
<?php 

$host="localhost";
$username="xxx";
$password="xxx";
$database="xxx";
$table="xxx"; 

// Make a MySQL Connection 
mysql_connect("$host","$username","$password") or die(mysql_error()); 
mysql_select_db("$database") or die(mysql_error()); 

$fname= mysql_real_escape_string($_POST['fname']); 
$lname= mysql_real_escape_string($_POST['lname']); 
$address= mysql_real_escape_string($_POST['address']); 
$month_birth= mysql_real_escape_string($_POST['month_birth']); 
$mobile= mysql_real_escape_string($_POST['mobile']);
$email=mysql_real_escape_string($_POST['email']);
$subscribe=mysql_real_escape_string($_POST['subscribe']);
$welcome=mysql_real_escape_string($_POST['welcome']);

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


// update data in mysql database 
$sql="UPDATE $table SET fname='$fname', lname='$lname', month_birth='$month_birth', mobile='$mobile',  email='$email', subscribe='$subscribe', welcome='$welcome'  WHERE id='$id'" mysql_affected_rows(); 

//$res=mysql_query($sql) or die ("Error: ". mysql_error(). " with query ". $sql); 
//echo "Record Successfully Updated"; 

?>

jorgepinho

that is not the way...

like this:

// update data in mysql database
$sql="UPDATE $table SET fname='$fname', lname='$lname', month_birth='$month_birth', mobile='$mobile',  email='$email', subscribe='$subscribe', welcome='$welcome'  WHERE id='$id'";

$res=mysql_query($sql) or die ("Error: ". mysql_error(). " with query ". $sql);

echo 'rows updated:' . mysql_affected_rows();

echo "<br />Record Successfully Updated";

Danielinpoland

This is what I got after the changes

rows updated:0
Record Successfully Updated

jorgepinho

It means that no rows are being updated...

echo the $sql, like:

echo $sql;
$res=mysql_query(........

Danielinpoland

Thanks Jorge

Here is the result

UPDATE vip SET fname='xxxx', lname='xxxxx', month_birth='August', mobile='0410 111 111', email='xxxxxx@hotmail.com', subscribe='Yes', welcome='No' WHERE id='0'rows updated:0
Record Successfully Updated

jorgepinho

So... the query is trying to update a row with id = 0

is that what you want ?

Danielinpoland

No. The ID should be 81 for that fileOn the edit page the address is

http://ixxxx.com.au/edit.php?id=81

but when I hit update and it runs the update script I get

UPDATE vip SET fname='xxxxx', lname='xxxx', month_birth='August', mobile='0410 111111, email='xxxx@hotmail.com', subscribe='Yes', welcome='No' WHERE id='0'rows updated:0
Record Successfully Updated

It looks like it is not passing the ID.
Any ideas?

jorgepinho

isnt the page name "retrieve.php" ?

Danielinpoland

retrieve.php displays all the database entries with and edit link.
when you press the edit link it opens edit.php that displays the selected entry in a form.
Then wehen you make a change and press submit it runs update.php

Does that help?

bradgrafelman

Do a [man]var_dump/man on $_GET['id']; that will give you a better idea of what that variable actually contains. Let us know what the output is.

EDIT: Wait... are you passing the 'id' in the URL from the 'edit' page to the 'update' page? If so, can you show us how you're doing that?

jorgepinho

On the update page, check this at the top

<pre>
<?php
print_r($_GET);
print_r($_POST);
?>
</pre>

Danielinpoland

I did a var dump and got

Array
(
    [id] => 81
)
Array
(
)


string(2) "81"

Here are all the pages. first retrieve.php

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>VIP Database</title>

<!-- Styling for Search Button-->
<style type="text/css">
.style1 {
	float: right;
	color: #FF0000;
}
.fieldset1 {
	float: top;
	border-top-style: double;
	border-right-style: double;
	border-bottom-style: double;
	border-left-style: double;
	border-top-color: #9900FF;
	border-right-color: #9900FF;
	border-bottom-color: #9900FF;
	border-left-color: #9900FF;
}
</style>

</head><head>
<title>Search the Database</title>
</head>
</html> 
<?php
// Make a MySQL Connection
mysql_connect("localhost", "xxx", "xxxx") or die(mysql_error());
mysql_select_db("xxxx") or die(mysql_error());

// Get all the data from the "xxx" table
$result = mysql_query("SELECT * FROM vip ORDER by fname ASC") 
or die(mysql_error());  

$query = mysql_query("SELECT * FROM vip"); 
$number=mysql_num_rows($query); 

$id = $_POST['id'];

echo "<TABLE BORDER='3' CELLPADDING='10' CELLSPACING='10' ALIGN = 'CENTER'><TD>";
echo "<table border='0' align = 'center'>";
echo "<tr> <th></th><th colspan='4'><font color='#FF0000' align = 'right'>Search the VIP Database</th> <th><form action='search.php' method='post'>			<input name='term' type='text' class='style1'></th> <th colspan='2'><input name='submit' type='submit' class='style1' value='Search'</th> </tr>";

echo "<tr> <th bgcolor='#f2f0eb'>Total VIP's = $number </th>  <th colspan='9' bgcolor='#f2f0eb'>VIP Database - Order By First Name</th> </tr>";

echo "<tr> <th width='130'><p><span><a href='registervip.html'>Add VIP</a></span></p></th> <th>First Name</th> <th>Last Name</th> <th>Birthday Month</th> <th>Mobile</th> <th>Email</th> <th>Subscribe</th> <th>Welcome</th> <th>Edit</th> <th>Delete</th></tr>";

// keeps getting the next row until there are no more to get

while($row = mysql_fetch_array( $result )) {

// Print out the contents of each row into a table

if($color==1){

echo "<tr bgcolor='#f2f0eb'><td>"; 
echo "</td>";
echo "<td>"; 
echo $row['fname'];
echo "</td><td>"; 
echo $row['lname'];
echo "</td><td>"; 
echo $row['month_birth'];
echo "</td>"; 
echo "<td>"; 
echo $row['mobile'];
echo "</td><td>"; 
echo $row['email'];
echo "</td>";
echo "<td>";
//echo $row['subscribe'];
echo $row['subscribe'] ? 'Yes' : 'No'; 
echo "</td><td>";
echo $row['welcome'] ? 'Yes' : 'No';
echo "</td><td>";

$color="2";
}

// When $color not equal 1, use this table row color 
else {
	echo "<tr bgcolor='#EEEEEE'><td>"; 
	echo "</td>";
	echo "<td>"; 
	echo $row['fname'];
	echo "</td><td>"; 
	echo $row['lname'];
	echo "</td><td>"; 
	echo $row['month_birth'];
	echo "</td>"; 
	echo "<td>"; 
	echo $row['mobile'];
	echo "</td><td>"; 
	echo $row['email'];
	echo "</td>";
	echo "<td>";
	echo $row['subscribe'] ? 'Yes' : 'No'; 
	echo "</td><td>";
	echo $row['welcome'] ? 'Yes' : 'No';
	echo "</td><td>";

$color="1";
}
?>
	<span><a href="edit.php?id=<? echo $row['ID']; ?>">Edit</a></span>
	<td><a href="delete.php?id=<? echo $row['ID']; ?>"onclick="return confirm('Are you sure you want to delete <? echo $row ['fname']." ". $row ['lname']?> ?');"><font color="red"> DELETE </font></a></span></td>
</tr>

<?php
} 
echo "</table>";

echo "</TABLE>";
?>

</html>

now the edit.php

<pre> 
<?php 
print_r($_GET); 
print_r($_POST); 
?> 
</pre> 

<?php

$host="localhost"; // Host name 
$username="xxx"; // Mysql username 
$password="xxx"; // Mysql password 
$db_name="xxxxx"; // Database name 
$tbl_name="xxx"; // Table name 


// Make a MySQL Connection
mysql_connect("localhost", "xxxx", "xxxx") or die(mysql_error());
mysql_select_db("xxxx") or die(mysql_error());

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


// Retrieve data from database 
$sql="SELECT * FROM $tbl_name 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.php">
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>&nbsp;</td>
<td colspan="3"><strong>Update data in mysql</strong> </td>
</tr>
<tr>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
<td align="center">&nbsp;</td>
</tr>
<tr>
<td align="center">&nbsp;</td>
<td align="center"><strong>First Name</strong></td>
<td align="center"><strong>Last Name</strong></td>
<td align="center"><strong>Month Of Birth</strong></td>
<td align="center"><strong>Mobile</strong></td>
<td align="center"><strong>Email</strong></td>
<td align="center"><strong>Subscribe</strong></td>
<td align="center"><strong>Welcome</strong></td>
</tr>
<tr>
<td>&nbsp;</td>
<td align="center"><input name="fname" type="text" id="fname" value="<? echo $rows['fname']; ?>"></td>
<td align="center"><input name="lname" type="text" id="lname" value="<? echo $rows['lname']; ?>" size="15"></td>
<td align="center"><input name="month_birth" type="text" id="month_birth" value="<? echo $rows['month_birth']; ?>" size="15"></td>
<td align="center"><input name="mobile" type="text" id="mobile" value="<? echo $rows['mobile']; ?>" size="15"></td>
<td><input name="email" type="text" id="email" value="<? echo $rows['email']; ?>" size="30"></td>
<td><input name="subscribe" type="text" id="subscribe" value="<? echo $rows['subscribe']? 'Yes' : 'No'; ?>"size="7"></td>
<td><input name="welcome" type="text" id="welcome" value="<? echo $rows['welcome']? 'Yes' : 'No'; ?>" size="7"></td>
<tr>
<td>&nbsp;</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>&nbsp;</td>
</tr>
</table>
</td>
</form>
</tr>
</table>

<?

// close connection 
mysql_close();

?>

now the update.php

<head> 
<!--<meta http-equiv="refresh" content="2; URL=retrieve.php"> -->
</head> 
<?php 

$host="localhost";
$username="xxxx";
$password="xxxxx";
$database="xxxxx";
$table="xxxx"; 

// Make a MySQL Connection 
mysql_connect("$host","$username","$password") or die(mysql_error()); 
mysql_select_db("$database") or die(mysql_error()); 

$fname= mysql_real_escape_string($_POST['fname']); 
$lname= mysql_real_escape_string($_POST['lname']); 
$address= mysql_real_escape_string($_POST['address']); 
$month_birth= mysql_real_escape_string($_POST['month_birth']); 
$mobile= mysql_real_escape_string($_POST['mobile']);
$email=mysql_real_escape_string($_POST['email']);
$subscribe=mysql_real_escape_string($_POST['subscribe']);
$welcome=mysql_real_escape_string($_POST['welcome']);

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

// update data in mysql database 
$sql="UPDATE $table SET fname='$fname', lname='$lname', month_birth='$month_birth', mobile='$mobile',  email='$email', subscribe='$subscribe', welcome='$welcome' WHERE id='$id'"; 

echo $sql;
$res=mysql_query($sql) or die ("Error: ". mysql_error(). " with query ". $sql); 

echo 'rows updated:' . mysql_affected_rows(); 

echo "<br />Record Successfully Updated"; 


?>

bradgrafelman

In update, you shouldn't be looking in the $_GET array, because the id is no longer passed in the URL - it's inside the form that's being POST'ed instead.

Danielinpoland

ok thanks. How do I change that?
In update.php what should the code look like?