php - mysql更新多行中的多列

Question

我正在尝试更新表中的行和列中的多个数据，其中 client_id = '5' 且发票_number = '11'

我的表tbl_pspecials

我的代码:

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

我的 PHP 脚本:

<?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>";


} ?>

现在脚本不起作用，出现以下错误:

'( date_from='newvalue', date_to='newvalue', car_no='newvalue' � la ligne 1 语法错误

Answer 1

您收到的语法错误是由于 SET 关键字后面的“(”符号造成的。它不允许在一个查询中使用多个 SET 或 WHERE 设置多个语句 ( MySQL Reference )。

可以将条件表达式放入每个字段并连接所有收到的 id ( some example ) 的查询字符串。但这确实太多了 - 每个字段都需要一个额外的循环，并且需要复杂的代码来格式化此查询(考虑将来编写、调试、审查和支持它)。