Creating an Iterator for While Loop Forms in PHP/MySQLi

Submitted by: 
Language: 
Visitors have accessed this post 3807 times.

This tutorial will teach you on how to create 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 iterator to associate our text with the selected product's id.

Creating our Database

First step is to create our database.
1. Open phpMyAdmin.
2. Click databases, create a database and name it as test.
3. After creating a database, click the SQL and paste the below codes. See image below for detailed instruction.

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

index.php

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

  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <title>Creating an Iterator for While Loop Forms in PHP/MySQLi</title>
  5. <script src="<a href="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  6. " rel="nofollow">https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></scri...</a> <link rel="stylesheet" href="<a href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css"" rel="nofollow">https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css"</a> />
  7. <script src="<a href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  8. " rel="nofollow">https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></s...</a>
  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="text" name="quantity_<?php echo $iterate; ?>" 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>

purchase.php

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

  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])){
  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. ?>

modal.php

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

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

And that ends this tutorial. 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.

Comments

your coding is pretty amazing

this is nearby good

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.