Easy To Learn PHP MySQL SELECT Statement

PHP MySQL SELECT Statement

The MySQL SELECT statement is used to fetch data from the one or more tables in MySQL. We can retrieve records of all fields or specified fields.

Syntax

SELECT column_name FROM table_name

We can use the * character to select ALL columns from a table :

Syntax

SELECT * FROM table_name

SELECT Statement With MySQLi

The following example selects the stu_id, stu_name, college, email and mobile columns from the student_details table and displays it on the page :

MySQLi SELECT(Procedural) for specified fields

<!DOCTYPE html>
<html>
<head>
	<title>PHP MySQL Select Statement for specified fields</title>
</head>
<body>

<?php

	// Create connection
	$ftl_connect = mysqli_connect("localhost", "root", "", "ftl_db");
	// Check connection
	if (!$ftl_connect) {
		die("Connection failed: " . mysqli_connect_error());
	}
	
	$sql = "SELECT stu_id, stu_name, college FROM student_details";
	$result = mysqli_query($ftl_connect, $sql);
	
	if (mysqli_num_rows($result) > 0) {
		// output data of each row
		while($row = mysqli_fetch_assoc($result)) {
			echo "Student Id : " . $row["stu_id"]. " <br /> Name : " . $row["stu_name"]. " <br /> College : " . $row["college"]. "<br /> <br />";
		}
	} else {
		echo "0 results";
	}
	
	// Close Connection
	mysqli_close($ftl_connect);
	
?> 

</body>
</html>

Output :

MySQLi SELECT(Procedural) : all fields

Use this syntax : SELECT * FROM table_name

<!DOCTYPE html>
<html>
<head>
	<title>PHP MySQL Select Statement for all fields</title>
</head>
<body>

<?php
// Create connection
$ftl_connect = mysqli_connect("localhost", "root", "", "ftl_db");
// Check connection
if (!$ftl_connect) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT * FROM student_details";
$result = mysqli_query($ftl_connect, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
	echo "<table border='1'><tr><th>Stu_ID</th><th>Student Name</th><th>College</th><th>Email-ID</th><th>Mobile</th></tr>";
    while($row = mysqli_fetch_assoc($result)) {
        echo   "<tr><td>".$row["stu_id"]."</td><td>".$row["stu_name"]."</td><td>".$row["college"]."</td><td>".$row["email"]."</td><td>".$row["mobile"]."</td></tr>";
    }
	echo "</table>";
} else {
    echo "0 results";
}

mysqli_close($ftl_connect);
?> 

</body>
</html>

Output :

MySQLi SELECT(Object-oriented) : all fields

<!DOCTYPE html>
<html>
<head>
	<title>PHP MySQL Select Statement for all fields</title>
</head>
<body>

<?php

	// Create connection
	$ftl_connect = mysqli_connect("localhost", "root", "", "ftl_db");
	// Check connection
	if ($ftl_connect->connect_error) {
		die("Connection failed: " . $ftl_connect->connect_error);
	} 
	
	$sql = "SELECT * FROM student_details";
	$result = $ftl_connect->query($sql);
	
	if ($result->num_rows > 0) {
		// output data of each row
		echo "<table border='1'><tr><th>Stu_ID</th><th>Student Name</th><th>College</th><th>Email-ID</th><th>Mobile</th></tr>";
		while($row = mysqli_fetch_assoc($result)) {
			echo   "<tr><td>".$row["stu_id"]."</td><td>".$row["stu_name"]."</td><td>".$row["college"]."</td><td>".$row["email"]."</td><td>".$row["mobile"]."</td></tr>";
		}
		echo "</table>";
	} else {
		echo "0 results";
	}
	//Close Connection
	$ftl_connect->close();
	
?> 

</body>
</html>

Output :

MySQL SELECT(PDO + Prepared Statements) all fields

It selects the stu_id, stu_name and mobile columns from the student_details table and displays it in an HTML table :

<!DOCTYPE html>
<html>
<head>
	<title>PHP MySQL Select Statement</title>
</head>
<body>

<?php
	//echo "<table border='1'><tr><th>Stu_ID</th><th>Student Name</th><th>College</th><th>Email-ID</th><th>Mobile</th></tr>";
	echo "<table border='1'><tr><th>Stu_ID</th><th>Student Name</th><th>Mobile</th></tr>";
	
	class TableRows extends RecursiveIteratorIterator { 
		function __construct($it) { 
			parent::__construct($it, self::LEAVES_ONLY); 
		}
	
		function current() {
			return "<td>" . parent::current(). "</td>";
		}
	
		function beginChildren() { 
			echo "<tr>"; 
		} 
	
		function endChildren() { 
			echo "</tr>" . "\n";
		} 
	} 

	try {
		$ftl_connect = new PDO("mysql:host=localhost;dbname=ftl_db", "root", "");
		$ftl_connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		//$stmt = $conn->prepare("SELECT * FROM student_details"); 
		$st_de = $ftl_connect->prepare("SELECT stu_id, stu_name, mobile FROM student_details"); 
		$st_de->execute();
	
		// set the resulting array to associative
		$result = $st_de->setFetchMode(PDO::FETCH_ASSOC); 
	
		foreach(new TableRows(new RecursiveArrayIterator($st_de->fetchAll())) as $k=>$v) { 
			echo $v;
		}
	}
	catch(PDOException $e) {
		echo "Error: " . $e->getMessage();
	}
	//Close Connection
	$ftl_connect = null;
	echo "</table>";
?> 



</body>
</html>

Output :

PHP Tutorial