Jump to content

Fetch sum of stockqty from a table?


Ronel
 Share

Go to solution Solved by Ronel,

Recommended Posts

 $query = mysqli_query($conn, "select SUM(stockqty),itemname,status,ponum from purchaseorder inner join stock on purchaseorder.id=stock.purchaseid group by ponum,stockqty,itemname,status ");

Below is my stock table where i have purchaseid from purchaseorder table kindly help me how to fetch datas from both the tables and then retrieve sum of stockqty from stock table?

Untitled.jpg

Link to comment
Share on other sites

Are the 2 id columns the same? Or is it purchaseid and ponum that are the same?  YOu need to match the true related key values in order to get the amount in stock and the amounts sold and get the inventory left.

Basically what 2 columns tie the inventory amounts to the purchases from that inventory?

Edited by ginerjm
Link to comment
Share on other sites

You've shown us a a (1) stock table which I assume is your inventory and a (2) table of po's but which has no count in it.  To join them I think you need to connect purchaseid to id.  Correct?  But where is the amount of items purchased?

Why does your stock table have 2 items named 'Pen' with unique id's?  And your naming of two columns as 'id' is a bad practice since they apparenty are not the SAME value.  Give them unique names if they are not the same values in your appl

Edited by ginerjm
Link to comment
Share on other sites

You made it sound like you wanted data from both tables.  Now you are saying otherwise.  So your initial post was pretty close.

$q = "select itemname, status, SUM(stockqty) as total_qty from stock
	order by itemname 
	group by itemname,status";

 

Edited by ginerjm
Link to comment
Share on other sites

no sir i want datas from both tables as well as the sum of stockqty from stock table!

this is what i did.

 $query = mysqli_query($conn, "select *, SUM(stockqty) as total_qty from purchaseorder inner join stock on purchaseorder.id=stock.purchaseid order by itemname group by itemname");
        
  while($row=$query->fetch_array())
			

and it throws me an error Fatal error: Uncaught Error: Call to a member function fetch_array() on bool in C:\xampp\htdocs\Stock Manager updated today\Stock Manager\html\ltr\view.php:34 Stack trace: #0 {main} thrown in C:\xampp\htdocs\Stock Manager updated today\Stock Manager\html\ltr\view.php on line 34

Link to comment
Share on other sites

Sir when i change the code into this it runs but i don't get the sum of mystockqty from stock table

 $query = mysqli_query($conn, "select *, SUM(stockqty) from purchaseorder inner join stock on purchaseorder.id=stock.purchaseid group by itemname");

 

Link to comment
Share on other sites

Sir getting confused what i have done wrong

 $query = mysqli_query($conn, "select ponum, status, itemname, SUM(stockqty) as total_qty from purchaseorder inner join stock on purchaseorder.id=stock.purchaseid group by itemname");
        
<td><?php echo $row['ponum']?></td>
                <td><?php echo $row['itemname']?></td>
                <td><?php echo $row['0']?></td>
                <td><?php echo $row['status']?></td>

Below is the result 

result.jpg

Link to comment
Share on other sites

  • Solution

okay thanks alot sir finally found this 

<td><?php echo $row['ponum']?></td>
                <td><?php echo $row['itemname']?></td>
                <td><?php echo $row['total_qty']?></td>
                <td><?php echo $row['status']?></td>

 

Link to comment
Share on other sites

So - you're happy now?

A suggested correction to how you are coding:

echo "<table>";
echo "<tr>
	<th>PO Number</th>
	<th>Item Name</th>
	<th>Stockin Qty</th>
	<th>Status</th>
	</tr>";
while($row = $query->fetch_array())
{
	echo "<tr>
	<td>{$row['ponum']}</td>
	<td>{$row['itemname']}</td>
	<td>{$row['total_qty']}</td>
	<td>{$row['status']}</td>
	</tr>";
}
echo "</table>";	

Much cleaner when one stops entering and exiting php mode.

  • Like 1
Link to comment
Share on other sites

14 hours ago, Ronel said:
select ponum, status, itemname, SUM(stockqty) as total_qty from purchaseorder inner join stock on purchaseorder.id=stock.purchaseid group by itemname

In almost every DBMS by default, and MySQL if you configure it properly, this query will be flatly rejected as an error. 

What values of ponum and status would you expect to be returned when you are only grouping by itemname? 

The fields that you select either must be included in the "group by" clause or must be used in aggregate functions, like SUM. 

 

Suppose you had a number of purchase orders - what output would you expect to see for these "extra" fields? 

+-------+------------+------------+-----+ 
| ponum | status     | itemname   | qty | 
+-------+------------+------------+-----+
|   111 | Complete   | Keyboard   |   1 |
|   222 | Complete   | Keyboard   |   2 |
|   222 | Complete   | Mouse      |   1 |
|   333 | InProgress | Keyboard   |   1 |
|   333 | InProgress | Mouse      |   1 |
|   333 | InProgress | Chair      |   1 |
+-------+------------+------------+-----+

select itemname, ponum, status, sum(qty) 
from ... 
group by itemname ; 

+----------+-------+------------+----------+
| itemname | ponum | status     | sum(qty) |
+----------+-------+------------+----------+
| Chair    |   333 | InProgress | 1        |
| Keyboard |   ??? | ???        | ???      | 
| Mouse    |   ??? | ???        | ???      | 
+----------+-------+------------+----------+

If you can't tell the query which value you want within the grouping, the query should give up and throw an error. 

MySQL is one of the few DBMS that does not do this by default. 

Regards, 
   Phill  W.

 

  • Like 1
Link to comment
Share on other sites

Not sure what you are thanking this forum for.  You haven't acknowledged the last two posts to let us know what you think of them nor to question about the errors being pointed out in your supposedly working query.

We are all happy to help out people here but a two-way conversation is vital to getting you onto the right track.

Link to comment
Share on other sites

Posted (edited)

save.png.570d8a11818ca2f62377d5aecff4dcdb.pngUntitled.thumb.png.cd479c102ccc4d30130c7a6f4d3cc7e7.pngok sir can you help me edit my items i am still stuck here!!! 

this is what ia was trying to do but cannot get my head around!!

<?php 
$con = new mysqli("localhost","root","","loginsystem");

$errors = array();
if($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['updatestock'])):
/*
echo "<pre>";
print_r($_POST);	 
echo "</pre>";
*/
	$fields = array(	
		 "itemname" => "Item Name"
	    ,"purchaseid" => "Purchase ID"	
	    ,"poqty" => "Purchase Order QTY"
	    
	); 
	
	foreach($fields as $field => $label):
		if(empty($_POST[$field])): 
			array_push($errors,"Please Enter ".$label);
		endif;
	endforeach;
	
	//if(empty($errors)):
		//$sqlpurchaseid = "SELECT `id` FROM `stock_orders` WHERE purchaseid = ? LIMIT 1"; 
		//$querypurchaseid = $con->prepare($sqlpurchaseid);	
		//$querypurchaseid->bind_param("i", $_POST['purchaseid']); 
		//$querypurchaseid->execute(); 
		//$resultpurchaseid = $querypurchaseid->get_result();	
		//$rowpurchaseid = $resultpurchaseid->fetch_assoc();

		//if(!empty($rowpurchaseid)){ 
		//	array_push($errors,"This Purchase ID already exists");
		//}
	//endif;		
	
	
	if(empty($errors)):	
	
		$today = date('Y-m-d');
		
		$sqlstockorder = "INSERT INTO `stock_orders`(`stock_id`, `purchaseid`, `poqty`, `entrydate`) 
		VALUES (?,?,?,?)";
		
		$querystockorder = $con->prepare($sqlstockorder);		
		$querystockorder->bind_param("iiis", $_POST['id'], $_POST['purchaseid'], $_POST['poqty'], $today); 		
		$querystockorder->execute();
	
	
		$sqlstock = "UPDATE `stock` SET 
		`stockqty` =  ABS(COALESCE(stockqty,0)+?)		
		WHERE `id` = ?";
		$querystock = $con->prepare($sqlstock);		
		$querystock->bind_param("ii", $_POST['poqty'], $_POST['id']); 		
		$querystock->execute();
		$num = $con -> affected_rows;			
		$message = (!empty($num) ? "Stock Updated" : 'No Changes Made');	
		
		header("refresh: 3; URL=edit.php");
	endif;
	
endif;

$sql = "SELECT `itemname` FROM `stock`";
$res = mysqli_query($con, $sql);
?>


<!DOCTYPE html>
<html>
<head>
    <title></title>
    <script type="text/javascript" src="fetch.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"></script>
    </head>
<body>
	
        
<?php	

if(!empty($message)):
	echo '<div style="width:500px; text-align: center; margin:20px auto;">'."\r";
		echo '<span style="color:green"><b>'.$message.'</b></span></br >'."\r";
	echo '</div>'."\r";
endif;
if(!empty($errors)):
	echo '<div style="width:500px; text-align: center; margin:20px auto;">'."\r";
	foreach($errors as $error):
		echo '<span style="color:red">'.$error.'</span></br >'."\r";
	endforeach;
	echo '</div>'."\r";
endif;



			
if($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['itemname'])){

		echo '<form action="" method="post">
			<table class="table table-bordered">
				<thead class="table-primary" style="white-space:nowrap;">
				<th>Stock ID</th>	 
				<th>Item Name</th>
				<th>Purchase ID</th>
				<th>P.O. Quantity</th>				
				<th>Update Stocks</th>
				</thead>'."\r";	
		
			$sql = "select `id`, `itemname` from stock where itemname = ? "; 
			$query = $con->prepare($sql);		
			$query->bind_param("s",$_POST['itemname']); 		
			$query->execute();
			$result = $query->get_result();
			while($rows = $result->fetch_assoc()){	
			
				$purchaseid = (!empty($_POST['purchaseid']) ? $_POST['purchaseid'] : '');
				$poqty = (!empty($_POST['poqty']) ? $_POST['poqty'] : '');
			
				echo '<tr>
					<td><input style="width:100%" type="text" name="id" value="'.$rows['id'].'" readonly /></td>
					<td><input style="width:100%" type="text" name="itemname" value="'.$rows['itemname'].'" readonly /></td>
					<td><input style="width:100%" type="text" name="purchaseid" value="'.$purchaseid.'" /></td>
					<td><input style="width:100%" type="text" name="poqty" value="'.$poqty.'" /></td>					
					<td><input style="width:100%" type="submit" name="updatestock" value="Update" /></td>
				</tr>'."\r";
			}
		echo '</table>
	</form>'."\r";
	         
}else{ 
	echo '<b class="d-inline p-2 bg-primary text-white">Update Stock Here</b><hr>    
	<b>Select Item to update :</b> 
    <select id="itemname" onchange="selectItem()">
        <option value="Select Item here" selected>Select Item here</option>'."\r";
	
		while ($rows = mysqli_fetch_array($res)){		 
			echo '<option value="'.$rows['itemname'].'">'.$rows['itemname'].'</option>'."\r";
		}
		   
    echo '</select><br><br>'."\r";
	echo '<div id="ans"></div>';
}
?>
	
	<script type="text/javascript">
		function selectItem(){
			    var x = document.getElementById("itemname").value;
			    
			    $.ajax({
			       url:"edit.php",
			        method: "POST",
			        data:{
			            itemname : x
			        },
	        success:function(DataView){
	        $("#ans").html(DataView);
	    }
  
});
		}
	</script>
    </body>
</html>

below are my two tables 

save.png

 

Edited by Ronel
Link to comment
Share on other sites

Ok - so tell us what you want to do.  What table do you want to update?  What columns do you want to change? What is the record key that selects the record to be updated?  From that we can write a query statement for you.  But - you should also be able to write it if you can answer all of my questions.  :)

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.