Introduction
PHP and MySQL are widely used open-source technologies for web development. PHP is a server-side scripting language that allows you to create dynamic web pages and applications. MySQL is a relational database management system used for storing and retrieving data. In this article, we'll discuss how to search a database using PHP and MySQL and display the results on a web page.
Connecting to a Database
Before we can perform a search in a database, we need to connect to it using PHP. The following code demonstrates how to connect to a database using PHP and MySQL:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
The mysqli_connect() function is used to connect to a database. The function takes four parameters:
$servername– the name of the server where the database is located$username– the username used to connect to the database$password– the password for the username$dbname– the name of the database
Searching a Database
Once we have connected to the database, we can perform a search. The following code demonstrates how to perform a simple search for data in a database:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$searchTerm = $_GET['term'];
$sql = "SELECT * FROM table_name WHERE column_name LIKE '%".$searchTerm."%'";
$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["name"]. "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
The code above first performs a connection to the database, and then retrieves the search term from the URL using the $_GET superglobal. The search term is then used in a SQL SELECT statement to retrieve data from the database. The mysqli_query() function is used to execute the SQL statement, and the mysqli_num_rows() function is used to check if there are any results. If there are results, they are displayed on the web page using a while loop.
Displaying the Results
The results of the search can be displayed on a web page in various ways, depending on the requirements of your project. In the code
Sorting Results
In addition to searching a database, it is often necessary to sort the results. This can be done in PHP and MySQL by including an ORDER BY clause in the SQL SELECT statement. The following code demonstrates how to sort the results of a search in ascending order based on a specific column:
$sql = "SELECT * FROM table_name WHERE column_name LIKE '%".$searchTerm."%' ORDER BY column_name ASC";
To sort the results in descending order, you can use the DESC keyword instead of ASC:
$sql = "SELECT * FROM table_name WHERE column_name LIKE '%".$searchTerm."%' ORDER BY column_name DESC";
Pagination
When displaying a large number of results, it is often necessary to break the results into multiple pages. This can be achieved using pagination. The following code demonstrates how to implement pagination in PHP and MySQL:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$searchTerm = $_GET['term'];
$resultsPerPage = 5;
$page = 1;
if(isset($_GET['page'])) {
$page = $_GET['page'];
}
$start = ($page - 1) * $resultsPerPage;
$sql = "SELECT * FROM table_name WHERE column_name LIKE '%".$searchTerm."%' ORDER BY column_name ASC LIMIT $start, $resultsPerPage";
$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["name"]. "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
In the code above, we have added two new variables: $resultsPerPage and $page. The $resultsPerPage variable is used to specify the number of results to display on each page, while the $page variable is used to keep track of which page we are currently on. We use the LIMIT clause in the SQL SELECT statement to limit the number of results returned based on the $start and $resultsPerPage variables. The $start variable is calculated by multiplying the current page number by the number of results per page.
Conclusion
In this article, we have discussed how to search a database using PHP and MySQL and display the results on a web page. We have also discussed how to sort the results and implement pagination. By using these techniques, you can create dynamic and interactive web applications that allow users to search and retrieve data from a database.
Popular questions
- What is the difference between
mysqlandmysqliin PHP?
mysql is the original extension used for connecting to MySQL databases in PHP, while mysqli stands for "MySQL Improved". The mysqli extension provides improved functionality and security over the mysql extension, and is the recommended method for connecting to MySQL databases in PHP.
- How do you connect to a MySQL database using PHP?
To connect to a MySQL database using PHP, you can use the mysqli_connect() function. The function takes four parameters: the server name, username, password, and database name. For example:
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
- How do you perform a search in a MySQL database using PHP?
To perform a search in a MySQL database using PHP, you can use a SELECT statement in SQL. For example:
$searchTerm = $_GET['term'];
$sql = "SELECT * FROM table_name WHERE column_name LIKE '%".$searchTerm."%'";
$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["name"]. "<br>";
}
} else {
echo "0 results";
}
In the code above, we are using the mysqli_query() function to execute the SQL SELECT statement and retrieve the results. The results are then displayed using a while loop and the mysqli_fetch_assoc() function.
- How do you display the results of a search on a web page in PHP?
To display the results of a search on a web page in PHP, you can use a while loop and the mysqli_fetch_assoc() function. For example:
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
}
} else {
echo "0 results";
}
In the code above, we are using the mysqli_num_rows() function to check if there are any results. If there are, we use a while loop to display the results. The mysqli_fetch_assoc() function is used to retrieve the data for each row in the results.
- How do you sort the results of a search in a MySQL database using PHP?
To sort the results of a search in a MySQL database
Tag
Database
