0

I have the code below but it does not do what i want it to do.

"UPDATE products SET quantity = quantity - 1 WHERE  product = $'product'";

I have a data base called products with field quantity, now i do not know how to code such that the value stored in quantity will be decremented by number 1(one) or any other value of sold goods. I am trying to code a point of sale system for myself.
how do i fix it?

And how can i code it such that i can select multiple products and find their sum? i am able to do it for just one item at a time (assume i am selling two different products at the same time). the code i have for one item is below;

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>sale</title>

<?php

if (isset($_POST['GetPrice']))
    {
    //make connection to the database
    $connect = mysql_connect("localhost", "root","");
    if (!$connect)
    {
       die("database connection failed". mysql_error());
    }
    //make sure we’re using the right database
    $select_db = mysql_select_db("lero_med");
    if (!select_db)
    {
      die("database selection failed " .mysql_error());
    }
    
  		$productQuantity= trim($_POST['productQuantity']);
    	$product = trim($_POST['product']);
    	$total = 0;
		$query = "SELECT price FROM products WHERE product = '$product'";
		
		
		$result2 = mysql_query($query2);
    	$result = mysql_query($query);
    	
      
} 
?>

<style type="text/css">
.auto-style1 {
	margin-left: 16px;
}
.auto-style2 {
	margin-left: 160px;
}
.auto-style3 {
	margin-left: 20px;
}
.auto-style4 {
	text-align: right;
}
</style>

</head>

<body>

<div class="auto-style4">

<form method="post" action="sell.php">
	<fieldset name="Price" style="width: 448px; height: 164px">
	<legend>Get Product Price</legend>
		<br />
		<select name="product" multiple="multiple" style="height: 23px; width: 117px;" class="auto-style3">
		 		<option value="noSelection" selected="selected"> No Selection </option>
				<option value="Panado"> Panado </option>
				<option value="Compral"> Compral</option>
				<option value="Peniciline"> Peniciline </option>
				<option value="Granpar"> Granpar </option>
        </select>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        
        Quantity:
        <input name="productQuantity" type="text" class="auto-style1" /> <br />
	    <br />
	    <br />

        <input name="GetPrice" type="submit" value="Price?" class="auto-style2" />
        <br/>
        
        <?php
      	while ($row = mysql_fetch_array($result))
        {
       		extract($row);
	   		echo "Price per unit of " .$product . "  is  M " .$price ."<br/>";
	   		
	   		 
	    }
	    

	    $subtotal = ($price * $productQuantity);
	    
	    if($productQuantity > -1)
	   		{
	   			echo "Price of " . $productQuantity ." ".$product . "  is    M" .sprintf("%0.2f", $subtotal ). "<br/>";
	   			 //$result2 = mysql_query($query2);
	   		}
	   		
	   		"UPDATE products SET quantity = quantity - 1 WHERE  product = $'product'";
	   		
        

        ?>
		
	</fieldset>
</form>

</div>

</body>

</html>
3
Contributors
3
Replies
5
Views
5 Years
Discussion Span
Last Post by hari.sarvothama
0

The dollar sign has to be after the single quote, not before.

mysql_query("UPDATE products SET quantity = quantity - 1 WHERE  product = '$product'";
0

sorry i posted the one i was using for trial and error, this is the one i have completed, the questions are placed as comments for what i had wanted or expected.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>sale</title>

<?php

if (isset($_POST['GetPrice']))
    {
    //make connection to the database
    $connect = mysql_connect("localhost", "root","");
    if (!$connect)
    {
       die("database connection failed". mysql_error());
    }
    //make sure we’re using the right database
    $select_db = mysql_select_db("lero_med");
    if (!select_db)
    {
      die("database selection failed " .mysql_error());
    }
    /*
    	get values form HTML
    */
    
  		$productQuantity= trim($_POST['productQuantity']);
    	$product = trim($_POST['product']);
    	$total = 0;
    	
    	/*
    		call up the price of the product from the database and store in $query
    		p.s this works fine
    	*/ 
		$query = "SELECT price FROM products WHERE product = '$product'";
		
		
		/*
    		change the value of quantity in the database by decrementing it by $productQuantity 
    	*/
		$query2 = "UPDATE product SET quantity = quantity - ($productQuantity) WHERE product = '$product'";
		
		/*
		   not sure what this do but saw it in a number of books; assumed its a function and the value returned will be stored
		   in the php variables
		*/
		$result2 = mysql_query($query2);
    	$result = mysql_query($query);
    	
      
} 
?>

<style type="text/css">
.auto-style1 {
	margin-left: 16px;
}
.auto-style2 {
	margin-left: 160px;
}
.auto-style3 {
	margin-left: 20px;
}
.auto-style4 {
	text-align: right;
}
</style>

</head>

<body>

<div class="auto-style4">

<form method="post" action="sell.php">
	<fieldset name="Price" style="width: 448px; height: 164px">
	<legend>Get Product Price</legend>
		<br />
		<select name="product" multiple="multiple" style="height: 23px; width: 117px;" class="auto-style3">
		 		<option value="noSelection" selected="selected"> No Selection </option>
				<option value="Panado"> Panado </option>
				<option value="Compral"> Compral</option>
				<option value="Peniciline"> Peniciline </option>
				<option value="Granpar"> Granpar </option>
        </select>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        
        Quantity:
        <input name="productQuantity" type="text" class="auto-style1" /> <br />
	    <br />
	    <br />

        <input name="GetPrice" type="submit" value="Price?" class="auto-style2" />
        <br/>
        
        <?php
        
        /*
        	go through the table and print the value of the variable $result
        */
      	while ($row = mysql_fetch_array($result))
        {
       		extract($row);
	   		echo "Price per unit of " .$product . "  is  M " .$price ."<br/>";
	   		
	   		 
	    }
	    

	    $subtotal = ($price * $productQuantity);
	    
	    /*
	    	if there is anything purchased, find the subtotal
	    */
	    if($productQuantity > -1)
	   		{
	   			echo "Price of " . $productQuantity ." ".$product . "  is    M" .sprintf("%0.2f", $subtotal ). "<br/>";
	   			
	   			/*
	   			   this is where i would like to then decrement the value but do not know how,  i am checking is the value
	   			    has been decremented on the command window of Mysql. hope i am going about ithe right way and that what
	   			    i want to do is clear. if there is a better way of doing something else i would appreciate the heads-up
	   			*/
	   		}
	   		
        ?>
		
	</fieldset>
</form>

</div>

</body>

</html>
0

Hey, i have some confusion about your code: You seem to have created an update query (in line 42 and executed it (in line 48). Why do you want it to do another update at line number 120.
Anyway, Echo the $result2 variable and check whether if it says true or false.
Also, try echoing the $query variable, copy what it prints in ur page and try running it manually in ur db and see if that works. If it does it means the query is right which i believe is.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.