Member Avatar for ehpratah

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
  • 224 Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by Rahul47
Member Avatar for ehpratah

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

Member Avatar for Rahul47

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 because: Mistakes
Member Avatar for ehpratah

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>
Member Avatar for ehpratah

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 because: changes
Member Avatar for Rahul47

Kudos !!

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.