Member Avatar for liphoso

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
  • 139 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by hari.sarvothama
Member Avatar for pritaeas

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

mysql_query("UPDATE products SET quantity = quantity - 1 WHERE  product = '$product'";
Member Avatar for liphoso

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>
Member Avatar for hari.sarvothama

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.

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.