Updating data in mysql

cskott · 11-07-2009, 05:24 PM

Hello hope you can help me..

I have found this script on http://www.phpeasystep.com/mysql/9.html but it dosen't update the database. I don't get any errors so i can't find out what is wrong.

UPDATE.PHP:
<?php
$host="localhost"; // Host name
$username="xx"; // Mysql username
$password="xx"; // mysql password
$db_name="xx"; // Database name
$tbl_name="test_mysql"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// 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_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>Name</strong></td>
<td align="center"><strong>Lastname</strong></td>
<td align="center"><strong>Email</strong></td>
</tr>
<tr>
<td> </td>
<td align="center"><input name="name" type="text" id="name" value="<? echo $rows['name']; ?>"></td>
<td align="center"><input name="lastname" type="text" id="lastname" value="<? echo $rows['lastname']; ?>" size="15"></td>
<td><input name="email" type="text" id="email" value="<? echo $rows['email']; ?>" size="15"></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();

?>

UPDATE_AC.PHP:
<?php
$host="localhost"; // Host name
$username="xx"; // Mysql username
$password="xx"; // Mysql password
$db_name="xx"; // Database name
$tbl_name="test_mysql"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// update data in mysql database
$sql = "UPDATE $tbl_name SET name='$name', lastname='$lastname', email='$email' WHERE id='$id'";
$result=mysql_query($sql);

// if successfully updated.
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='list_records.php'>View result</a>";
}

else {
echo "ERROR";
}

?>

LIST_RECORDS.PHP:
<?php
$host="localhost"; // Host name
$username="xx"; // Mysql username
$password="xx"; // Mysql password
$db_name="xx"; // Database name
$tbl_name="test_mysql"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name";
$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['name']; ?></td>
<td><? echo $rows['lastname']; ?></td>
<td><? echo $rows['email']; ?></td>

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

adamdecaf · 11-07-2009, 05:39 PM

Put the line

PHP Code:


			
error_reporting(E_ALL);

in as one of the first lines and see what error it gives.

Just a note, none of the data is being escaped; this means that I could submit a MySQL command like "DROP TABLE", and the entire database/table would be wiped clean. This code is very poorly written and I would advise you to steer clear of any code written by this author/site.

cskott · 11-07-2009, 06:25 PM

I have put in error_reporting(E_ALL);
but it dosen't come with any errors?

cskott · 11-08-2009, 10:24 AM

Is there not any who has an idea what the problem is?

sLysdal · 11-08-2009, 05:07 PM

try changing

PHP Code:


			
mysql_query($sql);

to

PHP Code:


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

what does that output?

and as adamdecaf said, the code can easily be injected

cskott · 11-08-2009, 08:00 PM

I have put in mysql_query($sql) or die(mysql_error()); in UPDATE_AC.PHP but that didn't work..

What can i do so my code isn't so easy to enject?
I don't know so much about php so hope you can help me. Also to get the UPDATE working..

adamdecaf · 11-08-2009, 08:19 PM

Well, I would run mysql_real_escape_string() on every variable that is being placed into the mysql statement.

cskott · 11-08-2009, 08:23 PM

and what would that mean?
Could you place an eksampel..

adamdecaf · 11-09-2009, 12:56 AM

An escape sample.

PHP Code:


			
$sql = "UPDATE " . mysql_real_escape_string($tbl_name) . 
" SET name=\'" . mysql_real_escape_string($name) . 
"\', lastname=\'" . mysql_real_escape_string($lastname) .
 "\', email=\'" . mysql_real_escape_string($email) . 
"\' WHERE id=\'" . mysql_real_escape_string($id) . "\'";

cskott · 11-09-2009, 10:15 AM

I have put your eksampel in update_ac.php and then i get ERROR.

<?php
$host="localhost"; // Host name
$username="xx"; // Mysql username
$password="xx"; // Mysql password
$db_name="xx"; // Database name
$tbl_name="test_mysql"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// update data in mysql database
$sql = "UPDATE " . mysql_real_escape_string($tbl_name) .
" SET name=\'" . mysql_real_escape_string($name) .
"\', lastname=\'" . mysql_real_escape_string($lastname) .
"\', email=\'" . mysql_real_escape_string($email) .
"\' WHERE id=\'" . mysql_real_escape_string($id) . "\'";

// if successfully updated.
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='list_records.php'>View result</a>";
}

else {
echo "ERROR";
}

?>

sketchMedia · 11-09-2009, 10:52 AM

Right first off, there is no legitimate reason for wrapping your variables in double quotes when passing into a function (for example your mysql_connect etc), all it does is make php do extra work for no apparent reason.

PHP Code:


			
mysql_connect("$host" ...

becomes:

PHP Code:


			
mysql_connect($host ...

Secondly you don't call mysql_query at any point in your script, mysql can't read your mind.

Also in your sql, there is no need to escape the single quotes if the string is wrapped in doubles

heres a more or less cleaned version:

PHP Code:


			
<?php
$host     = 'localhost';  // Host name
$username = 'xx';         // Mysql username
$password = 'xx';         // Mysql password
$db_name  = 'xx';         // Database name
$tbl_name = 'test_mysql'; // Table name

// Connect to server and select database.
mysql_connect($host, $username, $password)or die("cannot connect");
mysql_select_db($db_name)or die("cannot select DB");

// update data in mysql database
$sql = "UPDATE " . mysql_real_escape_string($tbl_name) .
       " SET name='" . mysql_real_escape_string($name) .
       "', lastname='" . mysql_real_escape_string($lastname) .
       "', email='" . mysql_real_escape_string($email) .
       "' WHERE id='" . mysql_real_escape_string($id) . "'";

$result = mysql_query($sql);
// if successfully updated.
if($result){
    echo "Successful";
    echo "<BR>";
    echo "<a href='list_records.php'>View result</a>";
}else {
    echo "ERROR";
}

also you might want to change

PHP Code:


			
echo "ERROR";

to somehting more useful like

PHP Code:


			
echo mysql_error();

cskott · 11-09-2009, 11:08 AM

?php
$host = 'localhost'; // Host name
$username = 'xx'; // Mysql username
$password = 'xx'; // Mysql password
$db_name = 'xx'; // Database name
$tbl_name = 'test_mysql'; // Table name

// Connect to server and select database.
mysql_connect($host, $username, $password)or die("cannot connect");
mysql_select_db($db_name)or die("cannot select DB");

// update data in mysql database
$sql = "UPDATE " . mysql_real_escape_string($tbl_name) .
" SET name='" . mysql_real_escape_string($name) .
"', lastname='" . mysql_real_escape_string($lastname) .
"', email='" . mysql_real_escape_string($email) .
"' WHERE id='" . mysql_real_escape_string($id) . "'";

$result = mysql_query($sql);
// if successfully updated.
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='list_records.php'>View result</a>";
}else {
echo mysql_error();
}

I have changed the file to this now.
It tell's me that the database is successfully updated, but it still doens't update the database?

adamdecaf · 11-09-2009, 10:51 PM

PHP Code:


			
// Take this out when you're ready to make the code live.
error_reporting(E_ALL);

// Connect to server and select database.
$hostname = 'CHANGE ME';
$username = 'CHANGE ME';
$password = 'CHANGE ME';
$database = 'CHANGE ME';

$link = mysql_connect($host, $username, $password) || die(mysql_error());
// mysql_select_db($database, $link)or die(mysql_error());

// Set some default data.
$name = 'John';
$lastname = 'Doe';
$email = 'user@example.com';
$id = 123145;

// update data in mysql database
$sql = "UPDATE " . $database .
       " SET name='" . mysql_real_escape_string($name) .
       "', lastname='" . mysql_real_escape_string($lastname) .
       "', email='" . mysql_real_escape_string($email) .
       "' WHERE id='" . mysql_real_escape_string($id) . "'";

$result = mysql_query($sql, $link);

Ok, wow, I was stupid and escaped $db_name. This will cause MySQL to try to update the data into a nonexistence/new database. Sorry!!

I would recommend the code in the post above me (sketchMedia's), or use this code, it's just easier for me to read. I was going through the code trying to fix any mundane bug and I found that I was escaping the table name.

cskott · 11-10-2009, 07:23 AM

What about $tbl_name = 'test_mysql';
Shoulden't that be somewhere in the script?

adamdecaf · 11-10-2009, 09:52 PM

I changed it to $database, it really doesn't matter.

cskott · 11-11-2009, 06:35 AM

I get this error now:

// Take this out when you're ready to make the code live. error_reporting(E_ALL); // Connect to server and select database. $hostname = 'localhost'; $username = 'xx'; $password = 'xx'; $database = 'xx'; $link = mysql_connect($host, $username, $password) || die(mysql_error()); // mysql_select_db($database, $link)or die(mysql_error()); // Set some default data. $name = 'John'; $lastname = 'Doe'; $email = 'user@example.com'; $id = 123145; // update data in mysql database $sql = "UPDATE " . $database . " SET name='" . mysql_real_escape_string($name) . "', lastname='" . mysql_real_escape_string($lastname) . "', email='" . mysql_real_escape_string($email) . "' WHERE id='" . mysql_real_escape_string($id) . "'"; $result = mysql_query($sql, $link);

maeltar · 11-11-2009, 07:46 AM

Looks like you are just copy/pasting what's been put on here....

Code:

$link = mysql_connect($host, $username, $password) || die(mysql_error());
// mysql_select_db($database, $link)or die(mysql_error());

change to

Code:

$link = mysql_connect($host, $username, $password) || die(mysql_error());
mysql_select_db($database, $link)or die(mysql_error());

did you put the code inside php tags ?

Code:

<?PHP
error_reporting(E_ALL);

// Connect to server and select database.
$hostname = 'CHANGE ME';
$username = 'CHANGE ME';
$password = 'CHANGE ME';
$database = 'CHANGE ME';

$link = mysql_connect($host, $username, $password) || die(mysql_error());
mysql_select_db($database, $link)or die(mysql_error());

// Set some default data.
$name = 'John';
$lastname = 'Doe';
$email = 'user@example.com';
$id = 123145;

// update data in mysql database
$sql = "UPDATE " . $database .
       " SET name='" . mysql_real_escape_string($name) .
       "', lastname='" . mysql_real_escape_string($lastname) .
       "', email='" . mysql_real_escape_string($email) .
       "' WHERE id='" . mysql_real_escape_string($id) . "'";

$result = mysql_query($sql, $link);

?>

cskott · 11-11-2009, 03:49 PM

No i forgot the php tags.. Sorry..

Now have put the tags in then it goes to a blank page when i press submit. No errors nothing.. The database is still not updated. Should i not tell the script what tabel in the database it should update.

maeltar · 11-11-2009, 05:34 PM

yep seen the mistake...

Code:

$sql = "UPDATE " . $database .
       " SET name='" . mysql_real_escape_string($name) .
       "', lastname='" . mysql_real_escape_string($lastname) .
       "', email='" . mysql_real_escape_string($email) .
       "' WHERE id='" . mysql_real_escape_string($id) . "'";

should read

Code:

$sql = "UPDATE " . $table_name .
       " SET name='" . mysql_real_escape_string($name) .
       "', lastname='" . mysql_real_escape_string($lastname) .
       "', email='" . mysql_real_escape_string($email) .
       "' WHERE id='" . mysql_real_escape_string($id) . "'";

cskott · 11-12-2009, 06:27 AM

No it still goes to a blank page

<?PHP
// Take this out when you're ready to make the code live.
error_reporting(E_ALL);

// Connect to server and select database.
$hostname = 'localhost';
$username = 'xx';
$password = 'xx';
$database = 'xx';
$tbl_name = 'xx';

$link = mysql_connect($host, $username, $password) || die(mysql_error());
// mysql_select_db($database, $link)or die(mysql_error());

// update data in mysql database
$sql = "UPDATE " . $tbl_name .
" SET name='" . mysql_real_escape_string($name) .
"', lastname='" . mysql_real_escape_string($lastname) .
"', email='" . mysql_real_escape_string($email) .
"' WHERE id='" . mysql_real_escape_string($id) . "'";

$result = mysql_query($sql, $link);

?>