MySQL SELECT Statement

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:

  1. Create an HTML table using the <table> element.
  2. 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 } ?>