Skip to main content

How to Create SQL Sub Query in PHP/MySQLi Tutorial

Screenshot
Body

In this tutorial, I've prepared example queries for you to further understand the concept of subqueries. A tutorial that tackles SQL Subquery. Sub query is a query within another SQL query. Subqueries can be used with the 4 SQL methods which are SELECT, INSERT, UPDATE, and DELETE.

Before we start, please download XAMPP and Install It. After that, please open the XAMPP's Control Panel and start Apache and MySQL. I am using CDN's in this tutorial which means internet connection is needed.

Creating our Database

The first step is to create our database. This contains our sample data.

  1. Open phpMyAdmin.
  2. Click databases, create a database and name it as subquery.
  3. After creating a database, click the SQL and paste the below codes. See image below for detailed instruction.
  1. CREATE TABLE `post` (
  2. `postid` int(11) NOT NULL AUTO_INCREMENT,
  3. `userid` int(11) NOT NULL,
  4. `post_text` varchar(200) NOT NULL,
  5. `post_date` datetime NOT NULL,
  6. PRIMARY KEY(`postid`)
  7.  
  8. CREATE TABLE `user` (
  9. `firstname` varchar(30) NOT NULL,
  10. `lastname` varchar(30) NOT NULL
  1. INSERT INTO `post` (`postid`, `userid`, `post_text`, `post_date`) VALUES
  2. (1, 1, 'Hello', '2017-09-13 08:00:00'),
  3. (2, 1, 'Hi', '2017-10-19 13:30:00'),
  4. (3, 1, 'Eureka', '2017-10-04 20:05:00'),
  5. (4, 1, 'I got it', '2017-10-02 15:10:00'),
  6. (5, 1, 'Hello World', '2017-10-15 07:40:00'),
  7. (6, 1, 'Hi people', '2017-09-20 23:00:00'),
  8. (7, 1, 'Wazzup', '2017-10-04 01:00:00'),
  9. (8, 1, '#tb', '2017-09-01 22:05:00');
  10.  
  11. INSERT INTO `user` (`userid`, `firstname`, `lastname`) VALUES
  12. (1, 'neovic', 'devierte');
subquery

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. $conn = mysqli_connect("localhost","root","","subquery");
  3. if (!$conn) {
  4. die("Connection failed: " . mysqli_connect_error());
  5. }
  6.  
  7. ?>

Table HTML Format

The below HTML code is the html format that i will use to display each queries.

  1. <div class="row">
  2. <span><!-- Query Title Here --></span>
  3. <table class="table table-bordered table-striped">
  4. <tr>
  5. <th>PostID</th>
  6. <!-- For Last query only -->
  7. <th>User</th>
  8. <th>Post Text</th>
  9. <th>Post Date</th>
  10. </tr>
  11. </thead>
  12. <!-- Query rows here -->
  13. </tbody>
  14. </table>
  15. </div>

Example 1

table post order by post_date asc

  1. <?php
  2. $asc=mysqli_query($conn,"select * from post order by post_date asc");
  3. while($ascrow=mysqli_fetch_array($asc)){
  4. ?>
  5. <tr>
  6. <td><?php echo $ascrow['postid']; ?></td>
  7. <td><?php echo $ascrow['post_text']; ?></td>
  8. <td><?php echo date('M-d, Y h:i A',strtotime($ascrow['post_date'])); ?></td>
  9. </tr>
  10. <?php
  11. }
  12. ?>
Result: subqueryex1

Example 2

table post order by post_date desc

  1. <?php
  2. $desc=mysqli_query($conn,"select * from post order by post_date desc");
  3. while($descrow=mysqli_fetch_array($desc)){
  4. ?>
  5. <tr>
  6. <td><?php echo $descrow['postid']; ?></td>
  7. <td><?php echo $descrow['post_text']; ?></td>
  8. <td><?php echo date('M-d, Y h:i A',strtotime($descrow['post_date'])); ?></td>
  9. </tr>
  10. <?php
  11. }
  12. ?>
Result: subqueryex2

Example 3

table post order by post_date desc limit 3

  1. <?php
  2. $limit=mysqli_query($conn,"select * from post order by post_date desc limit 3");
  3. while($limitrow=mysqli_fetch_array($limit)){
  4. ?>
  5. <tr>
  6. <td><?php echo $limitrow['postid']; ?></td>
  7. <td><?php echo $limitrow['post_text']; ?></td>
  8. <td><?php echo date('M-d, Y h:i A',strtotime($limitrow['post_date'])); ?></td>
  9. </tr>
  10. <?php
  11. }
  12. ?>
Result: subqueryex3

Subquery

Lastly. this is our subquery. I've also added to join user table to provide examples in case you need to join.

(order by post_date desc limit 3) as subquery order by post_date asc
  1. <?php
  2. $sub=mysqli_query($conn,"select * from (select * from post order by post_date desc limit 3)as sub left join user on user.userid=sub.userid order by post_date asc");
  3. while($subrow=mysqli_fetch_array($sub)){
  4. ?>
  5. <tr>
  6. <td><?php echo $subrow['postid']; ?></td>
  7. <td><?php echo $subrow['firstname']; ?> <?php echo $subrow['lastname']; ?></td>
  8. <td><?php echo $subrow['post_text']; ?></td>
  9. <td><?php echo date('M-d, Y h:i A',strtotime($subrow['post_date'])); ?></td>
  10. </tr>
  11. <?php
  12. }
  13. ?>
Result: subqueryex4

That's it guys! In case you wanted the whole index.php code, here it is:

index.php

  1. <?php include('conn.php'); ?>
  2. <!DOCTYPE>
  3. <html>
  4. <head>
  5. <title>SQL SubQuery in PHP/MySQLi</title>
  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. </head>
  9. <body>
  10. <div class="container">
  11. <div class="row">
  12. <h2><center>SQL SubQuery in PHP/MySQLi</center></h2>
  13. </div>
  14. <div class="row">
  15. <span>order by post_date asc</span>
  16. <table class="table table-bordered table-striped">
  17. <thead>
  18. <th>PostID</th>
  19. <th>Post Text</th>
  20. <th>Post Date</th>
  21. </thead>
  22. <tbody>
  23. <?php
  24. $asc=mysqli_query($conn,"select * from post order by post_date asc");
  25. while($ascrow=mysqli_fetch_array($asc)){
  26. ?>
  27. <tr>
  28. <td><?php echo $ascrow['postid']; ?></td>
  29. <td><?php echo $ascrow['post_text']; ?></td>
  30. <td><?php echo date('M-d, Y h:i A',strtotime($ascrow['post_date'])); ?></td>
  31. </tr>
  32. <?php
  33. }
  34. ?>
  35. </tbody>
  36. </table>
  37. </div>
  38. <div class="row">
  39. <span>order by post_date desc</span>
  40. <table class="table table-bordered table-striped">
  41. <thead>
  42. <th>PostID</th>
  43. <th>Post Text</th>
  44. <th>Post Date</th>
  45. </thead>
  46. <tbody>
  47. <?php
  48. $desc=mysqli_query($conn,"select * from post order by post_date desc");
  49. while($descrow=mysqli_fetch_array($desc)){
  50. ?>
  51. <tr>
  52. <td><?php echo $descrow['postid']; ?></td>
  53. <td><?php echo $descrow['post_text']; ?></td>
  54. <td><?php echo date('M-d, Y h:i A',strtotime($descrow['post_date'])); ?></td>
  55. </tr>
  56. <?php
  57. }
  58. ?>
  59. </tbody>
  60. </table>
  61. </div>
  62. <div class="row">
  63. <span>order by post_date desc limit 3</span>
  64. <table class="table table-bordered table-striped">
  65. <thead>
  66. <th>PostID</th>
  67. <th>Post Text</th>
  68. <th>Post Date</th>
  69. </thead>
  70. <tbody>
  71. <?php
  72. $limit=mysqli_query($conn,"select * from post order by post_date desc limit 3");
  73. while($limitrow=mysqli_fetch_array($limit)){
  74. ?>
  75. <tr>
  76. <td><?php echo $limitrow['postid']; ?></td>
  77. <td><?php echo $limitrow['post_text']; ?></td>
  78. <td><?php echo date('M-d, Y h:i A',strtotime($limitrow['post_date'])); ?></td>
  79. </tr>
  80. <?php
  81. }
  82. ?>
  83. </tbody>
  84. </table>
  85. </div>
  86. <div class="row">
  87. <span>(order by post_date desc limit 3) as subquery order by post_date asc</span>
  88. <table class="table table-bordered table-striped">
  89. <thead>
  90. <th>PostID</th>
  91. <th>User</th>
  92. <th>Post Text</th>
  93. <th>Post Date</th>
  94. </thead>
  95. <tbody>
  96. <?php
  97. $sub=mysqli_query($conn,"select * from (select * from post order by post_date desc limit 3)as sub left join user on user.userid=sub.userid order by post_date asc");
  98. while($subrow=mysqli_fetch_array($sub)){
  99. ?>
  100. <tr>
  101. <td><?php echo $subrow['postid']; ?></td>
  102. <td><?php echo $subrow['firstname']; ?> <?php echo $subrow['lastname']; ?></td>
  103. <td><?php echo $subrow['post_text']; ?></td>
  104. <td><?php echo date('M-d, Y h:i A',strtotime($subrow['post_date'])); ?></td>
  105. </tr>
  106. <?php
  107. }
  108. ?>
  109. </tbody>
  110. </table>
  111. </div>
  112. </div>
  113. </body>
  114. </html>

DEMO

That ends this tutorial. For questions or comments, feel free to write below or message me.

Happy Coding :)

Add new comment