Update query not working PHP MySQL

Question

Can anybody help me understand why this update query isn't updating the fields in db.

    <?php
   $id= $_GET['id'];

                 $query1 = mysql_query("SELECT * FROM invoice WHERE id=$id limit 1");

       ?> 
      <form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
         <div class="card">
            <div class="card-header">
               <strong>Create Tax</strong>
            </div> <?php 

            while($query2=mysql_fetch_array($query1)){
            // $results = mysql_fetch_array($raw_results) puts data from database into array, while it's valid it does the loop

                ?>
            <div class="card-block">
               <div class="col-sm-6">
                  <div class="form-group">
                     <label for="company">Coustomer Name</label>
                     <input type="text" class="form-control" id="company" name="cname" value="<?php echo $query2['CustomerName']?>" placeholder="Coustomer Name">
                  </div>
               </div>
               <div class="col-sm-6">
                  <div class="form-group">
                     <label for="company">Coustomer Mobile </label>
                     <input type="text" class="form-control" id="company" name="mobile" value="<?php echo $query2['CustomerMobile']?>" placeholder="Coustomer Mobile">
                  </div>
               </div>
               <div class="col-sm-6">
                  <div class="form-group">
                     <label for="company">Email</label>
                     <input type="text" class="form-control" id="company" name="email" value="<?php echo $query2['Email']?>"  placeholder="Email">
                  </div>
               </div> <div class="col-sm-6">
                  <div class="form-group">
                     <label for="company">Coustomer Streat </label>
                     <input type="text" class="form-control" id="company" name="streat" value="<?php echo $query2['CustomerStreat']?>" placeholder="Coustomer Streat">
                  </div>
               </div> <div class="col-sm-6">
                  <div class="form-group">
                     <label for="company">Coustomer City</label>
                     <input type="text" class="form-control" id="company" name="city" value="<?php echo $query2['CustomerCity']?>" placeholder="Coustomer City">
                  </div>
               </div> <div class="col-sm-6">
                  <div class="form-group">
                     <label for="company">Coustomer State</label>
                     <input type="text" class="form-control" id="company" name="state" value="<?php echo $query2['CustomerState']?>" placeholder="Coustomer State">
                  </div>
               </div> <div class="col-sm-6">
                  <div class="form-group">
                     <label for="company">Coustomer Country</label>
                     <input type="text" class="form-control" id="company" name="country" value="<?php echo $query2['CustomerCountry']?>" placeholder="Coustomer Country">
                  </div>
               </div> <div class="col-sm-6">
                  <div class="form-group">
                     <label for="company">Zip/Pin Code</label>
                     <input type="text" class="form-control" id="company" name="pin" value="<?php echo $query2['ZipCode']?>" placeholder="ZipCode">
                  </div>
               </div> 
               <?php } ?>
               <div class="col-sm-6">
                  <div class="form-group">
                     <label for="company">Tax Name </label><?php

                 $query1 = mysql_query("SELECT * FROM Tax");

       ?> 
                     <select id="select" name="tax" class="form-control" size="1"><?php 

            while($query2=mysql_fetch_array($query1)){
            // $results = mysql_fetch_array($raw_results) puts data from database into array, while it's valid it does the loop

                ?>
                                                <option value="<?php echo $query2['NameOfTax']?> , <?php echo $query2['TaxPercentage']?>"><?php echo $query2['NameOfTax']?> , <?php echo $query2['TaxPercentage']?></option>
                               <?php $taxvalue =  $query2['TaxPercentage']; ?>                         <?php } ?>

                                            </select>
                  </div>
               </div> <div class="col-sm-6">
                  <div class="form-group">
                     <label for="company">Iteam Name </label>
                     <input type="text" class="form-control" id="company" name="item" placeholder="Iteam Name" value="<?php echo $query2['ItemName']?>">
                  </div>
               </div> 
               <div class="col-sm-6">
                  <div class="form-group">
                     <label for="company">Iteam Price </label>
                     <input type="text" class="form-control" id="company" name="itemprice" placeholder="Iteam price" value="<?php echo $query2['ItemPrice']?>">
                  </div>
               </div> 
               <div class="col-sm-6">
                  <div class="form-group">
                     <label for="company">Paid Ammount </label>
                     <input type="text" class="form-control" id="company" name="pammount" placeholder="Paid Ammount" value="<?php echo $query2['PaidAmmount']?>">
                  </div>
               </div> <div class="col-sm-6">
                  <div class="form-group">
                     <label for="company">Date</label>
                     <input type="text" class="form-control" id="company" name="date" placeholder="Date" value="<?php echo $query2['Date']?>">
                  </div>
               </div>
               <div class="col-sm-12">
                  <input type="submit" class="btn btn-outline-success" name="submit" value="Processe Invoice"/>
               </div>
            </div>
         </div>
   </div>
   </div> 
   <!--/col-->
   </form>
  <?php 


  $cname = $_POST['cname'];
  $mobile = $_POST['mobile'];
  $email = $_POST['email'];
  $streat = $_POST['streat'];
  $city = $_POST['city'];
  $state = $_POST['state'];
  $country = $_POST['country'];
  $pin = $_POST['pin'];
  $tax = $_POST['tax'];
  $item = $_POST['item'];
  $itemprice = $_POST['itemprice'];
  $percent = ($taxvalue / 100) * $itemprice ;
  $tammount = $percent  + $itemprice; 
  $pammount = $_POST['pammount'];
  $uammount = $tammount - $pammount;
  $date = $_POST['date'];
  $ip = $_SERVER['REMOTE_ADDR'];
  $date1 = date('h,i,s');

  if(isset($_POST['submit']))
      {

      $sql = "UPDATE `ero`.`invoice` SET `CustomerName` = '$cname', `CustomerMobile` = '$mobile', `Email` = '$email', `CustomerStreat` = '$streat', `CustomerCity` = '$city', `CustomerState` = '$state', `CustomerCountry` = '$country', `ZipCode` = '$pin', `TaxName` = '$tax', `ItemName` = '$item', `ItemPrice` = '$itemprice', `TotalAmmount` = '$tammount', `PaidAmmount` = '$pammount', `UnpaidAmmount` = '$uammount', `Date` = '$date', `IP` = '$ip', `DateTime` = '$date1'  WHERE `invoice`.`id` = '$id'";

      $result = mysql_query($sql);

      if($result)


 $url='customer.php';

    echo '<script>window.location = "'.$url.'";</script>';
    die;
      }

      ?>

invoice.php

<?php

                 $query1 = mysql_query("SELECT * FROM invoice");

       ?> 
                                <table class="table table-bordered table-striped table-condensed">
                                    <thead>
                                        <tr>
                                            <th>Customer Name</th>
                                            <th>Mobile</th>
                                            <th>Email</th>
                                            <th>City</th>
                                            <th>Item Name</th>
                                            <th>Item Price</th>
                                            <th>Tax</th>
                                            <th>Total Ammount</th>
                                            <th>Paid Ammount</th>
                                            <th>Unpaid Ammount</th>                                      
                                            <th>Action</th>
                                        </tr>
                                    </thead>
                                    <tbody><?php 

            while($query2=mysql_fetch_array($query1)){
            // $results = mysql_fetch_array($raw_results) puts data from database into array, while it's valid it does the loop

                ?>

                                        <tr>
                                            <td><?php echo $query2['CustomerName']?></td>
                                            <td><?php echo $query2['CustomerMobile']?></td>
                                            <td><?php echo $query2['Email']?></td>
                                            <td><?php echo $query2['CustomerCity']?></td>
                                            <td><?php echo $query2['ItemName']?></td> 
                                            <td><?php echo $query2['ItemPrice']?></td>
                                            <td><?php echo $query2['TaxName']?></td>
                                            <td><?php echo $query2['TotalAmmount']?></td>
                                            <td><?php echo $query2['PaidAmmount']?></td>

                                            <td <?php if ($query2['UnpaidAmmount'] > 1) echo 'style="background-color:#FF0000"' ?>><?php if ($query2['UnpaidAmmount'] < 1) echo "Ammount Paid"; else echo  $query2['UnpaidAmmount'];?></td>
                                            <td>
<button type="button" class="btn btn-outline-danger btn-sm"><a href="DeleteInvoice.php?id='<?php echo $query2['id'] ?>'" >Delete</a>
<button type="button" class="btn btn-outline-success btn-sm"><a href="ViewInvoice.php?id='<?php echo $query2['id'] ?>'" >View</a></button>
<button type="button" class="btn btn-outline-success btn-sm"><a href="updatecheck.php?id='<?php echo $query2['id'] ?>'" >Update</a></button>

</div>                        </td>      </tr>  <?php } ?>
<tr>

this scripts redirects customer.php.if i place a manual id like id = 38. its wroking. but there is no update in MySql Table.i am new to programming please explain with some code examples.

Thanks

Comments are not for extended discussion; this conversation has been [moved to chat](http://chat.stackoverflow.com/rooms/130001/discussion-on-question-by-utam-sharma-update-query-not-working-php-mysql). — Bhargav Rao, Dec 07 '16 at 11:01
Tags are for what's relevant to the question Utam, not a smorgasbord of everything you're using to build your app. E.g. see the description for [tag:phpmyadmin]. There is nothing in the question about CakePHP, phpmyadmin, sending an email, or xampp. Alternatively your question is missing the tags `monitor`, `keyboard` and `chair` :). — AD7six, Dec 08 '16 at 08:33

score 1 · Answer 1 · answered Dec 07 '16 at 10:32

Replace single quote in the query with double quotes and since you are already using the double quotes for the query, escape the double quotes for the variables or use string concatenation.

<?php
$id = $_GET['id'];
$cname = $_POST['cname'];
$mobile = $_POST['mobile'];
$email = $_POST['email'];
$streat = $_POST['streat'];
$city = $_POST['city'];
$state = $_POST['state'];
$country = $_POST['country'];
$pin = $_POST['pin'];
$tax = $_POST['tax'];
$item = $_POST['item'];
$itemprice = $_POST['itemprice'];
$percent = ($taxvalue / 100) * $itemprice;
$tammount = $percent + $itemprice;
$pammount = $_POST['pammount'];
$uammount = $tammount - $pammount;
$date = $_POST['date'];
$ip = $_SERVER['REMOTE_ADDR'];
$date1 = date('h,i,s');

if (isset($_POST['submit']))
    {
    $sql = "UPDATE `ero`.`invoice` SET `CustomerName` = \"$cname\", `CustomerMobile` = \"$mobile\", `Email` = \"$email\", `CustomerStreat` = \"$streat\", `CustomerCity` = \"$city\", `CustomerState` = \"$state\", `CustomerCountry` = \"$country\", `ZipCode` = \"$pin\", `TaxName` = \"$tax\", `ItemName` = \"$item\", `ItemPrice` = \"$itemprice\", `TotalAmmount` = \"$tammount\", `PaidAmmount` = \"$pammount\", `UnpaidAmmount` = \"$uammount\", `Date` = \"$date\", `IP` = \"$ip\", `DateTime` = \"$date1\"  WHERE `invoice`.`id` = \"$id\"";
    $result = mysql_query($sql);
    if ($result) $url = 'customer.php';
    echo '<script>window.location = "' . $url . '";</script>';
    die;
?>

thanks guys for your work. i really appreciate it . but i have a another problem my internet is not working correctly. it will be very helpful for me if you can download these files from dropbox.com/s/k928pjh9cp0oad4/ero.zip?dl=0 . and post a solution here. i will here after just some couple of hours. Thank you so much @Epistemophile — Utam Sharma, Dec 07 '16 at 11:41
here is the updated link with login file https://www.dropbox.com/s/k928pjh9cp0oad4/ero.zip?dl=0 @MasivuyeCokile — Utam Sharma, Dec 08 '16 at 04:55
Please note that your code is vulnerable to SQL injection and can be used to generate XSS attacks. Validate and sanitise your input, escape values in SQL queries, escape your HTML output. Better still, use libraries that deal with it all for you so you don't need to remember. Also, don't use script tags to do redirects; set an HTTP `Location` header instead along with a `301` response code. — Synchro, Dec 08 '16 at 07:17

score -2 · Answer 2 · answered Dec 07 '16 at 09:53

-2

UPDATE table
SET col_name = 'new value'
WHERE condition

this is the syntax for updating in sql, try deleting the ``

answered Dec 07 '16 at 09:53

salifkeita

52
5

6

The backticks indicate a column or database, they are valid as they are in OPs code. – Qirel Dec 07 '16 at 09:54
well , sorry , didn't know that – salifkeita Dec 07 '16 at 09:55
You'll never stop learning either ;-) Visit this thread for more info on the matter: http://stackoverflow.com/q/11321491/4535200 – Qirel Dec 07 '16 at 09:56
Thanks you so much this is helpful – salifkeita Dec 07 '16 at 10:48
It's probably worth deleting this answer – Synchro Dec 08 '16 at 07:13

Update query not working PHP MySQL

2 Answers2