Here's what Web designers need to know to create dynamic, database-driven Web sites
To be on the cutting edge, Web sites need to serve up HTML, CSS, and products specific to the needs of different customers using different browsers. An effective e-commerce site gathers information about users and provides information they need to get the desired result.
PHP scripting language with a MySQL back-end database offers an
effective way to design sites that meet these requirements. This full updated 4th Edition of PHP & MySQL For Dummies gets you quickly up to speed, even if your experience is limited.
- Explains the easy way to install and set up PHP and MySQL using XAMPP, so it works the same on Linux, Mac, and Windows
- Shows you how to secure files on a Web host and how to write secure code
- Packed
with useful and understandable code examples for Web site creators who are not professional programmers
- Fully updated to ensure your code will be compliant based on PHP 5.3 and MySQL 5.1.31
- Provides clear, accurate code examples
PHP & MySQL For Dummies, 4th Edition provides what you need to know to create sites that get results.
Note: CD-ROM/DVD and
other supplementary materials are not included as part of eBook file.
In this tutorial you will learn how to select the records from a MySQL database table based on specific conditions using PHP.
Filtering the Records
The WHERE clause is used to extract only those records that fulfill a specified condition.
The basic syntax of the WHERE clause can be given with:
SELECT column_name(s) FROM table_name
WHERE column_name operator value
Let's make a SQL query using the WHERE clause in SELECT statement, after that we'll execute this query through passing it to the PHP mysqli_query() function to get the filtered data.
Consider we've a persons table inside the demo database that has following records:
+----+------------+-----------+----------------------+
| id | first_name | last_name | email |
+----+------------+-----------+----------------------+
| 1 | Peter | Parker | |
| 2 | John | Rambo | |
| 3 | Clark | Kent | |
| 4 | John | Carter | |
| 5 | Harry | Potter | |
+----+------------+-----------+----------------------+
The following PHP code selects all the rows from the persons table where first_name='john':
Example
Procedural Object Oriented PDO
Download
0){
echo "";
echo "";
echo "| id | ";
echo "first_name | ";
echo "last_name | ";
echo "email | ";
echo "
";
while($row = mysqli_fetch_array($result)){
echo "";
echo "| " . $row['id'] . " | ";
echo "" . $row['first_name'] . " | ";
echo "" . $row['last_name'] . " | ";
echo "" . $row['email'] . " | ";
echo "
";
}
echo "
";
// Close result set
mysqli_free_result($result);
} else{
echo "No records matching your query were found.";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
// Close connection
mysqli_close($link);
?>
connect_error);
}
// Attempt select query execution
$sql = "SELECT * FROM persons WHERE first_name='john'";
if($result = $mysqli->query($sql)){
if($result->num_rows > 0){
echo "";
echo "";
echo "| id | ";
echo "first_name | ";
echo "last_name | ";
echo "email | ";
echo "
";
while($row = $result->fetch_array()){
echo "";
echo "| " . $row['id'] . " | ";
echo "" . $row['first_name'] . " | ";
echo "" . $row['last_name'] . " | ";
echo "" . $row['email'] . " | ";
echo "
";
}
echo "
";
// Free result set
$result->free();
} else{
echo "No records matching your query were found.";
}
} else{
echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
}
// Close connection
$mysqli->close();
?>
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 persons WHERE first_name='john'";
$result = $pdo->query($sql);
if($result->rowCount() > 0){
echo "";
echo "";
echo "| id | ";
echo "first_name | ";
echo "last_name | ";
echo "email | ";
echo "
";
while($row = $result->fetch()){
echo "";
echo "| " . $row['id'] . " | ";
echo "" . $row['first_name'] . " | ";
echo "" . $row['last_name'] . " | ";
echo "" . $row['email'] . " | ";
echo "
";
}
echo "
";
// 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($pdo);
?>
After filtration the result set will look something
like this:
+----+------------+-----------+---------------------+
| id | first_name | last_name | email |
+----+------------+-----------+---------------------+
| 2 | John | Rambo | |
| 4 | John | Carter | |
+----+------------+-----------+---------------------+
