Looping

Jeneca · February 9, 2012

I have in my database a list of store names, And it takes time to input one by one their names in a drop down list, so I need to loop, How to do this..?

Don E · February 9, 2012

Here's a snippet from something I have that can give you an idea on how to do what you want to accomplish:

<select name="subject" id="subject" tabindex="30" >			 <option value="general" selected="selected">General</option>			 <option value="items-products">Items/Products</option>			 <option value="other">Other</option><?php			 $dbc = new db_connect("np");			 $result = $dbc->query("SELECT title FROM postings");			 while($row = mysqli_fetch_assoc($result))			 {			   $title = $row['title'];			   echo '<option value=" ' . $title . ' ">' . $title . '</option>';			 }?>			</select>

For you, it would be storeName instead of title, from storeNames table. Hopefully you get the idea. Edit: The above is from an email form where the user can select a subject where the first are General, Items/Products, Other(these are basically for general inquiries)... and then a list of the actual products for the user to select as a subject if they have any specific questions for a product.

Jeneca · February 9, 2012

This is my sample program..

<?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 />			<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>Order No.</th>			<th>Order Source</th>			<th>Payment Type</th>			<th>Net Price</th>			<th>Gross Price</th>			<th>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.OrderNo,						 mds_orders.OrderSourceText AS 'ordersource', 						 mds_orders.PaymentTypeText AS 'paymenttype',						 mds_orders.NetTotal AS 'netprice',						 mds_orders.GrossTotal AS 'grossprice', 						 COUNT(ReasonMaster.ReasonDescription) AS 'reason'					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											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";				$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['OrderNo'].",".								$row['ordersource'].",".								$row['paymenttype'].",".								$row['netprice'].",".								$row['grossprice'].",".								$row['reason'].","."<br>";		?>				<tr>					<td><?php echo $row['code'];?></td>					<td><?php echo $row['name'];?></td>					<td><?php echo $row['OrderNo'];?></td>					<td><?php echo $row['ordersource'];?></td>					<td><?php echo $row['paymenttype'];?></td>					<td><?php echo $row['netprice'];?></td>					<td><?php echo $row['grossprice'];?></td>					<td><?php echo $row['reason'];?></td>				</tr>				</tr>				</tr>   		<?php		  		}}}		?>

Where I input this..?

<?php                         $dbc = new db_connect("np");                         $result = $dbc->query("SELECT title FROM postings");                         while($row = mysqli_fetch_assoc($result))                         {                           $title = $row['title'];                           echo '<option value=" ' . $title . ' ">' . $title . '</option>';                         }?>                    </select>

Don E · February 9, 2012

Are you asking to get/fetch the store names from the database and then have them in a drop down list using <select><option></option></select> tag? If so, you would insert that into the form in the code you provided or a form that requires that to be shown. Of course you wouldn't copy and paste exactly what I done, just switch it around to accommodate your code.

Jeneca · February 9, 2012

exactly, getting the store names from the database and then have them in a drop down list using <select><option></option </select> tag.Of course I will not copy and paste what you've done. But same thing right..? I need to insert that before the Go button is that mean that I will insert that before this..?

 <input name="submit" type="submit" value="Go">

Don E · February 9, 2012

Yes basically the same thing. Yes, before the Go button.

Jeneca · February 9, 2012

This is what I've done..Is that correct..?

<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 = $dbc->query("SELECT RestaurantID FROM mds_orders");       while($row = mysqli_fetch_assoc($result))       {       $id = $row['id'];       echo '<option value=" ' . $id . ' ">' . $id . '</option>';       }?>                    </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>Order No.</th>			<th>Order Source</th>			<th>Payment Type</th>			<th>Net Price</th>			<th>Gross Price</th>			<th>Reason</th>		</tr>

But still there are no names in a drop down.

Don E · February 9, 2012

You have to use the database connection you have for that page, which I think it's: $connect. Then have this: $result = mysql_query("SELECT RestaurantID FROM mds_orders"); Instead of: $result = $dbc->query("SELECT RestaurantID FROM mds_orders");

Jeneca · February 9, 2012

I already put my database connection at the top.

<?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 RestaurantID FROM mds_orders"); 				   while($row = mysqli_fetch_assoc($result))				   {				   $id = $row['id'];				   echo '<option value=" ' . $id . ' ">' . $id . '</option>';				   }				?>                    </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>Order No.</th>			<th>Order Source</th>			<th>Payment Type</th>			<th>Net Price</th>			<th>Gross Price</th>			<th>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.OrderNo,						 mds_orders.OrderSourceText AS 'ordersource', 						 mds_orders.PaymentTypeText AS 'paymenttype',						 mds_orders.NetTotal AS 'netprice',						 mds_orders.GrossTotal AS 'grossprice', 						 COUNT(ReasonMaster.ReasonDescription) AS 'reason'					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											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";				$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['OrderNo'].",".								$row['ordersource'].",".								$row['paymenttype'].",".								$row['netprice'].",".								$row['grossprice'].",".								$row['reason'].","."<br>";		?>				<tr>					<td><?php echo $row['code'];?></td>					<td><?php echo $row['name'];?></td>					<td><?php echo $row['OrderNo'];?></td>					<td><?php echo $row['ordersource'];?></td>					<td><?php echo $row['paymenttype'];?></td>					<td><?php echo $row['netprice'];?></td>					<td><?php echo $row['grossprice'];?></td>					<td><?php echo $row['reason'];?></td>				</tr>				</tr>				</tr>   		<?php		  		}}}		?>

And also change from this like what you've said awhile ago.

$result = mysql_query("SELECT RestaurantID FROM mds_orders");

But nothing's change.

Don E · February 9, 2012

the id should be: $id = row['RestaurantID']; instead of: $id = $row['id'];

Jeneca · February 9, 2012

I also try that, but I know why but still there are no store names in the drop down list. :|

birbal · February 9, 2012

echo '<option value=" ' . $id . ' ">' . $id . '</option>';

what does it print? what it is showing in source code? check the source code. and also it not supposed to be print the store name if everything is ok it will print the id only. if you are trying to to print the resturant name you are going to need to pull the name also from DB.

Jeneca · February 9, 2012

What I'm trying to display is the list of store names in a drop down list, but their restaurantID should be called/select..?What should I do..?

Don E · February 9, 2012

Select the names of the restaurants from the restaurant table... something like: SELECT restNames FROM restTable; I am assuming your database looks like this: SELECT name FROM restaurant_master; $result = mysql_query("SELECT name FROM restaurant_master"); Then have this in the while loop: $restName = $row['name'];echo '<option value=" ' . $restName . ' ">' . $restName . '</option>';

Jeneca · February 9, 2012

how can I get their value..?Example:I Choose Greenhills, only Greenhills should be displayed.

birbal · February 9, 2012

query should look something like

$result = mysql_query("SELECT name,id FROM restaurant_master");

it will get you name and id

echo "<option value='{$row['id']} '>{$row['name']}</option>";

and it will show the resturant name but when selected the id will be submited with the form.

Jeneca · February 9, 2012

@Don E and @ birbal thank you for your response. I already shows the store names in the drop down list, my problem now is how can I get their value..? Ex: If I Choose Greenhills, only the record of Greenhills should be displayed.

birbal · February 9, 2012

every resturant name has a uniqe id. right? as i said earlier when you submit the form will id will be submited. on submission you can query your database where the id matches and pull out the data. that means when you submit the page $_POST['name'] will hold the id of the resturant name (according tour code). so that later on you can use $_POST['name'] to query the database and format the pulled data. If you are going to do that dynamicaly you are going to need to use ajax. which will have involvment of javascript to submit the form and get the response without loading the page.

Jeneca · February 9, 2012

How can I add in the drop list the "ALL", that when I choose to select all store names..?

birbal · February 9, 2012

Select: <select name="id">echo "<option value='0'>ALL</option>";								  								<?php								   $result = mysql_query("SELECTname,id FROM mds_orders");								   while($row = mysqli_fetch_assoc($result))								   {

put a option tag outside of loop. you can make its value 0 (as any of your resturant id cant be 0). then check on the submission page like

$qry="SELECT name,location,id FROM resturant";   if($_POST['id'])$qry.=" WHERE id={$_POST['id']}"; $result=mysql_query($qry........

Jeneca · February 9, 2012

This query already works, but when I try to select all, there is no data displayed

<?php				   $result = mysql_query("SELECT name FROM restaurant_master"); 				   echo "<select name='name'>";				   echo "<option value='0'>ALL</option>";				   while($r = mysql_fetch_array($result))				   {				   echo "<option value='".$r['name']."'>".$r['name']."</option>"; 				   }				   echo "</select>";				?>

akienghie09 · February 9, 2012

It's like what you're trying to explain is that you wanted to have a select option where when you select it all the record in that date will display and it did not consider the name of the restaurant? Is that what you wanted to do? Is that right?

birbal · February 9, 2012

post the updated code where the submited form will be proccessed.

Don E · February 9, 2012

Jeneca, are you saying... when someone selects a restaurant name from the drop down list, to display all the info for that particular restaurant? If so, there are several ways you can do this.. one way is how birbal said. Another way could be to have a link in the table for each restaurant of your listed restaurants and the link says something like "Click here for more details". You can have that for each restaurant in your table. Then when the person clicks on the link, it takes them to another page displaying specific info about that restaurant. The link will have the id and name of the restaurant in name/value pairs, and then on the other page, you can use $_GET to get the id and name and use that to query the database to get specific info for that restaurant. Then you display the info in whatever way you wish.

Jeneca · February 10, 2012

Don E not that info, when someone selects a restaurant name from the drop down list it display the data for that particular restaurant, and data will come from the database. Any ways already done with that, my problem now is what if I select "ALL" restaurant..? IThis is my updated code

<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="pkid">		<?php					   echo "<option value='0'>ALL</option>";				   $result = mysql_query("SELECT name,pkid FROM restaurant_master"); 				  				   while($r = mysql_fetch_assoc($result))				   {				   echo "<option value='{$r['pkid']} '>{$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>Order No.</th>			<th>Order Source</th>			<th>Payment Type</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.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											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";				if($_POST['pkid'])				$sql.=" WHERE pkid={$_POST['pkid']}";				$result = mysql_query($sql)					          or die("Error: ".mysql_error());				$num_rows = mysql_num_rows($result);

Looping

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived