更新查询，更新多个数据

Question

I want to update multiple data by using this code, but the problem is, when i tried it, it updates the whole data which has the same category id and It supposed to update individually. what could be the solution. Please help. Thanks in advance :)

<?php
if (isset($_GET['pid'])){
    $view="";
    $targetID = $_GET['pid'];
    $sql = mysql_query("SELECT specs, category_id, price FROM specs WHERE category_id='$targetID'");
                                $productCount = mysql_num_rows($sql);
                                    if($productCount > 0){
                                        while($row = mysql_fetch_array($sql)){
                                        $specs = $row["specs"];
                                        $category_id = $row["category_id"]; 
                                        $price = $row["price"];
                                        $view .=  '<div class="control-group">
                            <label class="control-label" >Specs</label>
                            <div class="controls">
                            <input type="text"  placeholder="Specs" name="specs" value="'.$specs.'">
                            </div>
                            </div>
                            <div class="control-group">
                            <label class="control-label" >Price</label>
                            <div class="controls">
                            <input type="text"  placeholder="Price" name="price" value="PHP&nbsp;'.number_format($price, 2).'">
                            </div>
                            </div>';
                                }
                                }
        }
    ?>
    <?php
    if (isset($_POST['specs'])){

    $pid = mysql_real_escape_string($_POST['thisID']);
    $specs = mysql_real_escape_string($_POST['specs']);;
    $price = mysql_real_escape_string($_POST['price']);
    $sql= mysql_query("UPDATE specs SET specs='$specs', price='$price' WHERE category_id='$pid'");

    header("Location: manageproducts.php");
    exit();
    }
    ?>

And heres the html.

 <div class="container">
    <div class="page-header">
      <h1>Manage Products</h1>
    </div>
    <div class="row-fluid ">

            <div class="box span12center-align" >
                <div class="box-header well" data-original-title>
                    <center><h2><i class="icon-edit"></i> Edit Specifications </h2></center>
                </div>

                <div class="box-content" >
                    <form class="form-horizontal" action="" method='post'>

                        <fieldset>

                         <?php echo $view; ?>


                          <div class="form-actions">
                            <input name="thisID" type="hidden" value="<?php echo $targetID; ?>">
                            <button type="submit" class="btn btn-primary" name="add_product">Update Item</button>
                            <button class="btn">Cancel</button>
                          </div>
                        </fieldset>
                    </form>
                </div>
            </div><!--/span-->

        </div><!--/row--></center>
        </div>
        </div>
        </div>

Answer 1

You need to add the primary key to the WHERE-clause of the row you want to update, now you're just updating all rows with a certain category_id.

So add a primary key id to the table (if your table doesn't have one already) and set it to auto-increment. Then modify your select query:

"SELECT id, specs, category_id, price FROM specs WHERE category_id='$targetID'"

add that id to a hidden input field.

Then you can modify the update query like this:

"UPDATE specs SET specs='$specs', price='$price' WHERE category_id='$pid' AND id='$id'"

SQL Injection alert

You should also know that the code you have written is very dangerous and prone to SQL injection. Never, ever, use GET/POST variables directly in the query. Please use mysqli with prepared statements or PDO.