PHP MySQLi WHERE
PHP Tutorial » PHP MySQLi WHERE
How to select rows from MySQL database table in PHP based on specific conditions using where clause.
The WHERE condition is used to extract only certain records that fulfill a specified condition.
Syntax:
In this chapter as well as in the following chapters we will show you three methods of working with PHP MySQLi create DataBase:
- MySQLi (object oriented)
- MySQLi (procedural)
- PDO
We will create a table named "Users", with five columns as: "id", "first_name", "last_name", "user_name", "email". The following PHP code selects the row from the Users table where last_name='Carter':
| ID | First Name | Last Name | UserName | |
|---|---|---|---|---|
| 1 | Clark | Kent | clarkkent | clarkkent@mail.com |
| 2 | John | Carter | johncarter | johncarter@mail.com |
| 3 | Peter | Parker | peterparker | peterparker@mail.com |
PHP MySQLi WHERE (object oriented)
The following PHP code selects the row from the "Users" table where last_name='Carter':
<?php
$server_name = "localhost";
$user_name = "my_username";
$password = "my_password";
$db_name = "my_DB";// Create connection
$link = new mysqli($server_name, $user_name,
$password, $db_name);// Check connection
if ($link->connect_error) {
die("Connection failed: " . $link->connect_error);
}
// Create database
$sql = "SELECT id, first_name, last_name FROM Users WHERE last_name='Carter'";
$result = $link->query($sql);if ($result->num_rows > 0) {
// 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";
}
$link->close();
?>
function num_rows() checks table if there are more than zero rows returned.
If it is are more than zero rows returned, fetch_assoc() function add all the results into an associative array.
while() loop loops through the result set and outputs data from: id, first_name and last_name columns.
Related subjects:
PHP myadmin update date
Insert multiple rows
PHP select / view data
Conect to MySQLi
Tags: PHP MySQLi where, select query in php with where clause, php mysql where in array, php mysql select where variable example, select query with multiple where condition in php,
php mysql where: in array, like, in, not equal, clause, or, is null, not null, clause with variable, date today
PHP MySQLi WHERE (Procedural)
Attempt MySQL server connection. Assuming you are running MySQL server with default setting ("localhost", "root", "your_password")
The following PHP code selects the row from the "Users" table where last_name='Carter':
<?php
$server_name = "localhost";
$user_name = "my_username";
$password = "my_password";
$db_name = "my_DB";// Create connection
$link = mysqli_connect($server_name, $user_name,
$password, $db_name);// Check connection
if (!$link) {
die("Connection failed: " . mysqli_connect_error());
}
// Create database
$sql = "SELECT id, first_name, last_name FROM Users WHERE last_name='Carter'";
$result = mysqli_query($link, $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($link);
?>
PHP MySQLi WHERE using (PDO) extension.
PDO extension. Attempt MySQL server connection. Assuming you are running MySQL server with default setting ("localhost", "root", "your_password")
The next PDO example display row where last_name='Carter' from 'User' table
<?php
$server_name = "localhost";
$user_name = "my_username";
$password = "my_password";
$db_name = "my_DB";
try {
$link = new PDO("mysql:host=$server_name;dbname=$db_name", $user_name, $password);// set the PDO error mode to exception
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}
// Attempt select query execution
try{
$sql = "SELECT * FROM Users WHERE last_name='Carter'";
$result = $link->query($sql);
if($result->rowCount() > 0){
echo "<table align='center' style='width:99%'>";
echo "<tr style='background:#0C6; padding:8px'>";
echo "<th style='padding:8px'>ID</th>";
echo "<th style='padding:8px'>First Name</th>";
echo "<th style='padding:8px'>Last Name</th>";
echo "<th style='padding:8px'>Email</th>";
echo "</tr>";
while($row = $result->fetch()){
echo "<tr>";
echo "<td style='padding:8px'>" . $row['id'] . "</td>";
echo "<td style='padding:8px'>" . $row['first_name'] . "</td>";
echo "<td style='padding:8px'>" . $row['last_name'] . "</td>";
echo "<td style='padding:8px'>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
// Free result set
unset($result);
} else{
echo "No records matching your query were found.";
}
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
// Close connection
unset($link);
?>
The outcome
| ID | First Name | Last Name | UserName | |
|---|---|---|---|---|
| 1 | John | Carter | johncarter | johncarter@mail.com |
PHP MySQLi where, select query in php with where clause, php mysql where in array, php mysql select where variable example, select query with multiple where condition in php, php mysql where: in array, like, in, not equal, clause, or, is null, not null, clause with variable, date today
PHP MySQLi WHERE - php tutorial
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.