PHP MySQL select Useing WHERE Filter
PHP-MySQLi Tutorial » PHP MySQL select WHERE
How to used WHERE filter to extract records from database using PHP MySQLi?
How to used WHERE filter using PHP MySQLi and PDO
WHERE clause is used to filter and extract records only those that fulfill a specified condition.
Syntax:
We have creat table "Students", with seven columns as: "id", "first_name", "last-name", "email", "age","password" and "date" and we select a specific column from database.
Select and Filter Data With MySQLi useing WHERE clause:
Example with (MySQLi Object-oriented)
<?php
$server_name = "localhost";
$user_name = "username";
$password = "password";
$db_name = "myDB";// Create connection
$con = new mysqli($server_name, $user_name, $password, $db_name);
// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}$sql = "SELECT id, first-name, last-name FROM Students WHERE age='24'";
if ($result->num_rows > 0) {
$result = $con->query($sql);
// output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["first_name"]. " " . $row["last_name"]. "<br>";
}
} else {
echo "0 results";
}
$con->close();
?>
We explain code lines from the above example:
First, we set up the SQL query that selects the id, first_name and last_name columns from the Students table where age is "23".
The next line of PHP code will runs the query and puts the result data into a variable named $result.
Next, function num_rows() checks if in database Students are more than zero rows returned.
If we have are more than zero rows returned, then the function fetch_assoc() add all the results into an associative array that we can loop through. The while() loops through the result set and outputs the data from the id, first_name and last_name columns.
PHP MySQL Use WHERE Filter ( MySQLi Object-oriented ) with HTML table - example
Open a Connection to MySQL
You can also put the result in an HTML table:
The following examples use WHERE Filter to select specific data from database "Students" table:
<?php
$server_name = "localhost";
$user_name = "username";
$password = "password";
$db_name = "myDB";// Create connection
$con = new mysqli($server_name, $user_name, $password, $db_name);
// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}$sql = "SELECT id, first_name, last_name FROM Students WHERE age=24";
$result = $con->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["first_name"]." ".$row["last_name"]."</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
$con->close();
?>
If you remember from the preceding chapter, id field was marked with AUTO_INCREMENT flag. This modifier tells the MySQL to automatically assign a value to this field if it is left unspecified, by incrementing the previous value by 1.
Function num_rows() checks if in database are more than zero rows.
If we have more than zero rows then returned, function fetch_assoc() where puts all the results into an associative array that we can loop through.
The while() - loops through the result set and outputs the data from the id, first_name and last_name columns.
PHP MySQL Use WHERE Filter ( PHP MySQLi Procedural )
How to Use WHERE Filter to select specific column from mysql database tables using PHP MySQLi Procedural
<?php
$server_name = "localhost";
$user_name = "username";
$password = "password";
$db_name = "myDB";// Create connection
$con = mysqli_connect($server_name, $user_name, $password, $db_name);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}$sql = "SELECT id, first_name, last_name FROM Students WHERE agee=24";
$result = mysqli_query($con, $sql);if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"]. " - Name: " . $row["first_name"]. " " . $row["last_name"]. "<br>";
}
} else {
echo "0 results";
}mysqli_close($con);
?>
PHP MySQL Use The WHERE Clause, mysql select where in, mysql select where length of string greater than, select random where, select database where, update here, insert where, into where, select outfile where, select last row where
PDO get / select specific column data from database - example
Example (PDO) select specific column data from database - is a little bit different:
It selects the id, first_name and last_name columns from the Students table where the age is "24", and displays it in an HTML table:
<?php
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";
}
}$server_name = "localhost";
$user_name = "username";
$password = "password";
$db_name = "my_DBP_DO";try {
$con = new PDO("mysql:host=$server_name;dbname=$db_name", $user_name, $password);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("SELECT id, first_name, last_name FROM Students WHERE age=24");
$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();
}
$con = null;
echo "</table>";
?>
PHP MySQL Use The WHERE Clause, mysql select where in, mysql select where length of string greater than, select random where, select database where, update here, insert where, into where, select outfile where, select last row where
PHP MySQL select WHERE - php mysqli
This tool makes it easy to create, adjust, and experiment with custom colors for the web.
Magnews2 is a modern and creative free magazine and news website template that will help you kick off your online project in style.
Find here examples of creative and unique website layouts.
Find here examples of creative and unique website CSS HTML menu.