The SELECT
statement is used to retrieve data from a MySQL database. It is the most commonly used MySQL statement, and it is used to select data from a database table. The SELECT
statement can be used in a number of ways, and it can be used with various clauses such as WHERE
, GROUP BY
, and HAVING
to filter and sort the data.
Here is the basic syntax of the SELECT
statement:
Syntax:
SELECT column_name_1, column_name_2, ...
FROM table_name
WHERE condition;
- The
SELECT
statement allows you to specify which columns you want to retrieve from the table. You can also use the*
wildcard to select all columns. - The
FROM
clause specifies the table from which you want to retrieve the data. - The
WHERE
clause is optional and can be used to specify a condition that must be met for the rows to be included in the result set.
For example, the following SELECT
statement retrieves all rows from the users
table where the age
column is greater than 24:
Example:
SELECT *
FROM users
WHERE age > 24;
PHP MySQL SELECT Statement
The tutorial below will demonstrate how to use the SELECT
statement in a PHP script to retrieve data from a MySQL database.
Connecting to a MySQL Database
Before executing the SELECT
statement, it is a must to establish a connection to the MySQL database. This can be done in PHP using the mysqli_connect()
function. This function takes three arguments: hostname, username, and password.
Example:
$hostname = "localhost";
$username = "your_username";
$password = "your_password";
$conn = mysqli_connect($hostname, $username, $password);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
Selecting Data from a MySQL Database Table
Once a connection to the MySQL database is established, you can use the mysqli_query()
function to execute a SELECT
statement. This function takes two arguments: a connection to the database and the select statement to execute.
Example:
$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);
The $result
variable will contain a result set, which is a set of rows returned by the SELECT
statement. To access the rows in the result set, you can use the mysqli_fetch_assoc()
function. This function fetches a single row from the result set as an associative array, where the keys are the column names and the values are the column values.
Example:
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"]. " - Name: " . $row["name"]. " " . $row["email"]. "<br>";
}
The while
loop will iterate over the rows in the result set, and the PHP echo
statement will print each row's ID, Name, and Email values.
Display the Result of a MySQL Query in an HTML Table
To display the result of the MySQL query in an HTML table, the following steps can be followed:
- Create an HTML table using the
<table>
element. - Iterate through the result set, and for each row, create a new row in the table using the
<tr>
element. Then, for each column in the row, create a new cell in the table using the<td>
element and print the value of the column in the cell.
Here is an example of how this can be done:
<table border='1'>
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
</tr>
<?php while($row= mysqli_fetch_array($result)){ ?>
<tr>
<td><?php echo $row["id"]; ?></td>
<td><?php echo $row["name"]; ?></td>
<td><?php echo $row["email"]; ?></td>
</tr>
<?php } ?>