birbal Posted February 10, 2012 Share Posted February 10, 2012 $sql = "SELECT restaurant_master.code, restaurant_master.name, mds_orders.OrderNo, mds_orders.OrderSourceText AS 'ordersource', mds_orders.PaymentTypeText AS 'paymenttype', mds_orders.NetTotal AS 'netprice', mds_orders.GrossTotal AS 'grossprice', ReasonMaster.ReasonDescription AS 'reason', COUNT(ReasonMaster.ReasonDescription) AS 'reacount' FROM mds_orders JOIN ReasonMaster ON mds_orders.ReasonFKID = ReasonMaster.PKID JOIN restaurant_master ON mds_orders.RestaurantID = restaurant_master.pkid WHERE mds_orders.OrderDate BETWEEN '".$date_from." 00:00:00' AND '".$date_to." 23:59:59' AND restaurant_master.name = '$name' AND mds_orders.StatusFKID = 3"; if($_POST['pkid']) $sql.=" AND pkid={$_POST['pkid']}"; $sql.=" GROUP BY restaurant_master.code, restaurant_master.name, mds_orders.OrderNo, mds_orders.OrderSourceText, mds_orders.PaymentTypeText, ReasonMaster.ReasonDescription, mds_orders.NetTotal, mds_orders.GrossTotal"; you were concatenate in wrong place. it should work now. Link to comment Share on other sites More sharing options...
Jeneca Posted February 10, 2012 Author Share Posted February 10, 2012 Thank you Birbal! it already works.. but when I try to select all there are no data displayed..? Link to comment Share on other sites More sharing options...
birbal Posted February 10, 2012 Share Posted February 10, 2012 can you tell what does echo $sql prints after you choose ALL and submit the form? Link to comment Share on other sites More sharing options...
Jeneca Posted February 10, 2012 Author Share Posted February 10, 2012 Nothing, only the selected date like.. Date select from 2012-01-01 to 2012-01-02Restaurant: 0Other than that there are no restaurant name and data displayed. Link to comment Share on other sites More sharing options...
birbal Posted February 10, 2012 Share Posted February 10, 2012 $sql = "SELECT restaurant_master.code, restaurant_master.name, mds_orders.OrderNo, mds_orders.OrderSourceText AS 'ordersource', mds_orders.PaymentTypeText AS 'paymenttype', mds_orders.NetTotal AS 'netprice', mds_orders.GrossTotal AS 'grossprice', ReasonMaster.ReasonDescription AS 'reason', COUNT(ReasonMaster.ReasonDescription) AS 'reacount' FROM mds_orders JOIN ReasonMaster ON mds_orders.ReasonFKID = ReasonMaster.PKID JOIN restaurant_master ON mds_orders.RestaurantID = restaurant_master.pkid WHERE mds_orders.OrderDate BETWEEN '".$date_from." 00:00:00' AND '".$date_to." 23:59:59' AND restaurant_master.name = '$name' AND mds_orders.StatusFKID = 3"; if($_POST['pkid']) $sql.=" AND pkid={$_POST['pkid']}"; $sql.=" GROUP BY restaurant_master.code, restaurant_master.name, mds_orders.OrderNo, mds_orders.OrderSourceText, mds_orders.PaymentTypeText, ReasonMaster.ReasonDescription, mds_orders.NetTotal, mds_orders.GrossTotal";echo $qry; <=== [b]i mean add this[/b] here and tell us what does it print Link to comment Share on other sites More sharing options...
Jeneca Posted February 10, 2012 Author Share Posted February 10, 2012 SELECT restaurant_master.code, restaurant_master.name, mds_orders.NetTotal AS 'netprice', mds_orders.GrossTotal AS 'grossprice', ReasonMaster.ReasonDescription AS 'reason', COUNT(ReasonMaster.ReasonDescription) AS 'reacount' FROM mds_orders JOIN ReasonMaster ON mds_orders.ReasonFKID = ReasonMaster.PKID JOIN restaurant_master ON mds_orders.RestaurantID = restaurant_master.pkid WHERE mds_orders.OrderDate BETWEEN '2012-01-01 00:00:00' AND '2012-01-31 23:59:59' AND restaurant_master.name = '0' AND mds_orders.StatusFKID = 3 GROUP BY ReasonMaster.ReasonDescription this is the echo prints after I choose all. Link to comment Share on other sites More sharing options...
birbal Posted February 10, 2012 Share Posted February 10, 2012 where does $name comes from here?AND restaurant_master.name = '$name'The preparation of query looks like working.Probably this the conditions in WHERE clause which is not matching so that no rows are returning. Link to comment Share on other sites More sharing options...
Don E Posted February 10, 2012 Share Posted February 10, 2012 So if you select ALL from the drop down list, you want it to display all the restaurant names? Link to comment Share on other sites More sharing options...
Jeneca Posted February 10, 2012 Author Share Posted February 10, 2012 But why when I try to choose one store there is a rows that returning..? <?php // db connection $db = "mds_reports"; if($connect = mysql_connect("172.16.8.32", "mds_reports", "password")) $connect = mysql_select_db($db); else die("Unable to connect".mysql_error()); $date_from = $_POST['dfrom']; $date_to = $_POST['dto']; $name = $_POST['name'];?><html> <head> <title>Cancelled Order</title> </head> <body> <h1>Cancelled Order Report</h1> <form name="fetching" method="POST" action="cancelled_orders_index.php"> Date From: <input type="text" name="dfrom" id="dfrom"> Date To: <input type="text" name="dto" id="dto"><br /><br /> Select: <select name="name"> <?php $result = mysql_query("SELECT name FROM restaurant_master"); echo "<option value='0'>ALL</option>"; while($r = mysql_fetch_assoc($result)) { echo "<option value='".$r['name']."'>".$r['name']."</option>"; } echo "</select>"; ?> <input name="submit" type="submit" value="Go"> </form> <table> <table border="2" cellpadding="2" cellspacing="1" style='width:100%;'> <tr> <th>Restaurant Code</th> <th>Restaurant Name</th> <th>Net Price</th> <th>Gross Price</th> <th>Reason</th> <th>Count Reason</th> </tr> <?php if(isset($_POST['submit'])){ echo "Date select from $date_from to $date_to"."</p>"; echo "Restaurant: $name"."</p>"; $sql = "SELECT restaurant_master.code, restaurant_master.name, mds_orders.NetTotal AS 'netprice', mds_orders.GrossTotal AS 'grossprice', ReasonMaster.ReasonDescription AS 'reason', COUNT(ReasonMaster.ReasonDescription) AS 'reacount' FROM mds_orders JOIN ReasonMaster ON mds_orders.ReasonFKID = ReasonMaster.PKID JOIN restaurant_master ON mds_orders.RestaurantID = restaurant_master.pkid WHERE mds_orders.OrderDate BETWEEN '".$date_from." 00:00:00' AND '".$date_to." 23:59:59' AND restaurant_master.name = '$name' AND mds_orders.StatusFKID = 3"; if($_POST['pkid']) $sql.=" AND pkid={$_POST['pkid']}"; $sql.=" GROUP BY ReasonMaster.ReasonDescription"; $result = mysql_query($sql) or die("Error: ".mysql_error()); $num_rows = mysql_num_rows($result); $i = 0; if (mysql_num_rows($result) >0) { while ($row = mysql_fetch_array($result,MYSQL_ASSOC)){ $csv .= $row['code'].",". $row['name'].",". $row['netprice'].",". $row['grossprice'].",". $row['reason'].",". $row['reacount'].","."<br>"; ?> <tr> <td><?php echo $row['code'];?></td> <td><?php echo $row['name'];?></td> <td><?php echo $row['netprice'];?></td> <td><?php echo $row['grossprice'];?></td> <td><?php echo $row['reason'];?></td> <td><?php echo $row['reacount'];?></td> </tr> </tr> </tr> <?php }}} ?> I thought that $name comes from the variable for the restaurant_master.name, is it right..?or no need to do this..? Link to comment Share on other sites More sharing options...
birbal Posted February 10, 2012 Share Posted February 10, 2012 I am confused. you are keep changing the codes. it is hard to debug if you keep changing the approach. <select name="name"> in your last code you are using name but in previous one you was using pkid. both codes are redundant each other. at first decide how you are going to choose resturant by ID or by name? Link to comment Share on other sites More sharing options...
Jeneca Posted February 10, 2012 Author Share Posted February 10, 2012 Going to choose by Name, but the output is correct, the problem only is when the selection of all store there are output or data displayed. Link to comment Share on other sites More sharing options...
Jeneca Posted February 10, 2012 Author Share Posted February 10, 2012 Going to choose by Name, but the output is correct, the problem only is when the selection of all store there are no output or data displayed. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.