How to Create SQL Sub Query in PHP/MySQLi Tutorial

Henrythecobra · 2025-02-27T06:20:36+0000

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.

Open phpMyAdmin

.
Click databases, create a database and name it as subquery

.
After creating a database, click the SQL and paste the below codes. See image below for detailed instruction.

CREATE

TABLE

`post`

(
`postid`

int

(

11

)

NOT

NULL

AUTO_INCREMENT

,
`userid`

int

(

11

)

NOT

NULL

,
`post_

text`

varchar

(

200

)

NOT

NULL

,
`post_

date`

datetime

NOT

NULL

,
PRIMARY KEY

(

`postid`

)
)

ENGINE

=

InnoDB

DEFAULT

CHARSET

=

latin1;
CREATE

TABLE

`user`

(
`userid`

int

(

11

)

NOT

NULL

AUTO_INCREMENT

PRIMARY KEY

,
`firstname`

varchar

(

30

)

NOT

NULL

,
`lastname`

varchar

(

30

)

NOT

NULL
)

ENGINE

=

InnoDB

DEFAULT

CHARSET

=

latin1;

INSERT

INTO

`post`

(

`postid`

,

`userid`

,

`post_

text`

,

`post_

date`

)

VALUES
(

1

,

1

,

'Hello'

,

'2017-09-13 08:00:00'

)

,
(

2

,

1

,

'Hi'

,

'2017-10-19 13:30:00'

)

,
(

3

,

1

,

'Eureka'

,

'2017-10-04 20:05:00'

)

,
(

4

,

1

,

'I got it'

,

'2017-10-02 15:10:00'

)

,
(

5

,

1

,

'Hello World'

,

'2017-10-15 07:40:00'

)

,
(

6

,

1

,

'Hi people'

,

'2017-09-20 23:00:00'

)

,
(

7

,

1

,

'Wazzup'

,

'2017-10-04 01:00:00'

)

,
(

8

,

1

,

'#tb'

,

'2017-09-01 22:05:00'

)

;
INSERT

INTO

`user`

(

`userid`

,

`firstname`

,

`lastname`

)

VALUES
(

1

,

'neovic'

,

'devierte'

)

;

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

.

<?php
$conn

=

mysqli_connect

(

"localhost"

,

"root"

,

""

,

"subquery"

)

;
if

(

!

$conn

)

{
die

(

"Connection failed: "

.

mysqli_connect_error

(

)

)

;
}
?>

Table HTML Format

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

<div

class

=

"row"

>
<span

>



</

span

>
<table

class

=

"table table-bordered table-striped"

>
<thead

>
<tr

>
<th

>

PostID</

th

>
<th

>

User</

th

>
<th

>

Post Text</

th

>
<th

>

Post Date</

th

>
</

tr

>
</

thead

>
<tbody

>
</

tbody

>
</

table

>
</

div

>

Example 1

table post order by post_date asc

<?php
$asc

=

mysqli_query

(

$conn

,

"select * from post order by post_date asc"

)

;
while

(

$ascrow

=

mysqli_fetch_array

(

$asc

)

)

{
?>
<tr>
<td><?php

echo

$ascrow

[

'postid'

]

;

?>

</td>
<td><?php

echo

$ascrow

[

'post_text'

]

;

?>

</td>
<td><?php

echo

date

(

'M-d, Y h:i A'

,

strtotime

(

$ascrow

[

'post_date'

]

)

)

;

?>

</td>
</tr>
<?php
}
?>

Result:

Example 2

table post order by post_date desc

<?php
$desc

=

mysqli_query

(

$conn

,

"select * from post order by post_date desc"

)

;
while

(

$descrow

=

mysqli_fetch_array

(

$desc

)

)

{
?>
<tr>
<td><?php

echo

$descrow

[

'postid'

]

;

?>

</td>
<td><?php

echo

$descrow

[

'post_text'

]

;

?>

</td>
<td><?php

echo

date

(

'M-d, Y h:i A'

,

strtotime

(

$descrow

[

'post_date'

]

)

)

;

?>

</td>
</tr>
<?php
}
?>

Result:

Example 3

table post order by post_date desc limit 3

<?php
$limit

=

mysqli_query

(

$conn

,

"select * from post order by post_date desc limit 3"

)

;
while

(

$limitrow

=

mysqli_fetch_array

(

$limit

)

)

{
?>
<tr>
<td><?php

echo

$limitrow

[

'postid'

]

;

?>

</td>
<td><?php

echo

$limitrow

[

'post_text'

]

;

?>

</td>
<td><?php

echo

date

(

'M-d, Y h:i A'

,

strtotime

(

$limitrow

[

'post_date'

]

)

)

;

?>

</td>
</tr>
<?php
}
?>

Result:

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

<?php
$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"

)

;
while

(

$subrow

=

mysqli_fetch_array

(

$sub

)

)

{
?>
<tr>
<td><?php

echo

$subrow

[

'postid'

]

;

?>

</td>
<td><?php

echo

$subrow

[

'firstname'

]

;

?>

<?php

echo

$subrow

[

'lastname'

]

;

?>

</td>
<td><?php

echo

$subrow

[

'post_text'

]

;

?>

</td>
<td><?php

echo

date

(

'M-d, Y h:i A'

,

strtotime

(

$subrow

[

'post_date'

]

)

)

;

?>

</td>
</tr>
<?php
}
?>

Result:

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

index.php

<?php

include

(

'conn.php'

)

;

?>
<!DOCTYPE>
<html>
<head>
<title>SQL SubQuery in PHP/MySQLi</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<h2><center>SQL SubQuery in PHP/MySQLi</center></h2>
</div>
<div class="row">
<span>order by post_date asc</span>
<table class="table table-bordered table-striped">
<thead>
<th>PostID</th>
<th>Post Text</th>
<th>Post Date</th>
</thead>
<tbody>
<?php
$asc

=

mysqli_query

(

$conn

,

"select * from post order by post_date asc"

)

;
while

(

$ascrow

=

mysqli_fetch_array

(

$asc

)

)

{
?>
<tr>
<td><?php

echo

$ascrow

[

'postid'

]

;

?>

</td>
<td><?php

echo

$ascrow

[

'post_text'

]

;

?>

</td>
<td><?php

echo

date

(

'M-d, Y h:i A'

,

strtotime

(

$ascrow

[

'post_date'

]

)

)

;

?>

</td>
</tr>
<?php
}
?>
</tbody>
</table>
</div>
<div class="row">
<span>order by post_date desc</span>
<table class="table table-bordered table-striped">
<thead>
<th>PostID</th>
<th>Post Text</th>
<th>Post Date</th>
</thead>
<tbody>
<?php
$desc

=

mysqli_query

(

$conn

,

"select * from post order by post_date desc"

)

;
while

(

$descrow

=

mysqli_fetch_array

(

$desc

)

)

{
?>
<tr>
<td><?php

echo

$descrow

[

'postid'

]

;

?>

</td>
<td><?php

echo

$descrow

[

'post_text'

]

;

?>

</td>
<td><?php

echo

date

(

'M-d, Y h:i A'

,

strtotime

(

$descrow

[

'post_date'

]

)

)

;

?>

</td>
</tr>
<?php
}
?>
</tbody>
</table>
</div>
<div class="row">
<span>order by post_date desc limit 3</span>
<table class="table table-bordered table-striped">
<thead>
<th>PostID</th>
<th>Post Text</th>
<th>Post Date</th>
</thead>
<tbody>
<?php
$limit

=

mysqli_query

(

$conn

,

"select * from post order by post_date desc limit 3"

)

;
while

(

$limitrow

=

mysqli_fetch_array

(

$limit

)

)

{
?>
<tr>
<td><?php

echo

$limitrow

[

'postid'

]

;

?>

</td>
<td><?php

echo

$limitrow

[

'post_text'

]

;

?>

</td>
<td><?php

echo

date

(

'M-d, Y h:i A'

,

strtotime

(

$limitrow

[

'post_date'

]

)

)

;

?>

</td>
</tr>
<?php
}
?>
</tbody>
</table>
</div>
<div class="row">
<span>(order by post_date desc limit 3) as subquery order by post_date asc</span>
<table class="table table-bordered table-striped">
<thead>
<th>PostID</th>
<th>User</th>
<th>Post Text</th>
<th>Post Date</th>
</thead>
<tbody>
<?php
$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"

)

;
while

(

$subrow

=

mysqli_fetch_array

(

$sub

)

)

{
?>
<tr>
<td><?php

echo

$subrow

[

'postid'

]

;

?>

</td>
<td><?php

echo

$subrow

[

'firstname'

]

;

?>

<?php

echo

$subrow

[

'lastname'

]

;

?>

</td>
<td><?php

echo

$subrow

[

'post_text'

]

;

?>

</td>
<td><?php

echo

date

(

'M-d, Y h:i A'

,

strtotime

(

$subrow

[

'post_date'

]

)

)

;

?>

</td>
</tr>
<?php
}
?>
</tbody>
</table>
</div>
</div>
</body>
</html>

DEMO

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

Happy Coding :)

Download

You must upgrade your account or reply in the thread to view hidden text.

How to Create SQL Sub Query in PHP/MySQLi Tutorial

More options

Henrythecobra

452,292

323,692

323,701