Need help updating just a single field mysql in php
i have an issue with my code. I have data in a database, and i need help with my php. i coded a page that helps me edit my database by clicking on the specific data and editing it via the website, but anytime i edit a specfic line, it affects all the data of other fields in the database.
<?php
include_once 'db.php';
if(count($_POST)>0) {
mysqli_query($conn,"UPDATE tracking set orderstatus='" . $_POST['orderstatus'] . "'");
$message = "Record Modified Successfully";
}
$result = mysqli_query($conn,"SELECT * FROM tracking WHERE trackingnum='" . $_GET['trackingnum'] . "'");
$row= mysqli_fetch_array($result);
?>
<html>
<style>
@import url(https://fonts.googleapis.com/css?family=Montserrat:400,700);
body { background:rgb(30,30,40); }
form { max-width:420px; margin:50px auto; }
.feedback-input {
color:black;
font-family: Helvetica, Arial, sans-serif;
font-weight:500;
font-size: 18px;
border-radius: 5px;
line-height: 22px;
background-color: transparent;
border:2px solid #CC6666;
transition: all 0.3s;
padding: 9px;
margin-bottom: 15px;
width:100%;
box-sizing: border-box;
outline:0;
}
.feedback-input:focus { border:2px solid #CC4949; }
textarea {
height: 150px;
line-height: 150%;
resize:vertical;
}
[type="submit"] {
font-family: 'Montserrat', Arial, Helvetica, sans-serif;
width: 100%;
background:#CC6666;
border-radius:5px;
border:0;
cursor:pointer;
color:black;
font-size:24px;
padding-top:10px;
padding-bottom:10px;
transition: all 0.3s;
margin-top:-4px;
font-weight:700;
}
[type="submit"]:hover { background:#CC4949; }
</style>
<head>
<title>Update Tracking Data</title>
</head>
<body>
<form name="frmUser" method="post" action="">
<div><?php if(isset($message)) { echo $message; } ?>
</div>
<div style="padding-bottom:5px;">
</div>
Tracking Number: <br>
<input type="text" name="trackingnum" class="feedback-input" value="<?php echo $row['trackingnum']; ?>">
<br>
Current Status: <?php echo $row['orderstatus']; ?><br>
Order Status: <br>
<select name="orderstatus" class="feedback-input"
<option value = ""></option>
<option value = ""><?php echo $row['orderstatus']; ?></option>
<option value = "Pending">Pending</option>
<option value = "Confirmed">Confirmed</option>
<option value = "In-Progress">In-Progress</option>
<option value = "In-Transit">In-Transit</option>
<option value = "On Route">On Route</option>
<option value = "Delivered">Delivered</option>
</select>
<br>
<input type="submit" name="submit" value="Submit" class="buttom">
</form>
</body>
</html>
<section class="about_top">
<div class="container">
<div class="row">
<div class="col-md-4 col-sm-4 col-xs-12">
<div class="about_single_item">
<div class="item_icon">
</div>
<div class="about_single_item_content">
<h4><a href="form.php">Add New Tracking</h4></a>
</div>
</div>
</div>
<div class="col-md-4 col-sm-4 col-xs-12">
<div class="about_single_item">
<div class="item_icon">
</div>
<div class="about_single_item_content">
<h4><a href="edit.php">Edit Existing Tracking</h4></a>
</div>
</div>
</div>
<div class="col-md-4 col-sm-4 col-xs-12">
<div class="about_single_item">
<div class="item_icon">
</div>
<div class="about_single_item_content">
<h4><a href="index.php">Test Tracking</h4></a>
</div>
</div>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Fast Delivery</title>
<!-- bootstrap css -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" integrity="sha384-1q8mTJOASx8j1Au+a5WDVnPi2lkFfwwEAa8hDDdjZlpLegxhjVME1fgjWPGmkzs7" crossorigin="anonymous">
<!-- font Awesome Css -->
<!--slick css-->
<link href="css/slick.css" rel="stylesheet">
<!-- owl-carousel css -->
<link href="css/owl.carousel.css" rel="stylesheet">
<!-- YTPlayer css For Background Video -->
<link href="css/jquery.mb.YTPlayer.min.css" rel="stylesheet">
<!-- style css -->
<!-- Responsive Css -->
<link href="css/responsive.css" rel="stylesheet">
<!-- browser campatibel css files-->
<!--[if lt IE 9]>
<script src="//oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
<script src="//oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
<![endif]-->
</head>
<body class="js">
</section>
</body>
</html>
Above is my update page code. https://i.stack.imgur.com/gQSX6.png If i update via my site both rows change. even tho i just want 1 row to change https://i.stack.imgur.com/AiIcD.png
Answer
Solution:
You want a where clause in the update statement, presumably on trackingnum:
update tracking
set orderstatus = :orderstatus
where trackingnum = :trackingnum
Important note: use prepared statements! Do not concatenate POST values in the query string: this is inefficient, and highly unsafe as it opens up your code to SQL injection attacks. Recommend reading: How can I prevent SQL injection in PHP?.
Answer
Solution:
You need to target a specific record using a WHERE clause. Your code is also very prone to SQL injection. You should never use user input concatenated directly into your SQL statements. You should always use prepared statements.
Using mysqli it would look like something like this.
I will assume you have an id field that is an integer, adapt it to your data model. That field would have to be passed along with your form, an input (type="hidden") could be appropriate for that. But once again, adapt this to your needs.
$stmt = $conn->prepare("UPDATE tracking set orderstatus=? WHERE id=?");
// The "si" here is a type representation where first parameter is a string second is an integer
$stmt->bind_param("si", $status, $id);
$status = $_POST['orderstatus'];
$id = $_POST['id'];
$stmt->execute();
The same goes for select statements (or any statement really).
$stmt = $conn->prepare("SELECT * FROM tracking WHERE trackingnum=?");
$stmt->bind_param("s", $trackingnum);
$trackingnum = $_GET['trackingnum'];
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
More info can be found here https://www.php.net/manual/en/mysqli.prepare.php
Proper validation of the user inputs would also be recommended, but at least with a prepared statement, you prevent SQL injection. Keep in mind that user inputs can almost always be tempered.
Note: I did not test any of this.
Source