PHP - SPLessons

PHP Select Data From MySQL

Chapter 37

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PHP Select Data From MySQL

PHP Select Data From MySQL

shape Description

“PHP Select Data From MySQL” chapter allows to select the data from the database. MySQL SELECT statement allows to fetch zero or multiple rows from database tables or views. The SELECT statement is one among mostly used MySQL queries.

The SELECT statement returns a result that comprises of rows and columns, also called as a Result Set.

shape Syntax

SELECT field1,field2,…fieldN table_name1,table_name2…[WHERE Clause]
[OFFSET M][LIMIT N]

The SELECT statement has several clauses:

shape Example

MySQLi (Object-oriented)

<!DOCTYPE html>
<html>
    <body>
        <?php 
            $HostName = "localhost"; 
            $UserName = "root"; 
            $Password = "password"; 
            $dbname = "accounts"; 
             
            // Create connection 
            $connection = new mysqli($HostName, $UserName, $Password, $dbname); 
             
            // Check connection 
            if ($connection->connect_error) 
            {
                die("Connection failed: " . $connection->connect_error);
            } 
              
            $sql = "SELECT id, First, Last FROM users2";
            $result = $connection->query($sql);
              
            if ($result->num_rows > 0) 
            {
                // output data of each row
                while($row = $result->fetch_assoc()) 
                {
                    echo "id: " . $row["id"]. " - Name: " . $row["First"]. " " . $row["Last"]. "<br>";
                }
            } 
            else
            {
                echo "0 results";
            }
            $connection->close();
        ?>
    </body>
</html>

The output looks like below in phpmyadmin with all the selected column values.

MySQLi (Procedural)

<!DOCTYPE html>
<html>
	<body>
		<?php
			$servername = "localhost";
			$username = "root";
			$password = "password";
			$dbname = "Student";

			// Create connection
			$connection = mysqli_connect($servername, $username, $password, $dbname);
			
			// Check connection
			if (!$connection) {
				die("Connection failed: " . mysqli_connect_error());
			}
			
              
			//Selecting the details  
			$sql = "SELECT id, First, Last FROM users2";
			$result = mysqli_query($connection, $sql);

			if (mysqli_num_rows($result) > 0) 
			{
				// output data of each row
				while($row = mysqli_fetch_assoc($result)) 
				{
					echo "id: " . $row["id"]. " - Name: " . $row["First"]. " " . $row["Last"]. "<br>";
				}
			} 
			else 
			{
				echo "0 results";
			}

			mysqli_close($connection);
		?>
	</body>
</html>

Summary

shape Points

  • SELECT statement fetch the data from database tables or views.
  • Result set is the result with columns and rows.