Can't update data from MySQL database

Question

This is my update.php codes. I already checked others php file and found no problem. This codes have no error but it cant updating data in database.

require_once "conn.php";
$conn=connect();
$db=connectdb();

$id= "";
$parcelno = "";
$items = "";
if(isset($_REQUEST['id'])){ $id= $_REQUEST['id']; }
if(isset($_REQUEST['parcel'])){ $parcel = $_REQUEST['parcel']; }
if(isset($_REQUEST['items'])){ $items = $_REQUEST['items']; }


mysql_select_db($db,$conn) or die (mysql_error()."\n");
$sql="UPDATE parcel SET parcelno='$parcelno', items='$items' where id='$id'";
$result=@mysql_query($sql) or die(mysql_error()."\n");

This is edit.php code which used for update form :

<?php 
$ic = $_REQUEST["ic"];
require_once "conn.php";
$conn = connect();
$db = connectdb();

mysqli_select_db($conn,$db) or die (mysqli_error($conn)."\n");
$query_usr = "select * from parcel";
$usr = mysqli_query($conn,$query_usr) or die (mysqli_error($conn)."\n".$query_usr);
$row_usr = mysqli_fetch_assoc($usr);
?>
<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style type="text/css">
body {
background-color: #CCC;
}
</style>
</head><body><center><p><a href="adminselect.php"><img src="image/header.png" width="800" height="200"></a></p></center>
<form action="update.php" method="get">
<?php
$sql = "SELECT * FROM parcel where ic = $ic";
$result1 = mysqli_query($conn,$sql);
while ($row=mysqli_fetch_assoc($result1)){
?>
<center><table border="0">
<tr>
<td colspan="3" bgcolor="#0066FF"><strong><center>Update Registration </strong></td>
</tr>
</tr>
<tr>
<td bgcolor="#0099FF">Parcel Number</td>
<td>:</td>
<td bgcolor="#FFFFFF"><input name="parcelno" type="text" id="parcelno" value="<?php echo $row["parcelno"];?>" size="50"></td>
</tr>

<tr>
<td bgcolor="#0099FF">Items</td>
<td>:</td>
<td colspan="2" bgcolor="#FFFFFF">
<p>
<label for="select"></label>
<select name="items" size="1" id="items">
<option><?php echo $row["items"];?></option>
<option>Pos Laju</option>
<option>Pos Ekspress</option>
<option>Skynet</option>
<option>GDEX</option>
<option>Nationwide Express</option>
<option>FedEx</option>
<option>UPS</option>
</select>
</p></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>

<td colspan="2" bgcolor="#CCCCCC"> <input name="" type="submit" value="Update"></td>
</tr>
<?php }?>
</table></center>
</form></body></html>

I researched this problem almost half a day and nothing works :/

Irrelevant to your question, but FYI, PHPMyAdmin is not a database but a tool to interact with database like mysql in your case !! — nkchandra, Aug 02 '14 at 03:49
assuming the server has mysqli extension, OP should use the mysqli equivalents of the mysql functions he's using. — yitwail, Aug 02 '14 at 04:05
@nkchandra 100& correct & edited the question to reflect that since it nothing to do with phpMyAdmin. That is just a web interface for MySQL. — Giacomo1968, Aug 02 '14 at 05:17

score 5 · Accepted Answer · edited May 23 '17 at 11:49

You're using two different variables:

$parcelno in your UPDATE query

and $parcel = $_REQUEST['parcel'];

both variables must match. If one doesn't, then your entire query will fail.

Add error reporting to the top of your file(s) right after your opening <?php tag, which will help during pre-production testing.

error_reporting(E_ALL);
ini_set('display_errors', 1);

Your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements.

Plus, I quote nkchandra in a comment +1 (if I may):

"Irrelevant to your question, but FYI, PHPMyAdmin is not a database but a tool to interact with database like mysql in your case"

Edit: After reading your comment, it seems like you will need to switch to mysqli_ functions.

This is just a quick fix before you learn to use prepared statements.

<?php

error_reporting(E_ALL);
ini_set('display_errors', 1);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);


$DB_HOST = "xxx"; // replace with yours
$DB_USER = "xxx"; // replace with yours
$DB_PASS = "xxx"; // replace with yours
$DB_NAME = "xxx"; // replace with yours


$conn = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($conn->connect_errno > 0) {
  die('Connection failed [' . $conn->connect_error . ']');
}

$id= "";
$parcelno = "";
$items = "";
if(isset($_REQUEST['id'])){ 
$id= mysqli_real_escape_string($conn,$_REQUEST['id']); }
if(isset($_REQUEST['parcel'])){ 
$parcelno = mysqli_real_escape_string($conn,$_REQUEST['parcel']); }
if(isset($_REQUEST['items'])){ 
$items = mysqli_real_escape_string($conn,$_REQUEST['items']); }


$sql="UPDATE parcel SET parcelno='$parcelno', items='$items' where id='$id'";
$result=mysqli_query($conn,$sql) or die(mysqli_error()."\n");

if (!$result)
    {
        throw new Exception($conn->error);
    }

else { echo "Success"; }

mysqli_close($conn); // close the connection

Plus, as per r3wt's comment: You can also use:

$result= $conn->query($sql) or die(mysqli_error()."\n");

instead of

$result=mysqli_query($conn,$sql) or die(mysqli_error()."\n");

score 0 · Answer 2 · edited Aug 02 '14 at 04:00

0

There is a problem with the parse variable in your query. Try this:

$sql="UPDATE parcel SET parcelno='" . $parcelno . "', items='" . $items . "' where id='" . $id . "'";

edited Aug 02 '14 at 04:00

Nick Dickinson-Wilde

1,015
2
15
21

answered Aug 02 '14 at 03:50

hunguyenv

51
1
2

score 0 · Answer 3 · answered Aug 02 '14 at 03:54

<?php
ini_set('display_errors', '1');
error_reporting(E_ALL ^ E_NOTICE);
require_once "conn.php";

$conn=connect();
$db=connectdb();

$id= "";
$parcelno = "";
$items = "";
if(isset($_REQUEST['id'])){ $id= $_REQUEST['id']; }
if(isset($_REQUEST['parcel'])){ $parcel = $_REQUEST['parcel']; }
if(isset($_REQUEST['items'])){ $items = $_REQUEST['items']; }


mysql_select_db($db,$conn) or die (mysql_error()."\n");
$sql="UPDATE parcel SET parcelno='".$parcel."', items='".$items."' where id='".$id."'";
$result=mysql_query($sql) or die(mysql_error()."\n");

?>

Try this.

Can't update data from MySQL database

3 Answers3

Linked