SQL Sub Query in PHP/MySQLi

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

In this tutorial, I've prepared example queries for you to further understand the concept of subqueries.

Creating our Database

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. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  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');

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

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 example in case you need to join.

  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="<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. </head>
  9. <body>
  10. <div" rel="nofollow">https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></s...</a> 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>

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


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.