Tagged: mysql nested where

How to write SQL Sub Query in PHP with MySQLi 0

How to write SQL Sub Query in PHP with MySQLi

How to write SQL Sub Query in PHP with MySQLi

In this Post We Will Explain About is How to write SQL Sub Query in PHP with MySQLi With Example and Demo.Welcome on Pakainfo.com – Examples, The best For Learn web development Tutorials,Demo with Example! Hi Dear Friends here u can know to SQL subquery with multiple results Example

In this post we will show you Best way to implement SQL Sub Query in PHP/MySQLi, hear for How to write subquery in FROM clause with Download .we will give you demo,Source Code and examples for implement Step By Step Good Luck!.

Creating Simple Database

CREATE TABLE `students` (
  `studenid` INT(11) NOT NULL AUTO_INCREMENT,
  `studentId` INT(11) NOT NULL,
  `student_post` VARCHAR(200) NOT NULL,
  `studregDate` datetime NOT NULL,
PRIMARY KEY(`studenid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `students` (`studenid`, `studentId`, `student_post`, `studregDate`) VALUES
(1, 1, 'jaydeep Gondaliya', '2018-09-13 08:00:00'),
(2, 1, 'Ankit kathiriya', '2018-10-19 13:30:00'),
(3, 1, 'krunal sisodiya', '2018-10-04 20:05:00'),
(4, 1, 'mayur dhameliya', '2018-10-02 15:10:00'),
(5, 1, 'hitesh dhameliya', '2018-10-15 07:40:00'),
(6, 1, 'chirag dethariya', '2018-09-20 23:00:00'),
(7, 1, 'vivek viradiya', '2018-10-04 01:00:00'),
(8, 1, '#vishal pandya', '2018-09-01 22:05:00');

Creating Simple Connection

<?php
 
$db_con = mysqli_connect("localhost","username","password","crudopet");
if (!$db_con) {
	die("Connection failed: " . mysqli_connect_error());
}
 
?>

Example 1 : table students order by studregDate asc

<?php
	$asc=mysqli_query($db_con,"select * from students order by studregDate asc");
	while($rowasc=mysqli_fetch_array($asc)){
		?>
		<tr>
			<td><?php echo $rowasc['studenid']; ?></td>
			<td><?php echo $rowasc['student_post']; ?></td>
			<td><?php echo date('M-d, Y h:i A',strtotime($rowasc['studregDate'])); ?></td>
		</tr>
		<?php
	}
?>

Example 2 : table students order by studregDate desc

<?php
	$desc=mysqli_query($db_con,"select * from students order by studregDate desc");
	while($rowdesc=mysqli_fetch_array($desc)){
		?>
		<tr>
			<td><?php echo $rowdesc['studenid']; ?></td>
			<td><?php echo $rowdesc['student_post']; ?></td>
			<td><?php echo date('M-d, Y h:i A',strtotime($rowdesc['studregDate'])); ?></td>
		</tr>
		<?php
	}
?>

Example 3 : table students order by studregDate desc limit 3

<?php
	$limit=mysqli_query($db_con,"select * from students order by studregDate desc limit 3");
	while($rowlimit=mysqli_fetch_array($limit)){
		?>
		<tr>
			<td><?php echo $rowlimit['studenid']; ?></td>
			<td><?php echo $rowlimit['student_post']; ?></td>
			<td><?php echo date('M-d, Y h:i A',strtotime($rowlimit['studregDate'])); ?></td>
		</tr>
		<?php
	}
?>

Subquery

<?php
	$sub=mysqli_query($db_con,"select * from (select * from students order by studregDate desc limit 3)as sub left join student on student.studentId=sub.studentId order by studregDate asc");
	while($low_subRow=mysqli_fetch_array($sub)){
		?>
		<tr>
			<td><?php echo $low_subRow['studenid']; ?></td>
			<td><?php echo $low_subRow['studentFname']; ?> <?php echo $low_subRow['studlname']; ?></td>
			<td><?php echo $low_subRow['student_post']; ?></td>
			<td><?php echo date('M-d, Y h:i A',strtotime($low_subRow['studregDate'])); ?></td>
		</tr>
		<?php
	}
?>

index.php

<?php include('db_con.php'); ?>
<!DOCTYPE>
<html>
<head>
<title>Simple SQL SubQuery using PHP with MySQLi</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css"" rel="nofollow">
<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>Simple SQL SubQuery in PHP as well as MySQLi</center></h2>
	</div>
	<div class="row">
		<span>order by studregDate asc</span>
		<table class="table table-bordered">
			<thead>
				<th>StudentID</th>
				<th>Stud Text</th>
				<th>Stud Reg Date</th>
			</thead>
			<tbody>
				<?php
					$asc=mysqli_query($db_con,"select * from students order by studregDate asc");
					while($rowasc=mysqli_fetch_array($asc)){
						?>
						<tr>
							<td><?php echo $rowasc['studenid']; ?></td>
							<td><?php echo $rowasc['student_post']; ?></td>
							<td><?php echo date('M-d, Y h:i A',strtotime($rowasc['studregDate'])); ?></td>
						</tr>
						<?php
					}
				?>
			</tbody>
		</table>
	</div>
	<div class="row">
		<span>order by studregDate desc</span>
		<table class="table table-bordered">
			<thead>
				<th>StudentID</th>
				<th>Stud Text</th>
				<th>Stud Reg Date</th>
			</thead>
			<tbody>
				<?php
					$desc=mysqli_query($db_con,"select * from students order by studregDate desc");
					while($rowdesc=mysqli_fetch_array($desc)){
						?>
						<tr>
							<td><?php echo $rowdesc['studenid']; ?></td>
							<td><?php echo $rowdesc['student_post']; ?></td>
							<td><?php echo date('M-d, Y h:i A',strtotime($rowdesc['studregDate'])); ?></td>
						</tr>
						<?php
					}
				?>
			</tbody>
		</table>
	</div>
	<div class="row">
		<span>Simple order by studregDate desc limit 3</span>
		<table class="table table-bordered>
			<thead>
				<th>StudentID</th>
				<th>Stud Text</th>
				<th>Stud Date</th>
			</thead>
			<tbody>
				<?php
					$limit=mysqli_query($db_con,"select * from students order by studregDate desc limit 3");
					while($rowlimit=mysqli_fetch_array($limit)){
						?>
						<tr>
							<td><?php echo $rowlimit['studenid']; ?></td>
							<td><?php echo $rowlimit['student_post']; ?></td>
							<td><?php echo date('M-d, Y h:i A',strtotime($rowlimit['studregDate'])); ?></td>
						</tr>
						<?php
					}
				?>
			</tbody>
		</table>
	</div>
	<div class="row">
		<span>(order by studregDate desc limit 3) as subquery order by studregDate asc</span>
		<table class="table">
			<thead>
				<th>StudentID</th>
				<th>student</th>
				<th>Stud Text</th>
				<th>Stud Date</th>
			</thead>
			<tbody>
				<?php
					$sub=mysqli_query($db_con,"select * from (select * from students order by studregDate desc limit 3)as sub left join student on student.studentId=sub.studentId order by studregDate asc");
					while($low_subRow=mysqli_fetch_array($sub)){
						?>
						<tr>
							<td><?php echo $low_subRow['studenid']; ?></td>
							<td><?php echo $low_subRow['studentFname']; ?> <?php echo $low_subRow['studlname']; ?></td>
							<td><?php echo $low_subRow['student_post']; ?></td>
							<td><?php echo date('M-d, Y h:i A',strtotime($low_subRow['studregDate'])); ?></td>
						</tr>
						<?php
					}
				?>
			</tbody>
		</table>
	</div>
</div>
</body>
</html>

You are Most welcome in my youtube Channel Please subscribe my channel. and give me FeedBack.
More Details……
Angularjs Example

Example

I hope you have Got What is MySQL WHERE Clause with Examples – AND, OR, IN, NOT IN And how it works.I would Like to have FeedBack From My Blog(Pakainfo.com) readers.Your Valuable FeedBack,Any Question,or any Comments about This Article(Pakainfo.com) Are Most Always Welcome.