0

Good Day everyone(even if its already 10pm here) i know its seems very easy to some of you guys but seems i cant get an hold onto it.. so i have this query that search data between 2 date range(which is working fine) and another query that add all data from a specific field in my table(whic is also working). The thing is now what i needed to do is to combine the two queries in one

here's the first query

select * from advance WHERE SDate BETWEEN '" . $from . "' AND '" . $to . "'  ORDER BY  `Payment_type`

and the other

select SUM(Grand_total) AStotal_sales_todayfrom advance

thanks

2
Contributors
5
Replies
37
Views
3 Years
Discussion Span
Last Post by Rahul47
0

Hi guys im having a progress now when i run this query in my phpmyadmin

SELECT SUM(Grand_total) AS `total` from advance where SDate between '2013-08-01' AND '2013-08-24'

it is working but when in my actual page im having this error

Notice: Undefined index: RA in C:\xampp\htdocs\daterange\ifind.php on line 37

but the only thing that i add in my query is the ORDER BY

whick look like this

SELECT SUM(Grand_total) AS `total` from advance where SDate between '" . $from . "' AND '" . $to . "'  ORDER BY  `Payment_type`

any idea what happening..?

0

In your first query as :

select * from advance WHERE SDate BETWEEN '" . $from . "' AND '" . $to . "'  ORDER BY  `Payment_type`

you selected all fields from advance which consisted of Payment_type,

But in you recent query as:

SELECT SUM(Grand_total) AS `total` from advance where SDate between '" . $from . "' AND '" . $to . "'  ORDER BY  `Payment_type`

You selected only SUM(Grand_Total), hence Payment_Type was never selected to be ordered by in requested fields.

Conclusion: You didnt asked for Payment_Type so you can't arrange according to it.

Edited by Rahul47: Mistakes

0

So what should i do..?how do i assign it inside the query..?

when i try ORDER BY Payment_type AS payment not also assigning

this is my code

<!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 http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head><body>
<?php
//$connection = mysql_connect('localhost', 'svcseuro_TRF', 'pcdoktor');
//$db = mysql_select_db('svcseuro_TRF', $connection);

$connection = mysql_connect('localhost', 'root', '');
$db = mysql_select_db('svcseuro_davao', $connection);

$from = $_POST['From'];
$to = $_POST['To'];
$from = mysql_escape_string($from); 
$to = mysql_escape_string($to); 

if($from=="" && $to=="")
echo "Enter Something to search";
else{
$query = mysql_query("SELECT SUM(Grand_total) AS `total` from advance where SDate between '" . $from . "' AND '" . $to . "' ORDER BY `Payment_type`" , $connection);


$string = '';

echo "<table border='1' width='100%' color='green'><tr><th width='5%'>Ref. No.</th><th width='5%'>Name</th><th width='5%'>Make</th><th width='5%'>Plate</th><th width='5%'>Service</th><th width='5%'>Service Fee</th><th width='5%'>Payment_type</th></tr>";

if (mysql_num_rows($query)){

while($rows = mysql_fetch_assoc($query))
{

$total=$rows['total'];


$string .= "<tr><td align='center'>".$rows['RA']."</td><td align='center'>".$rows['Name']."</td><td align='center'>".$rows['Model']."</td><td align='center'>".$rows['Plate']."</td><td align='center'>".$rows['Service']."<td align='center'>".$rows['Grand_total']."</td><td align='center'>".$rows['Payment_type']."</td></tr>";


}

}else{
$string = "No matches found!";
}

echo $string;


}

echo"<tr><td align='center'></td><td align='center'>&nbsp;</td><td align='center'></td><td align='center'></td><td align='center'><td align='center'></td><td align='center'></td></tr>";

?>
total:<?php echo $total;  ?>
</body>
</html>
0

Hey Guys finally problem solve after 3 cups of coffee:))

for anyone out there that face or in the future will face the same issues as me and give up trying to make it work inside the query itself you can just put the computation inside the while loop like this and echo the total amount. simple as that yet took me almost 3hrs..:((

while($rows = mysql_fetch_assoc($query))
{


 $total += $rows['Grand_total'];




}

Edited by ehpratah: changes

This question has already been answered. 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.