Need help updating just a single field mysql in php

Question

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

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Dec 19 '20 at 13:04

score 3 · Answer 1 · answered Dec 19 '20 at 01:28

3

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?.

answered Dec 19 '20 at 01:28

GMB

216,147
25
84
135

can you show me how to add the where statement in mysqli_query($conn,"UPDATE tracking set orderstatus='" . $_POST['orderstatus'] . "'"); Im still pretty new to php-mysql – Susie Skrill Dec 19 '20 at 01:37
@SusieSkrill: I gave the query in my answer: the `where` clause goes after the `set` clause. But, again: use prepared statements. I would recommend carefully reading the link I put in my answer, and implementing one of the suggested solutions. – GMB Dec 19 '20 at 01:43

Julien B. · Answer 2 · 2020-12-19T02:27:08.540

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.

@SusieSkrill If you consider that this answers your question, please mark it as accepted and maybe upvote it if your access level allows it. That would be very much appreciated. — Julien B., Dec 19 '20 at 17:59

Need help updating just a single field mysql in php

2 Answers2