PHP MySQL Select Data

This tutorial walks you through the steps to select data in a table from a MySQL database using PHP in XAMPP stack.

Prerequisites

Make sure you have set up the XAMPP stack in your system. The following guide explains how to setup XAMPP stack in Linux.

• How To Install XAMPP In Linux

Setting up XAMPP is much easier than LAMP and LEMP stacks. So, we will will be using XAMPP stack throughout this guide.

After setting up the XAMPP stack, you need to create a MySQL database and table inside the database. Refer to the following guide to know how to create MySQL database and table in XAMPP stack.

• Create MySQL Database And Table Using PHP In XAMPP

For demonstration purpose, I am going to create a table named "sales" in a database called "my_company" with the below schema and records in my XAMPP stack.

Selecting Data From A MySQL Database Using PHP

MySQL has SELECT command to get data from the table. Using the SELECT command, we can get all data from a particular column or all columns.

Query Syntax:

SELECT column1,column2,.,column n from table_name;

Where, columns are the column names to be selected.

If you want to display all the columns, you can use * instead of column names.

Query Syntax:

SELECT * from table_name;

Steps

1. Specify the MySQL servername, username, password and the database name in your PHP code.

Here, the servername is localhost, username is root and password is empty. And the database name is my_company, we are creating a table called sales inside this database.

2. Create a connection using the above details.

By using the mysqli_connect() function, we will establish a connection. It will take three parameters. First will be the servername, second is the username and last is password. It will also take a database name which is optional here, because we are just creating connection.

Code:

$connection = mysqli_connect($server_name, $user_name, $password,$database_name);

3. Check the Connection

We can check the connection using the mysqli_connect_error() function specified in an if condition. This function will represent an error, if the connection is failed.

4. Specify the SQL Query to select particular or all columns records from the table.

In this step, we can specify the SQL query to select columns from the table into a variable. Let the database name be My_company and we are storing it in a variable named query. The table name is Sales that has three columns.

Code:

$query = "SELECT column1,…. from Sales";

5. Store the selected results into the final variable using mysqli_query() function. It will take connection and query as parameters.

Code:

mysqli_query($connection, $query);

6. Get the rows one by one from the variable called "final" using mysqli_num_rows() function. After that fetch the results by iterating through a while loop using mysqli_fetch_assoc() function. It will take the "final" variable as a parameter. Specify the column names to be displayed inside the while loop.

Code:

if (mysqli_num_rows($final) > 0) {
 //get the output of each row
  while($i = mysqli_fetch_assoc($final)) {
    echo $i["column1”],…………..;
  }
} else {
  echo "No results";
}

7. Close the connection

This is the last step where we have to close the connection by using the mysqli_close() function.

Code:

mysqli_close($connection);

Now, let us write a sample PHP code based on the above steps. Here, we will be using the following mysqli functions to fetch and return the data.

• mysqli_num_rows() to get the data from the actual result.
• mysqli_fetch_assoc() to fetch the rows from the table one by one.
• mysqli_close() to close the connection.

PHP Code

Example Code 1:

In this example, we will select the id and name columns from the Sales table and display the result in the PHP page.

Create a new file named select.php under the /htdocs folder with the following contents in it.

Heads Up: If you use Linux, the htdocs folder will be under /opt/lampp/ directory. If you're on Windows, the htdocs will be usually in C:\xampp\ folder.

<?php
//specify the server name and here it is localhost
$server_name = "localhost";

//specify the username - here it is root
$user_name = "root";

//specify the password - it is empty
$password = "";

//specify the database name - "my_company"
$database_name = "my_company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password,$database_name);

//sql query to select particular columns
//select id and name columns
$query = "SELECT id,name from sales";

#get the result
$final = mysqli_query($connection, $query);

if (mysqli_num_rows($final) > 0) {
 //get the output of each row
  while($i = mysqli_fetch_assoc($final)) {
      //get id and name columns
    echo "id: " . $i["id"]. "  ----> name: " . $i["name"]. "<br>";
  }
} else {
  echo "No results";
}

//close the connection
mysqli_close($connection);
?>

Open your web browser and point it to http://localhost/select.php URL. You will see that the data is selected and displayed.

Example Code 2:

In this example, we will get all columns present in the table.

Open the same select.php file and update it with the contents.

<?php
//specify the server name and here it is localhost
$server_name = "localhost";

//specify the username - here it is root
$user_name = "root";

//specify the password - it is empty
$password = "";

//specify the database name - "my_company"
$database_name = "my_company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password,$database_name);

//sql query to select particular columns
//select all columns
$query = "SELECT * from sales";

#get the result
$final = mysqli_query($connection, $query);

if (mysqli_num_rows($final) > 0) {
 //get the output of each row
  while($i = mysqli_fetch_assoc($final)) {
      //get all columns
    echo "id: " . $i["id"]. "  ----> name: " . $i["name"]."  ----> count: " . $i["count"]. "<br>";
  }
} else {
  echo "No results";
}

//close the connection
mysqli_close($connection);
?>

Open your web browser and point it to http://localhost/select.php URL. You will now see that all columns are selected and displayed.

Conclusion

In this guide, we discussed how to select data from a MySQL Table using PHP in XAMPP stack. We also saw how to display data from a specific column or all columns from the table. In our upcoming articles, we will learn more PHP MySQL related topics.