Unable to sum certain values

tedi · June 24, 2021, 9:59am

so this is the form there is the field Total and i want when i push the button NEXT it must show me in the textbox the combined sum of all Total field

   elseif (isset($_POST['buton']))
{
        $sql="SELECT  SUM(income.count*menu.price) as TotalPrice 
        FROM income, menu  WHERE income.name = menu.name  and income.amount = menu.amount ";
        $quer=mysqli_query($conn,$sql) or die ('error sql');
}

this happen when i push the button NEXT

$sql… when i put the command in SQL tab it work is show me the right answer

<input type='submit' name='buton' value="NEXT"/>
                        <?php
                            if(isset($_POST['buton'])) $resul=mysqli_fetch_array($quer);
                        ?>
                        <td><input class="totPro" type ="text" value="<?php if(isset($_POST['buton'])) 
                         echo $resul['TotalPrice'];?>"  disabled=""/> </td>

ErnieAlex · June 24, 2021, 10:30pm

Just do it in the line-by-line display. Get the row of data along with the sum and display it. No need for buttons. Just get all the info based on the search options. Then, when you create the list of the dates, or rows of the results from the database, you keep a running grand total. Add the SUM in the SELECT where you get the top image from. Then, while parsing thru the list, keep running total like…
$grand_total = 0;
WHILE(mysqli_fetch_assoc… etc ) {
display a row of details.
$grand_total = $grand_total + $row[“TotalPrice”]; // Add each row’s total to grand total…
}
Then, just display the grand total! Something like that should work…

tedi · June 25, 2021, 9:26am

Sorry I dont know how to do that and i dont really understand it

ErnieAlex · June 25, 2021, 11:02am

Show us how you created the first image you posted. We can show you how to change it to get the grand total for you.

tedi · June 25, 2021, 11:18am

	<head>
		<style>
			body {background-color: #e6e6ff}
		</style>
	</head>
	<body>
		<center>
			<form action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="POST" >
				РѕС‚<input type="date" name="from_date"  value="<?php if(isset($_POST['from_date'])){ echo $_POST['from_date']; } ?>"> РґРѕ
				<input type="date" name="to_date"  value="<?php if(isset($_POST['to_date'])){ echo $_POST['to_date']; } ?>">
				<input type="submit" name="" value="SearchDate">
				
				<input type="text" name="searchname" placeholder="type a  name">
			<input type="submit" name="Search_N" value="SearchName">
				<table border="1" width ="40%">
				<tr>
					<tr><td>Date</td><td>Name</td> <td>Amount</td><td>Count</td><td>Unit price</td><td>Total</td> <td>Delete</td></tr>
				</tr>
<?php
while ($resul=mysqli_fetch_array($quer))
	{?>
		<tr>
			<td><input type="date" name="date"  placeholder="yyyy/mm/dd" value="<?php echo $resul['date'];?>"></td>
			<td><input type="text"   name="name"  value="<?php echo $resul['name'];?>"></td>
			<td><input type="text" name="amount"  value="<?php echo $resul['amount'];?>"></td>	
				<td><input type="text" name="count"  value="<?php echo $resul['count'];?>"></td>
					<td><input type="text" name="price"  value="<?php echo $resul['price'];?>"></td>
						<td><input type="text" name="price"  value="<?php echo $resul['price']*$resul['count'];?>" disabled=""></td>
				<td><a href="deleteFIN2.php?name=<?php echo $resul["name"]; ?>">Delete</a></td>
	</tr>
	<?php
	}
	?>
</table>
						<input type='submit' name='buton' value="NEXT"/>
                        <?php
                            if(isset($_POST['buton'])) {
  $resul=mysqli_fetch_array($quer);
}
                        ?>
                        <td><input class="totPro" type ="text" value="<?php if(isset($_POST['buton'])) 
                         echo $resul['TotalPrice'];?>"  disabled=""/> </td>
	</form>
			<form action="finance.php" method="post">
				<input type = "submit" name="Back" value="Back" >
			</form>
		</center>
	</body>
</html>``` 
this is the html cod where I created the look of the form
And you have php code up

ErnieAlex · June 25, 2021, 12:27pm

Hmm, this query is oddly formed. You probably should use a join…

 $sql="SELECT  SUM(income.count*menu.price) as TotalPrice 
        FROM income, menu  WHERE income.name = menu.name  and income.amount = menu.amount ";
        $quer=mysqli_query($conn,$sql) or die ('error sql');

The following does not show the query. If you add the TotalPrice calc into this query, you will have all the data plus the total is there, too. Then, just list the rows showing the Total. Add the total along the way like this and you will have the grand total…

<?php
$grand_total = 0;
while ($resul=mysqli_fetch_array($quer)) {
$grand_total = $grand_total + $resul["TotalPrice"];
?>
		<tr>
			<td><input type="date" name="date"  placeholder="yyyy/mm/dd" value="<?php echo $resul['date'];?>"></td>
			<td><input type="text"   name="name"  value="<?php echo $resul['name'];?>"></td>
			<td><input type="text" name="amount"  value="<?php echo $resul['amount'];?>"></td>	
			<td><input type="text" name="count"  value="<?php echo $resul['count'];?>"></td>
			<td><input type="text" name="price"  value="<?php echo $resul['price'];?>"></td>
			<td><input type="text" name="price"  value="<?php echo $resul['TotalPrice']];?>"></td>
			<td><a href="deleteFIN2.php?name=<?php echo $resul["name"]; ?>">Delete</a></td>
	</tr>
	<?php
	}
        //  Display grand total line
        echo "<tr><td colspan="5"></td><td>Grand Total:</td><td>" . $grand_total . "</td></tr>";
	?>

This is just an example off the top of my head, but, it would work if you fixed your query.

tedi · June 25, 2021, 4:03pm

$grand_total = $grand_total + $resul[“TotalPrice”]; this is line 53

ErnieAlex · June 25, 2021, 4:17pm

Well, as you see in the error, you didn’t add the SUM function to the query to create the TotalPrice field.
Fix the query to create the TotalPrice field and that will go away…

tedi · June 25, 2021, 4:29pm

Where exactly to put SUM function I dont see where

ErnieAlex · June 25, 2021, 4:54pm

Well, into the query where you get the data for this list. You display each row of data.
I removed the addition in each row as you can do that in the query instead and make it all faster.
Math in the query is faster than the match in PHP. So, create the TotalPrice in the query and
total it as you create each displayed line. Or, make a more complicated query to also total it all up
for the grand total. Either way should work for you.

tedi · June 25, 2021, 5:04pm

you said than i need to add the SUM function to the query to create the TotalPrice field
i just dont know where i cant understand you where to put it

ErnieAlex · June 25, 2021, 5:14pm

Please stop posting images. These do not help. Post code. Thanks!

Now, you do a WHILE ($resul …
That $resul is the RESULTS of a query. Where is the query part?

tedi · June 25, 2021, 5:26pm

elseif (isset($_POST['buton']))
{
		$sql="SELECT  SUM(income.count*menu.price) as TotalPrice 
		FROM income, menu  WHERE income.name = menu.name  and income.amount = menu.amount ";	
		exit("Run SQL: ".$sql);
        $quer=mysqli_query($conn, $sql) or die ('error sql');
}
this is the query

ErnieAlex · June 25, 2021, 5:41pm

That was posted before, it is not the one that creates the list that you place into tables.
You need to take that SUM and put it into the other query.

tedi · June 25, 2021, 5:44pm

 <?php
$grand_total = 0;
while ($resul=mysqli_fetch_array($quer)) {
$grand_total =  $grand_total + $resul["TotalPrice"];
?>
		<tr>
			<td><input type="date" name="date"  placeholder="yyyy/mm/dd" value="<?php echo $resul['date'];?>"></td>
			<td><input type="text"   name="name"  value="<?php echo $resul['name'];?>"></td>
			<td><input type="text" name="amount"  value="<?php echo $resul['amount'];?>"></td>	
			<td><input type="text" name="count"  value="<?php echo $resul['count'];?>"></td>
			<td><input type="text" name="price"  value="<?php echo $resul['price'];?>"></td>
			<td><input type="text" name="price"  value="<?php echo $resul['TotalPrice'] ;?>"></td>
			<td><a href="deleteFIN2.php?name=<?php echo $resul["name"]; ?>">Delete</a></td>
	</tr>
	<?php
	}
        //  Display grand total line
         echo "<tr><td colspan= '5'></td><td>Grand Total:</td><td>" . $grand_total . "</td></tr>";
	?>
</table>
						<input type='submit' name='buton' value="NEXT"/>
                        <?php
                            if(isset($_POST['buton']))
								{
									$resul=mysqli_fetch_array($quer);
								}
                        ?>
                        <td><input class="totPro" type ="text" value="<?php if(isset($_POST['buton'])) 
                         echo $resul['TotalPrice'];?>"  disabled=""/> </td>```
Where

ErnieAlex · June 25, 2021, 5:55pm

So, you run a query. It is inside the variable $quer !!!
Show THAT line. We can not help you unless we see your query!

ErnieAlex · June 25, 2021, 5:57pm

tedi:

 $sql="SELECT  SUM(income.count*menu.price) as TotalPrice 
        FROM income, menu  WHERE income.name = menu.name  and income.amount = menu.amount ";
        $quer=mysqli_query($conn,$sql) or die ('error sql');

Okay, at the top of this thread you show us this query. Perhaps that is what you mean.
BUT, that does not have the full list of date, name, amount, etc… So, this can NOT be the query you are using to get the data for the grid display you showed us!

tedi · June 25, 2021, 6:00pm

<?php
require_once("connect.php");
 if (isset($_POST['Search_N']))
	{	
		$sql="SELECT date, income.name, income.amount, count, menu.price FROM income JOIN menu ON menu.name = income.name AND menu.amount = income.amount WHERE income.name like '%".$_POST['searchname']."%'";	
		$quer=mysqli_query($conn,$sql) or die ('error sql');
		
	}
	elseif(isset($_POST['from_date']) && isset($_POST['to_date']) && $_POST['to_date'] != '' && $_POST['from_date'] != '')
{
		$from_date = $_POST['from_date'];
        $to_date = $_POST['to_date'];
        $sql="SELECT date, income.name, income.amount, count, menu.price FROM income JOIN menu ON menu.name = income.name AND menu.amount = income.amount  WHERE date BETWEEN '$from_date' AND '$to_date'"; 
		$quer=mysqli_query($conn,$sql) ;
		
}
elseif (isset($_POST['buton']))
{
		$sql="SELECT  SUM(income.count*menu.price) as TotalPrice 
		FROM income, menu  WHERE income.name = menu.name  and income.amount = menu.amount ";	
		exit("Run SQL: ".$sql);
        $quer=mysqli_query($conn, $sql) or die ('error sql');
}
else
    {
        $sql="SELECT date, income.name, income.amount, count, menu.price FROM income JOIN menu ON menu.name = income.name AND menu.amount = income.amount";
        $quer=mysqli_query($conn,$sql) ;
        if($quer === FALSE) exit('An error has occurred in the query, The error is : '.mysqli_error($conn).'');
    }	
?>
<html>
	<head>
		<style>
			body {background-color: #e6e6ff}
		</style>
	</head>
	<body>
		<center>
			<form action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="POST" >
				от<input type="date" name="from_date"  value="<?php if(isset($_POST['from_date'])){ echo $_POST['from_date']; } ?>"> до
				<input type="date" name="to_date"  value="<?php if(isset($_POST['to_date'])){ echo $_POST['to_date']; } ?>">
				<input type="submit" name="" value="SearchDate">
				
				<input type="text" name="searchname" placeholder="type a  name">
			<input type="submit" name="Search_N" value="SearchName">
				<table border="1" width ="40%">
				<tr>
					<tr><td>Date</td><td>Name</td> <td>Amount</td><td>Count</td><td>Unit price</td><td>Total</td> <td>Delete</td></tr>
				</tr>
<?php
$grand_total = 0;
while ($resul=mysqli_fetch_array($quer)) {
$grand_total =  $grand_total + $resul["TotalPrice"];
?>
		<tr>
			<td><input type="date" name="date"  placeholder="yyyy/mm/dd" value="<?php echo $resul['date'];?>"></td>
			<td><input type="text" name="name"  value="<?php echo $resul['name'];?>"></td>
			<td><input type="text" name="amount"  value="<?php echo $resul['amount'];?>"></td>	
			<td><input type="text" name="count"  value="<?php echo $resul['count'];?>"></td>
			<td><input type="text" name="price"  value="<?php echo $resul['price'];?>"></td>
			<td><input type="text" name="price"  value="<?php echo $resul['TotalPrice'] ;?>"></td>
			<td><a href="deleteFIN2.php?name=<?php echo $resul["name"]; ?>">Delete</a></td>
	</tr>
	<?php
	}
        //  Display grand total line
         echo "<tr><td colspan= '5'></td><td>Grand Total:</td><td>" . $grand_total . "</td></tr>";
	?>
</table>
						<input type='submit' name='buton' value="NEXT"/>
                        <?php
                            if(isset($_POST['buton']))
								{
									$resul=mysqli_fetch_array($quer);
								}
                        ?>
                        <td><input class="totPro" type ="text" value="<?php if(isset($_POST['buton'])) 
                         echo $resul['TotalPrice'];?>"  disabled=""/> </td>
	</form>
			<form action="finance.php" method="post">
				<input type = "submit" name="Back" value="Back" >
			</form>
		</center>
	</body>
</html>

this is the whole code

ErnieAlex · June 25, 2021, 6:04pm

Thank you! Change this:

tedi:

	$from_date = $_POST['from_date'];
        $to_date = $_POST['to_date'];
        $sql="SELECT date, income.name, income.amount, count, menu.price FROM income JOIN menu ON menu.name = income.name AND menu.amount = income.amount  WHERE date BETWEEN '$from_date' AND '$to_date'"; 
		$quer=mysqli_query($conn,$sql) ;

TO:

tedi:

		$from_date = $_POST['from_date'];
        $to_date = $_POST['to_date'];
        $sql="SELECT date, income.name, income.amount, count, menu.price, SUM(income.count*menu.price) as TotalPrice FROM income JOIN menu ON menu.name = income.name AND menu.amount = income.amount  WHERE date BETWEEN '$from_date' AND '$to_date'"; 
		$quer=mysqli_query($conn,$sql) ;

And, then, you will get an extra field returned with the totals from the count times price and then, you can display it without needing to have PHP calculate it. Do you understand this now?

tedi · June 25, 2021, 6:06pm

still give me error