php - 如何使用php更新mysql中相同的项目名称数量

Question

当我从公司得到一个项目时，我在我的 PHP 表单中输入所有项目的详细信息并将其保存在 MySQL 中

MySQL中有两张表一个是收到，第二个是库存

所以当我得到一个项目时，它保存在两个表中接收和库存

但有时库存(表格)项目是相同的，所以我想更新如果项目名称和公司相同，那么它只会改变数量

一段时间后它的新项目就会正常保存

所以我该怎么做请帮我解决这个问题谢谢

  mysql_query("INSERT INTO  receive SET date='$date',company='$company',itemname='$itemname',quantity='$quantity',category='$category',signature='$signature'");

    $result = mysql_query("INSERT INTO stock SET date='$date',company='$company',itemname='$itemname',quantity='$quantity',category='$category',signature='$signature'")

这是我的完整脚本

 // creates the new record form
 // since this form is used multiple times in this file, I have made it a function that is easily reusable
 function renderForm($id ,$date ,$company,$itemname,$quantity,$category,$signature,  $error)

 {
 ?>
 <form id="searchform" action="" method="post" enctype="multipart/form-data">
  <div align="center">
 <fieldset>

   <div align="center">
     <legend align="center" >Stock Receive!</legend>
   </div>
   <div class="fieldset">
     <p>
   <label class="field" for="date">Date: </label>

       <input name="date" type="text" class="tcal" value="<?php echo date("Y-m-d");; ?>" size="30"/>
     </p>
               <p>
       <label class="field" >Company :</label>
       <input  name="company" type="text" id="company" value="<?php echo $company; ?>"  size="30"/>



     </p> 
   <p>
     <label class="field" for="item">Item: </label>

     <input name="itemname" type="text"  id="itemname" value="<?php echo $itemname; ?>" size="30"/>
   </p>
      <p>
       <label class="field" >Quantity :</label>
       <input  name="quantity" type="text"  id="quantity" value="<?php echo $quantity; ?>"  size="30"/>
     </p> 
       <p>
       <label class="field" >Category :</label>
       <input  name="category" type="text" id="category" value="<?php echo $category; ?>"  size="30"/>



     </p> 
   <p>
     <label class="field" for="username">Signature : </label>

     <input name="signature" type="text"  id="signature" readonly  value="<?php echo $_SESSION['SESS_FIRST_NAME']; ?>">
   </p>




  </div>
 </fieldset>
   <p align="center" class="required style3">Please Fill The Complete Form </p>
   <div align="center">
     <input name="submit" type="submit" class="style1" value="Submit">

   </div>
 </form> 


 <?php 
 // if there are any errors, display them
 if ($error != '')
 {
 echo '<div style="padding:4px; border:1px solid red; color:red;">'.$error.'</div>';
 }
 ?> 




     <?php
     }

         $itemname = $_GET['itemname'];

         // connect to the database
         include 'connect-db.php';

         // check if the form has been submitted. If it has, start to process the form and save it to the database
         if (isset($_POST['submit']))
         {
         // get form data, making sure it is valid
         $id = mysql_real_escape_string(htmlspecialchars($_POST['id']));
         $date = mysql_real_escape_string(htmlspecialchars($_POST['date']));
         $company = mysql_real_escape_string(htmlspecialchars($_POST['company']));
         $itemname = mysql_real_escape_string(htmlspecialchars($_POST['itemname']));
         $quantity = mysql_real_escape_string(htmlspecialchars($_POST['quantity']));
         $category = mysql_real_escape_string(htmlspecialchars($_POST['category']));
         $signature = mysql_real_escape_string(htmlspecialchars($_POST['signature']));


             // check to make sure both fields are entered
             if ($date == '' || $quantity == '')
             {
             // generate error message
             $error = 'ERROR: Please fill in all required fields!';

             // if either field is blank, display the form again
             renderForm($id ,$date ,$company,$itemname,$quantity,$category,$signature,  $error);

             }
             else
             {
             // save the data to the database
              mysql_query("INSERT INTO  receive SET date='$date',company='$company',itemname='$itemname',quantity='$quantity',category='$category',signature='$signature'");

    $result = mysql_query("INSERT INTO stock SET date='$date',company='$company',itemname='$itemname',quantity='$quantity',category='$category',signature='$signature'")

                or die(mysql_error()); 
             echo "<center>Recive  Complete!</center>";
             // once saved, redirect back to the view page

             }
         }else
         // if the form hasn't been submitted, display the form
         {
         renderForm('','','','','','','','','','');
         }



    ?>

Answer 1

INSERT INTO stock SET
date='$date',company='$company',itemname='$itemname',quantity='$quantity',
category='$category',signature='$signature' 
ON DUPLICATE KEY UPDATE quantity='$quantity'

或者做

...ON DUPLICATE KEY UPDATE quantity='$quantity'+quantity 

如果您想增加现有数量。

之后您将唯一的复合键放在公司和项目名称上，以便触发重复条件。

ALTER IGNORE TABLE stock ADD UNIQUE someName (company, itemname);