hi,

looking for some tips, using dreamweaver as my tool and wondering if someone could help.

i basically have 2 tables. 1 which is partsused and the other stocklevels.

the stocklevels contains stock details and also if it is in the van or unit.

on my page i have an insert form which inserts into the partsused table the quantity used along with the partid from stocklevels table.

this works fine, however i need to trigger from the insert button an update which updates the stocklevels table by deducting the quantity used in the insert form for partsused.

can anyone show me a sample code of how this kind of update trigger would work?

thanks again

You can just call an update at the same time as you call the insert. Something like this?

mysql_query("INSERT INTO partsused (bin1, bin2, bin3) VALUES ('$thing1','$thing2', '$thing3)");		
mysql_query("UPDATE stocklevels SET somebin1 = '$thing1', somebin2 = '$thing2', somebin3 = '$thing3'WHERE id = '$id' AND van = '$van'");

thanks for your help, how do you think i should store the stock?

i.e. there are unit stock and van stock? should this be stored in one table or two?

thanks again

one table,
I hope you have unitmaster table. whose pk is related in parts table say unitid, because each part may be having differenct unit id

Stock update may have one more problem
If you allow user to change or delete record, that should also change stock levels

thanks, because the van stock comes from the userid session variable i was thinking of having 2 tables like this:

van stock tbale

VanStockID
Quantity
Stock_CategoryID
Description
PartNumber
Users_UserID


unitstock table

UnitStockID
quantity
Stock_CategoryID
Description
PartNumber


however i am unsure how i should store this in the parts used table which has to deduct the quantity from the vanstock or unit stock in an update form?

thanks again for your help

this is what i am trying now, ignore the post above. structure should be:

Stock Table

StockID Stock_CategoryID Description PartNumber

van stock tbale

VanStockID
Quantity
Stock_StockID (FK to Stock Table)
Users_UserID


unitstock table

UnitStockID
quantity
Stock_StockID (FK to stock table)


php code:

<?php
mysql_select_db($database_local, $local);
if(isset($_POST['StockFromVan'])){	$van=$_POST['PartQuantity'];	
$stock=$_POST['Stock_StockID'];
$query="update VanStock set Quantity=Quantity-$van where Stock_StockID=$stock";	
mysql_query($query) or die("Cannot update");
}
if(isset($_POST['StockFromUnit'])){	$unit=$_POST['PartQuantity'];	
$stock=$_POST['Stock_StockID'];
$query="update UnitStock set Quantity=Quantity-$unit where Stock_StockID=$stock";	
mysql_query($query) or die("Cannot update");
}
?>

my form:

<form method="post" id="form2" name="form2" class="clear" action="post">
<tr>
                <td class="KT_th"><label for="PartQuantity">Quantity:</label></td>
                <td><input type="text" name="PartQuantity" id="PartQuantity" value="<?php echo KT_escapeAttribute($row_rspartsused['Quantity']); ?>" size="32" />
                  <?php echo $tNGs->displayFieldHint("Quantity");?> <?php echo $tNGs->displayFieldError("partsused", "Quantity"); ?></td>
              </tr>
              <tr>
              
           <td class="KT_th"><label for="PartFrom">Part From:</label></td>
             
              <td>
             
             Unit:<input name="StockFromUnit" id="StockFromUnit" type="checkbox" value="2" />
              Van:<input name="StockFromVan" id="StockFromVan" type="checkbox" value="1" />
              </td>
              
              </tr>
              <tr>
                <td class="KT_th"><label for="stock_StockID">Part Used:</label></td>
                <td><select name="stock_StockID" id="stock_StockID">
                  <?php 
do {  
?>
                  <option value="<?php echo $row_rsStock['StockID']?>"<?php if (!(strcmp($row_rsStock['StockID'], $row_rspartsused['stock_StockID']))) {echo "SELECTED";} ?>><?php echo $row_rsStock['Description']?></option>
                  <?php
} while ($row_rsStock = mysql_fetch_assoc($rsStock));
  $rows = mysql_num_rows($rsStock);
  if($rows > 0) {
      mysql_data_seek($rsStock, 0);
	  $row_rsStock = mysql_fetch_assoc($rsStock);
  }
?>
                </select>
                  <?php echo $tNGs->displayFieldError("partsused", "stock_StockID"); ?></td>
              </tr>
  <tr class="KT_buttons">
                <td colspan="2"><input type="submit" name="KT_Insert1" id="KT_Insert1" value="Insert record" /></td>
              </tr>
            </table>
                   </form>

however i seem to be getting the cannot update error.

any ideas why?

thanks again anyone for any help.

Try

$query="update VanStock set Quantity=Quantity-'$van' where Stock_StockID='$stock'";

and check the variables inserted in the queries throughout the code.
You can also try putting the query into phpMyAdmin (SQL tab) and instead of using variables insert actual values from the database. See what is displayed. This will give you a handle on checking your query. The "or die" statement doesn't tell you much.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.