mysql更新多行中的多列

Question

我正在尝试更新表中client_id = '5‘和invoice_number = '11’的行和列中的多个数据

我的表tbl_particulars

我的代码：

​

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


} ?>

​

现在脚本不工作，给出以下错误：

错误语法pr�s de '( date_from='newvalue'，date_to='newvalue'，car_no='newvalue‘�la ligne 1

Answer 1

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

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