Fetch sum of stockqty from a table?

[Ronel]

 $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?

[ginerjm]

Maybe show us the other table as well?

[Ronel]

[ginerjm]

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 March 6, 2022 by ginerjm

[Ronel]

purchaseid is from purchaseorder table id. Do i need to rename the id of purchaseorder table to purchaseid?

Edited March 6, 2022 by Ronel

[ginerjm]

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 March 6, 2022 by ginerjm

[Ronel]

there is no amount for the purchase but instead i only want to sum the value of stockqty from stock and view it in my view page

[ginerjm]

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 March 6, 2022 by ginerjm

[Ronel]

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

[ginerjm]

You're trying to do a fetch but you never check if the query actual ran.

[Ronel]

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");

 

[ginerjm]

You need to group by all of the selected fields (never use *).  That query is probably not running either since it is not grouping all the un-summed fields.

[Ronel]

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 

[Ronel]

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>

 

[ginerjm]

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.

[Phi11W]

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.

 

[Ronel]

thanks a lot

[ginerjm]

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.

[Ronel]

ok 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 

 

Edited March 9, 2022 by Ronel

[ginerjm]

What is the problem?  And why all of this code?

[Ronel]

Sir the problem is all i wanted was update the datas being fetched from database and update it like remarks and stockqty from stock and stock_orders

[ginerjm]

Huh?

[ginerjm]

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.