mysql update multiple column in multiple rows

Question

i am trying to update multiple data in both rows and columns in my table where client_id = '5' and invoice_number = '11'

My Table tbl_particulars

My code:

         <table class="table table-bordered">
            <tr>
              <th>Date</th>
              <th>Car No.</th>
              <th>Particulars</th>
              <th>Rate</th>
              <th>Amount</th>
            </tr>
            <?php

         
    $sql = "SELECT * FROM tbl_particulars WHERE client_id = '5' AND invoice_number = '11'";
    $query = mysql_query($sql);
    while($row = mysql_fetch_assoc($query)) {
    ?>
            <tr>
              <td width="200px" nowrap="nowrap"><table>
                  <tr>
                    <td>From<br>
                      <input type="hidden" name="numbers" value="3">
                      <input type="hidden" name="ids[]" value="<?php echo $row['id'] ?>">
                      <input type="text" name="date_from[]" value="<?php echo $row['date_from'] ?>" class="form-control" placeholder="DD/MM/YYYY"></td>
                    <td>&ensp;</td>
                    <td>To<br>
                      <input type="text" name="date_to[]" value="<?php echo $row['date_to'] ?>" class="form-control" placeholder="DD/MM/YYYY"></td>
                  </tr>
                </table></td>
              <td width="100px"><input type="text" name="car_no[]" value="<?php echo $row['car_no'] ?>" class="form-control"></td>
              <td width="250px"><table>
                  <tr>
                    <td><select name="particulars[]" class="form-control">
                        <option value="">--Please Select ur Duty--</option>
                        <option value="Local 80 Kms. & 8.00 Hrs. Duty" <?php if($row['particulars']=="Local 80 Kms. & 8.00 Hrs. Duty") echo 'selected="selected"'; ?> >Local 80 Kms. & 8.00 Hrs. Duty</option>
                        <option value="Upto" <?php if($row['particulars']=="Upto") echo 'selected="selected"'; ?>>Upto</option>
                        <option value="Extra Kms." <?php if($row['particulars']=="Extra Kms.") echo 'selected="selected"'; ?>>Extra Kms.</option>
                        <option value="Extra Hrs." <?php if($row['particulars']=="Extra Hrs.") echo 'selected="selected"'; ?> >Extra Hrs.</option>
                        <option value="Local Nights" <?php if($row['particulars']=="Local Nights") echo 'selected="selected"'; ?> >Local Nights</option>
                        <option value="Parking Toll Tax" <?php if($row['particulars']=="Parking Toll Tax") echo 'selected="selected"'; ?> >Parking Toll Tax</option>
                        <option value="Out Station" <?php if($row['particulars']=="Out Station") echo 'selected="selected"'; ?> >Out Station</option>
                      </select></td>
                    <td style="vertical-align:text-top !important">&ensp; @ &ensp; </td>
                    <td width="100px"><input type="text" value="<?php echo $row['quantity'] ?>" name="quantity[]" class="form-control"></td>
                  </tr>
                </table></td>
              <td width="80px"><input type="text" name="rate[]" class="form-control" value="<?php echo $row['rate'] ?>" onChange="CalcGST()"></td>
              <td width="80px"><input type="text" name="amount[]" class="form-control" value="<?php echo $row['amount'] ?>" onChange="CalcGST()"></td>
            </tr>
            <?php } ?>
            <tr>
              <td colspan="5"><button type="submit" name="update_invoicee" class="btn btn-info">Save & Continue</button></td>
            </tr>
          </table>
        </div>
      </form>

My Php Script:

<?php if(isset($_POST['update_invoicee'])){

$s = "UPDATE `tbl_particulars` SET";
 for($i=0;$i<$_REQUEST['numbers'];$i++)
 {
  $s .="(
  date_from='".$_REQUEST['date_from'][$i]."',
  date_to='".$_REQUEST['date_to'][$i]."',
  car_no='".$_REQUEST['car_no'][$i]."',
  particulars='".$_REQUEST['particulars'][$i]."',
  quantity='".$_REQUEST['quantity'][$i]."',
  rate='".$_REQUEST['rate'][$i]."',
  amount='".$_REQUEST['amount'][$i]."' WHERE id='".$_REQUEST['ids'][$i]."'),";
 }
 $s = rtrim($s,",");
 if(!mysql_query($s))
  echo mysql_error();
 else
  echo "<script>alert('Records Updated')</script>";


} ?>

now script does not work gives below error:

Erreur de syntaxe pr�s de '( date_from='newvalue', date_to='newvalue', car_no='newvalue' � la ligne 1

1. **Don't** use the **deprecated and insecure** `mysql_*`-functions. They have been deprecated since PHP 5.5 (in 2013) and were completely removed in PHP 7 (in 2015). Use MySQLi or PDO instead. 2. **You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php)** and should really use [Prepared Statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead of concatenating your queries, which can be used if you use the above mentioned MySQLi or PDO. — M. Eriksson, Aug 20 '17 at 09:43

score 1 · Answer 1 · answered Aug 20 '17 at 12:17

The syntax error you received is due to "(" symbol after the SET keyword. It does not allow set multiple statements with more than one SET or WHERE in one query (MySQL Reference).

It is possible to put conditional expressions to every field and concatenate query string for all received ids (some example). But it will be really too much - one additional loop for every field and complex code to format this query (think about writing, debugging, reviewing and supporting it in future).

mysql update multiple column in multiple rows

1 Answers1