help with basic query

[gammaman]

what is wrong with this Update query

 

<?php

  $conn=mysql_connect("localhost","fierm","13183");

  if(!$conn){
   echo "failed";
}else{
   mysql_select_db(fierm);

   $Amt=$_POST['amt'];
   $Item=$_POST['new'];
   
   echo "$Amt";
   echo "$Item";

   $result=mysql_query("select InventoryLevel,ProductID from Inventory where ProductID='$Item' and '$Amt' < InventoryLevel");  
   $cou=mysql_num_rows($result);
   echo "$cou";

   if ($cou > 0)
   {
     mysql_query("Update table Inventory set InventoryLevel = InventoryLevel-'$Amt' 
                   Where ProductID='$Item'"); 

   }



}

?>

[BlueSkyIS]

table shouldn't be in the update query.

 

by the way, php/mysql will give you a clue about the error if you use or die(mysql_error()):

 

mysql_query($some_sql) or die(mysql_error() . "  $some_sql");

[Fadion]

First u can echo variable without double quotes: echo $amt;

 

Second, as stated above theres no need to: "UPDATE table Inventory", just "UPDATE Inventory".

 

Third im not sure about "set InventoryLevel = InventoryLevel-'$Amt'". U should calculate the difference in php and then update the db.

[gammaman]

You are right, I can't believe I missed that one.

 

Will something like I have in the clause of this if stament work.

<?php

 $conn=mysql_connect("localhost","fierm","13183");

 if(!$conn){
  echo "failed";
}else{
  mysql_select_db(fierm);

  $Amt=$_POST['amt'];
  $Item=$_POST['new'];
  
  echo "$Amt";
  echo "$Item";

  $result=mysql_query("select InventoryLevel, ProductID from Inventory where ProductID='$Item'");  
  $cou=mysql_num_rows($result);
  
  echo "$cou";

  if (InventoryLevel > $Amt )
  {
    echo "<br/>";
    echo "1";
    mysql_query("Update  Inventory set InventoryLevel = InventoryLevel-'$Amt' 
                  Where ProductID='$Item'"); 

  }



}
?>

[BlueSkyIS]

what is InventoryLevel? there is no $ in front, so it must be a defined constant.

[gammaman]

InventoryLevel is one of the fields of my table.