PHP MySQL Select PHP
- PHP Tutorial
- What is PHP
- Install PHP
- PHP Example
- PHP Echo
- PHP Print
- PHP Variables
- PHP $ and $$ Variables
- PHP Constants
- Magic Constants
- PHP Data Types
- PHP Operators
- PHP Comments
- PHP Boolean
- PHP is_bool() function
- PHP Integer
- PHP is_int() function
- PHP Float
- PHP is_float Function
- PHP Compound Types
- PHP Special Types
- PHP is_null() function
- PHP If Else
- PHP Switch
- PHP For Loop
- PHP While Loop
- PHP do while loop
- PHP Break
- PHP Functions
- PHP Parameterized Function
- PHP Call By Value
- PHP Call By Reference
- PHP Default Argument Values Function
- PHP Variable Length Argument Function
- PHP Recursive Function
- PHP Arrays
- PHP Indexed Array
- PHP Associative Array
- PHP Multidimensional Array
- PHP Array Functions
- PHP String
- PHP String Functions
- PHP Math functions
- PHP Form Handling | GET and POST method
- PHP Include File
- PHP Cookie
- PHP Session
- PHP File Handling
- PHP Open File
- PHP Read File
- PHP Write File
- PHP Append to File
- PHP Delete File
- PHP File Upload
- PHP Download File
- PHP Mail
- PHP MySQL Connect
- PHP MySQL Create Database
- PHP MySQL Create Table Example
- PHP MySQL Insert Record
- PHP Prepared Statements
- PHP MySQL Update Record
- PHP MySQL Delete Record
- PHP MySQL Select
- PHP MySQL Order By
- PHP Limit Data MySql
- PHP JSON
- PHP XML Parsers
- PHP SimpleXML Parser
- PHP SimpleXML - Get Node/Attribute Values
- PHP XML Expat Parser
- PHP XML DOM Parser
- PHP - AJAX intro
- MySQL CREATE VIEW
PHP MySQL Select
PHP MySQL Select
Select Data From a MySQL Database
The SELECT statement is used to select data from one or more tables:
or we can use the * character to select ALL columns from a table:
Select Data With MySQLi
The following example selects the id, firstname and lastname columns from the MyGuests table and displays it on the page:
Example (MySQLi Object-oriented)
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
Code lines to explain from the example above:
First, we set up an SQL query that selects the id, firstname and lastname columns from the MyGuests table. The next line of code runs the query and puts the resulting data into a variable called $result.
Then, the function num_rows() checks if there are more than zero rows returned.
If there are more than zero rows returned, the function fetch_assoc() puts all the results into an associative array that we can loop through. The while() loop loops through the result set and outputs the data from the id, firstname and lastname columns.
The following example shows the same as the example above, in the MySQLi procedural way:
Example (MySQLi Procedural)
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
You can also put the result in an HTML table:
Example (MySQLi Object-oriented)
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<table><tr><th>ID</th><th>Name</th></tr>";
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr><td>".$row["id"]."</td><td>".$row["firstname"]." ".$row["lastname"]."</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
$conn->close();
?>
Select Data With PDO (+ Prepared Statements)
The following example uses prepared statements.
It selects the id, firstname and lastname columns from the MyGuests table and displays it in an HTML table:
Example (PDO)
echo "<table style='border: solid 1px black;'>";
echo "<tr><th>Id</th><th>Firstname</th><th>Lastname</th></tr>";
class TableRows extends RecursiveIteratorIterator {
function __construct($it) {
parent::__construct($it, self::LEAVES_ONLY);
}
function current() {
return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>";
}
function beginChildren() {
echo "<tr>";
}
function endChildren() {
echo "</tr>" . "\n";
}
}
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests");
$stmt->execute();
// set the resulting array to associative
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
echo $v;
}
}
catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
echo "</table>";
?>
Download free E-book of PHP
#askProgrammers Learn Programming for Free
Join Programmers Community on Telegram
Talk with Experienced Programmers
Just drop a message, we will solve your queries
