We learned how to link a PHP web application to a MySQL database server in a previous PHP post. With the help of a PHP form example, we will learn how to pick data from a mysql database in this PHP lesson.
We create a simple select query in PHP form with a textbox and a button control in this example. While clicking the select button, we select data from the database to display on the webpage. We enter an integer id value in a textbox, and the Id-by-Id record is displayed on the result screen.
Example
<html>
<head>
<title>PHP MySQL connection example</title>
</head>
<body>
<form method="post">
Enter Record ID : <input type="text" name="id"><br/>
<input type="submit" value="SELECT" name="Submit1"> <br/>
</form>
<?php
if(isset($_POST['Submit1']))
{
$username = "username";
$password = "password";
$hostname = "localhost";
$database="School";
//connection to the mysql database,
$dbhandle = mysqli_connect($hostname, $username, $password,$database);
if(!empty($_POST["id"]))
{
$result = mysqli_query($dbhandle, "SELECT ID, Name, City FROM StudentMst where ID=".$_POST["id"] );
}
else
{
$result = mysqli_query($dbhandle, "SELECT ID, Name, City FROM StudentMst" );
}
//fetch the data from the database
while ($row = mysqli_fetch_array($result)) {
echo "ID:" .$row{'ID'}." Name:".$row{'Name'}." City: ". $row{'City'}."<br>";
}
//close the connection
mysqli_close($dbhandle);
}
?>
</body>
</form>
Output
What Is a SELECT Query?
To get data from the MySQL database, use the select query in PHP command. This command can be used at the mysql> prompt as well as in any PHP script.
The following is a generic select query in PHP syntax for retrieving data from a MySQL table using the SELECT command.
In SQL, ‘select' queries are used to retrieve one or more records from a table/database, and can also support various condition clauses depending on the needs of the user. The resulting data set is temporarily kept on an output table set, also known as a "result-set." If no condition statements are included in the ‘select' query, it will return the entire table. If there are filters or conditions included in the ‘select' query, it will return only the data you want.
Syntax of SQL
One of the most basic commands in a relational database management system is select. The select query in PHP keyword can be used as a prefix to select records from a table. The select query returns a set of records from one or more tables specified.
- SELECT is a command that can be used to choose a record from a table.
- The columns of the table My table name are column A>, column B>, and so on.
- The name of a table is my table name>.
Example
Query: SELECT Student_ID, First_name, Last_name from Student;
Output
SELECT Query using Object-Oriented Method
SELECT Query Using PDO Method
There are numerous ways to use PDO to run a select query in PHP query, which differ primarily in terms of the presence of parameters, the type of parameters, and the return type.
We’ll look at examples for each circumstance so you may pick the one that best fits your needs. Simply ensure that you have a properly set PDO connection variable in order to perform SQL queries using PDO and receive error notifications.
However, most queries require the usage of one or more variables, in which case a prepared statement (also known as a parameterized query) should be used, which involves first creating a query with parameters (or placeholder marks) and then running it, sending variables separately.
Both positional and named placeholders are available in select query in PHP. Positional placeholders for simple queries are preferable because they are less verbose; however, opinions on the same may vary.
Selecting Multiple Rows
If rows are to be handled one by one, this procedure may be advised. For example, if a select query in PHP processing is the only action required, or if the data must be pre-processed in some way before usage.
However, invoking the wonderful helper method fetchAll is the most preferable technique to get several rows that will be shown on a web page (). It will save all of the rows returned by a query in a PHP array, which can then be used to print data using a template (which is considered much better than echoing the data right during the fetch process).
How to Implement a SELECT Query?
The relational database can be used to execute the select statement indicated above. To choose the records, we can utilize a variety of select statement queries. A result set is always returned by the choose statement. There may be zero (0), one, or several records in this result set. We can use a variety of other options in the choose statement to attain the desired outcome. The JOIN keyword can be used to select records from two or more tables. To get records from one or more tables, you can use two or more select statements in a variety of ways. Any table should have a primary key so that each record may be uniquely identified.
With the select query in PHP statement, we may use the optional clause listed below:
- WHERE: We've already seen examples of this.
- Before utilizing the aggregate function, you must first GROUP BY.
- HAVING: We can use this over the GROUP BY statement to perform an aggregate function.
- ORDER BY: This can be used in conjunction with SELECT to sort the order of the results.
- AS: We've seen this before while trying to find the total number of records in a table. This AS can be used to create an alias for the selected column or the entire table.
Conclusion
The SQL select statement can be used to choose the required columns or records based on the business requirements. The select query in PHP command is used to select a record from a table in almost every relational database. Select can be used in a variety of ways. We can choose records based on conditions. On one or more tables, the select operation can be used. With the SELECT statements, we may use a variety of different commands. Relational database management systems include MySQL and ORACLE.
If you want to learn more about select query in PHP and other related topics, enroll in the Full Stack Web Development course from Simplilearn and accelerate your career as a software developer and become a full-stack technologist.