Jump to content

Looping


Jeneca

Recommended Posts

 $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

can you tell what does echo $sql prints after you choose ALL and submit the form?

Link to comment
Share on other sites

$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

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

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

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

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...