Creating an Iterator for While Loop Forms in PHP/MySQLi

Submitted by nurhodelta_17 on Tuesday, May 11, 2021 - 16:39.

Language

This tutorial will teach you how to create an iterator to use in while loops form to identify that a certain element of a form is associated with another element. In the case of this tutorial, we use an iterator to associate our text with the selected product's id.

Getting Started

In this tutorial, I am using XAMPP as my local web server to run PHP the php scripts. I used CDN's for the Bootstrap and jQuery which means internet connection is needed to run the source codes in this tutorial properly otherwise download the said libraries.

Creating our Database

The first step is to create our database.

  1. Open the XAMPP's Control Panel and start Apache and MySQL.
  2. Browse the PHPMyAdmin in a browser. i.e. http://localhost/phpmyadmin
  3. Click the databases, create a new database and name it as test.
  4. After creating a database, click the SQL and copy/paste the below codes. See the image below for detailed instructions.
  1. CREATE TABLE `details` (
  2.   `detailsid` int(11) NOT NULL AUTO_INCREMENT,
  3.   `purchaseid` int(11) NOT NULL,
  4.   `productid` int(11) NOT NULL,
  5.   `quantity` double NOT NULL,
  6. PRIMARY KEY(`detailsid`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  8.  
  9. CREATE TABLE `product` (
  10.   `productid` int(11) NOT NULL AUTO_INCREMENT,
  11.   `product_name` varchar(50) NOT NULL,
  12.   `price` double NOT NULL,
  13. PRIMARY KEY(`productid`) 
  14. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  15.  
  16. CREATE TABLE `purchase` (
  17.   `purchaseid` int(11) NOT NULL AUTO_INCREMENT,
  18.   `name` varchar(50) NOT NULL,
  19.   `total` double NOT NULL,
  20.   `date` datetime NOT NULL,
  21. PRIMARY KEY(`purchaseid`)
  22. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  23.  
  24. INSERT INTO `product` (`productid`, `product_name`, `price`) VALUES
  25. (4, 'Laptop', 699.99),
  26. (5, 'Desktop Pc', 999.99),
  27. (6, 'Tablet', 299.99);
iterator

Creating our Connection

Next, we create our connection to our database. This will serve as the bridge between our forms and database. We name this as conn.php.

  1. <?php
  2.  
  3. $conn = mysqli_connect("localhost","root","","test");
  4. if (!$conn) {
  5. 	die("Connection failed: " . mysqli_connect_error());
  6. }
  7.  
  8. ?>

Creating the Interface

Next, we create a list of our products and our purchase table. We included our iterator in our purchase product form.

index.php 
  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. 	<title>Creating an Iterator for While Loop Forms in PHP/MySQLi</title>
  5. 	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  6. 	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  7. 	<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  8.  
  9. 	<style>
  10. 		input[type="checkbox"] {
  11. 		transform:scale(2, 2);
  12.     }
  13. 	</style>
  14.  
  15. </head>
  16. <body>
  17. <div class="container">
  18. 	<div class="row">
  19. 		<div class="col-lg-12">
  20. 			<h2 style="color:blue">Product List</h2>
  21. 		</div>
  22. 	</div>
  23. 	<div class="row">
  24. 		<div class="col-lg-12">
  25. 			<form method="POST" action="purchase.php" class="form-inline">
  26. 				<table width="100%" class="table table-striped table-bordered table-hover">
  27.                     <thead>
  28. 						<th style="text-align:center"><input type="checkbox" id="checkAll"></th>
  29. 						<th>Product Name</th>
  30. 						<th>Price</th>
  31. 						<th>Quantity</th>
  32.                     </thead>
  33.                     <tbody>
  34. 						<?php
  35. 							include('conn.php');
  36. 							$query=mysqli_query($conn,"select * from product order by product_name asc");
  37. 							//dis is our iterator
  38. 							$iterate=0;
  39. 							while($row=mysqli_fetch_array($query)){
  40. 							?>
  41. 								<tr>
  42. 									<td align="center"><input type="checkbox" value="<?php echo $row['productid']; ?>||<?php echo $iterate; ?>" name="prodid[]"></td>
  43. 									<td><?php echo $row['product_name']; ?></td>
  44. 									<td><?php echo number_format($row['price'],2); ?></td>
  45. 									<td><input type="number" name="quantity_<?php echo $iterate; ?>" value="0" class="form-control"></td>
  46. 								</tr>
  47. 							<?php
  48. 							$iterate++;
  49. 							}
  50. 						?>
  51.                     </tbody>
  52.                 </table>
  53. 			<input type="text" name="cname" class="form-control" placeholder="Customer Name" style="width:300px;" required> <button type="submit" class="btn btn-primary"><span class="glyphicon glyphicon-floppy-disk"></span> Save</button>
  54. 			</form>
  55. 		</div>
  56.     </div>
  57.  
  58. 	<div class="row">
  59. 		<div class="col-lg-12">
  60. 			<h2 style="color:blue">Purchases</h2>
  61. 		</div>
  62. 	</div>
  63. 	<div class="row">
  64. 		<div class="col-lg-12">
  65. 				<table width="100%" class="table table-striped table-bordered table-hover">
  66.                     <thead>
  67. 						<th class="hidden"></th>
  68. 						<th>Date of Purchase</th>
  69. 						<th>Customer</th>
  70. 						<th>Total Purchase</th>
  71. 						<th>View</th>
  72.                     </thead>
  73.                     <tbody>
  74. 						<?php
  75. 							include('conn.php');
  76. 							$query1=mysqli_query($conn,"select * from purchase order by date desc");
  77. 							while($row1=mysqli_fetch_array($query1)){
  78. 							?>
  79. 								<tr>
  80. 									<td class="hidden"></td>
  81. 									<td><?php echo date('M d, Y h:i A', strtotime($row1['date'])); ?></td>
  82. 									<td><?php echo $row1['name']; ?></td>
  83. 									<td><?php echo number_format($row1['total'],2); ?></td>
  84. 									<td>
  85. 										<a href="#show<?php echo $row1['purchaseid']; ?>" data-toggle="modal" class="btn btn-primary btn-sm"><span class="glyphicon glyphicon-fullscreen"></span> Product List</a>
  86. 										<?php include('modal.php'); ?>
  87. 									</td>
  88. 								</tr>
  89. 							<?php
  90. 							}
  91. 						?>
  92.                     </tbody>
  93.                 </table>
  94. 		</div>
  95.     </div>
  96. </div>
  97. <script>
  98. $(document).ready(function(){
  99. 	$("#checkAll").change(function () {
  100.     $("input:checkbox").prop('checked', $(this).prop("checked"));
  101. 	});
  102. });
  103. </script>
  104. </body>
  105. </html>

Creating the Save Script

This is our purchase code. It will insert the products selected to our purchase table.

purchase.php 
  1. <?php
  2. 	include('conn.php');
  3. 	if(isset($_POST['prodid'])){
  4.  
  5. 		$customer=$_POST['cname'];
  6. 		mysqli_query($conn,"INSERT into purchase (name, date) values ('$customer', NOW())");
  7. 		$purchase=mysqli_insert_id($conn);
  8.  
  9. 		$total=0;
  10.  
  11. 		foreach($_POST['prodid'] as $product):
  12. 		$proinfo=explode("||",$product);
  13. 		$productid=$proinfo[0];
  14. 		$iterate=$proinfo[1];
  15. 		$pquery=mysqli_query($conn,"SELECT * from product where productid='$productid'");
  16. 		$prow=mysqli_fetch_array($pquery);
  17.  
  18. 		if (isset($_POST['quantity_'.$iterate]) && $_POST['quantity_'.$iterate] > 0){
  19. 			$subt= ($prow['price'] * $_POST['quantity_'.$iterate]);
  20. 			$total+=$subt;
  21. 			mysqli_query($conn,"INSERT into details (purchaseid, productid, quantity) values ('$purchase', '$productid', '".$_POST['quantity_'.$iterate]."')");
  22.  
  23. 			header('location:index.php');
  24.  
  25. 		}
  26. 		endforeach;
  27.  
  28. 		mysqli_query($conn,"UPDATE purchase set total='$total' where purchaseid='$purchase'");		
  29. 	}
  30. 	else{
  31. 		?>
  32. 		<script>
  33. 			window.alert('Please select a product');
  34. 			window.location.href='index.php';
  35. 		</script>
  36. 		<?php
  37. 	}
  38. ?>

Lastly, we create our modal in showing the list of products in a purchase.

modal.php 
  1. <!-- Product Purchase -->
  2.     <div class="modal fade" id="show<?php echo $row1['purchaseid']; ?>" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
  3.         <div class="modal-dialog modal-lg">
  4.             <div class="modal-content">
  5.                 <div class="modal-header">
  6.                     <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
  7. 					<center><h4 class="modal-title" id="myModalLabel">List of Purchased Products</h4></center>
  8.                 </div>
  9.                 <div class="modal-body">
  10. 				<?php
  11. 					$a=mysqli_query($conn,"select * from details where purchaseid='".$row1['purchaseid']."'");
  12. 					$arow=mysqli_fetch_array($a);
  13.  
  14. 					$b=mysqli_query($conn,"select * from purchase where purchaseid='".$row1['purchaseid']."'");
  15. 					$brow=mysqli_fetch_array($b);
  16. 				?>
  17. 				<div class="container-fluid">
  18. 					<div class="row">
  19. 						<div class="col-lg-12">
  20. 							<span>Sold to: <?php echo $brow['name']; ?></span>
  21. 						</div>
  22. 					</div>
  23. 					<div class="row">
  24. 						<div class="col-lg-12">
  25. 							<table width="100%" class="table table-striped table-bordered table-hover">
  26. 								<thead>
  27. 									<tr>
  28. 										<th>Product Name</th>
  29. 										<th>Price</th>
  30. 										<th>Quantity</th>
  31. 										<th>Subtotal</th>
  32. 									</tr>
  33. 								</thead>
  34. 								<tbody>
  35. 									<?php
  36. 										$total=0;
  37. 										$s=mysqli_query($conn,"select * from details left join product on product.productid=details.productid where purchaseid='".$row1['purchaseid']."'");
  38. 										while($srow=mysqli_fetch_array($s)){
  39. 											?>
  40. 											<tr>
  41. 												<td><?php echo $srow['product_name']; ?></td>
  42. 												<td><?php echo number_format($srow['price'],2); ?></td>
  43. 												<td><?php echo $srow['quantity']; ?></td>
  44. 												<td>
  45. 													<?php 
  46. 														$sub=$srow['price']*$srow['quantity'];
  47. 														echo number_format($sub,2);
  48. 														$total+=$sub;
  49. 													?>
  50. 												</td>
  51. 											</tr>
  52. 											<?php
  53. 										}
  54. 									?>
  55. 									<tr>
  56. 										<td colspan="3" align="right"><strong>Grand Total</strong></td>
  57. 										<td><strong><?php echo number_format($total,2); ?></strong></td>
  58. 									</tr>
  59. 								</tbody>
  60. 							</table>
  61. 						</div>
  62. 					</div>      
  63. 				</div>
  64. 				</div>
  65.                 <div class="modal-footer">
  66.                     <button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
  67.                 </div>
  68.             </div>
  69.         </div>
  70.     </div>
  71. <!-- /.modal -->

Demo

And that ends this tutorial. I hope this will help you with what you are looking for and for your future PHP Projects. If you have questions or comments, feel free to express it below or message me.

Happy Coding :)

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Tags

Comments

Add new comment

About text formats