- Getting Data From MySQL Database
- Example
- Example
- Example
- Releasing Memory
- Example
- PHP MySQL Select Data
- Select Data With MySQLi
- Example (MySQLi Object-oriented)
- Example (MySQLi Procedural)
- Example (MySQLi Object-oriented)
- Select Data With PDO (+ Prepared Statements)
- Example (PDO)
- PHP MySQL SELECT Query
- Selecting Data From Database Tables
- Example
- Explanation of Code (Procedural style)
Getting Data From MySQL Database
Data can be fetched from MySQL tables by executing SQL SELECT statement through PHP function mysql_query. You have several options to fetch data from MySQL.
The most frequently used option is to use function mysql_fetch_array(). This function returns row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.
Below is a simple example to fetch records from employee table.
Example
Try out following example to display all the records from employee table.
$sql = 'SELECT emp_id, emp_name, emp_salary FROM employee'; mysql_select_db('test_db'); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not get data: ' . mysql_error()); >while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) < echo "EMP ID :
". "EMP NAME :
". "EMP SALARY :
". "--------------------------------
"; > echo "Fetched data successfully\n"; mysql_close($conn); ?> The content of the rows are assigned to the variable $row and the values in row are then printed.
NOTE − Always remember to put curly brackets when you want to insert an array value directly into a string.
In above example the constant MYSQL_ASSOC is used as the second argument to mysql_fetch_array(), so that it returns the row as an associative array. With an associative array you can access the field by using their name instead of using the index.
PHP provides another function called mysql_fetch_assoc() which also returns the row as an associative array.
Example
Try out following example to display all the records from employee table using mysql_fetch_assoc() function.
$sql = 'SELECT emp_id, emp_name, emp_salary FROM employee'; mysql_select_db('test_db'); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not get data: ' . mysql_error()); >while($row = mysql_fetch_assoc($retval)) < echo "EMP ID :
". "EMP NAME :
". "EMP SALARY :
". "--------------------------------
"; > echo "Fetched data successfully\n"; mysql_close($conn); ?> You can also use the constant MYSQL_NUM, as the second argument to mysql_fetch_array(). This will cause the function to return an array with numeric index.
Example
Try out following example to display all the records from employee table using MYSQL_NUM argument.
$sql = 'SELECT emp_id, emp_name, emp_salary FROM employee'; mysql_select_db('test_db'); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not get data: ' . mysql_error()); >while($row = mysql_fetch_array($retval, MYSQL_NUM)) < echo "EMP ID :
". "EMP NAME :
". "EMP SALARY :
". "--------------------------------
"; > echo "Fetched data successfully\n"; mysql_close($conn); ?> All the above three examples will produce same result.
Releasing Memory
Its a good practice to release cursor memory at the end of each SELECT statement. This can be done by using PHP function mysql_free_result(). Below is the example to show how it has to be used.
Example
Try out following example
$sql = 'SELECT emp_id, emp_name, emp_salary FROM employee'; mysql_select_db('test_db'); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not get data: ' . mysql_error()); >while($row = mysql_fetch_array($retval, MYSQL_NUM)) < echo "EMP ID :
". "EMP NAME :
". "EMP SALARY :
". "--------------------------------
"; > mysql_free_result($retval); echo "Fetched data successfully\n"; mysql_close($conn); ?> While fetching data you can write as complex SQL as you like. Procedure will remain same as mentioned above.
PHP MySQL Select Data
The SELECT statement is used to select data from one or more tables:
or we can use the * character to select ALL columns from a table:
To learn more about SQL, please visit our SQL tutorial.
Select Data With MySQLi
The following example selects the id, firstname and lastname columns from the MyGuests table and displays it on the page:
Example (MySQLi Object-oriented)
$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
>
$sql = «SELECT id, firstname, lastname FROM MyGuests»;
$result = $conn->query($sql);
if ($result->num_rows > 0) // output data of each row
while($row = $result->fetch_assoc()) echo «id: » . $row[«id»]. » — Name: » . $row[«firstname»]. » » . $row[«lastname»]. «
«;
>
> else echo «0 results»;
>
$conn->close();
?>
Code lines to explain from the example above:
First, we set up an SQL query that selects the id, firstname and lastname columns from the MyGuests table. The next line of code runs the query and puts the resulting data into a variable called $result.
Then, the function num_rows() checks if there are more than zero rows returned.
If there are more than zero rows returned, the function fetch_assoc() puts all the results into an associative array that we can loop through. The while() loop loops through the result set and outputs the data from the id, firstname and lastname columns.
The following example shows the same as the example above, in the MySQLi procedural way:
Example (MySQLi Procedural)
$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) die(«Connection failed: » . mysqli_connect_error());
>
$sql = «SELECT id, firstname, lastname FROM MyGuests»;
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) // output data of each row
while($row = mysqli_fetch_assoc($result)) echo «id: » . $row[«id»]. » — Name: » . $row[«firstname»]. » » . $row[«lastname»]. «
«;
>
> else echo «0 results»;
>
You can also put the result in an HTML table:
Example (MySQLi Object-oriented)
$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
>
$sql = «SELECT id, firstname, lastname FROM MyGuests»;
$result = $conn->query($sql);
if ($result->num_rows > 0) echo «
| ID | Name |
|---|---|
| «.$row[«id»].» | «.$row[«firstname»].» «.$row[«lastname»].» |
«;
> else echo «0 results»;
>
$conn->close();
?>
Select Data With PDO (+ Prepared Statements)
The following example uses prepared statements.
It selects the id, firstname and lastname columns from the MyGuests table and displays it in an HTML table:
Example (PDO)
class TableRows extends RecursiveIteratorIterator <
function __construct($it) <
parent::__construct($it, self::LEAVES_ONLY);
>
function current() return «
«;
>
$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDBPDO»;
try $conn = new PDO(«mysql:host=$servername;dbname=$dbname», $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare(«SELECT id, firstname, lastname FROM MyGuests»);
$stmt->execute();
PHP MySQL SELECT Query
In this tutorial you’ll learn how to select records from a MySQL table using PHP.
Selecting Data From Database Tables
So far you have learnt how to create database and table as well as inserting data. Now it’s time to retrieve data what have inserted in the preceding tutorial. The SQL SELECT statement is used to select the records from database tables. Its basic syntax is as follows:
Let’s make a SQL query using the SELECT statement, after that we will execute this SQL query through passing it to the PHP mysqli_query() function to retrieve the table data.
Consider our persons database table has the following records:
+----+------------+-----------+----------------------+ | id | first_name | last_name | email | +----+------------+-----------+----------------------+ | 1 | Peter | Parker | peterparker@mail.com | | 2 | John | Rambo | johnrambo@mail.com | | 3 | Clark | Kent | clarkkent@mail.com | | 4 | John | Carter | johncarter@mail.com | | 5 | Harry | Potter | harrypotter@mail.com | +----+------------+-----------+----------------------+
The PHP code in the following example selects all the data stored in the persons table (using the asterisk character ( * ) in place of column name selects all the data in the table).
Example
// Attempt select query execution $sql = "SELECT * FROM persons"; if($result = mysqli_query($link, $sql)) < if(mysqli_num_rows($result) >0)< echo ""; echo ""; echo "id "; echo "first_name "; echo "last_name "; echo "email "; echo " "; while($row = mysqli_fetch_array($result))< echo ""; echo "" . $row['id'] . " "; echo "" . $row['first_name'] . " "; echo "" . $row['last_name'] . " "; echo "" . $row['email'] . " "; echo " "; > echo "
"; // Free result set mysqli_free_result($result); > else < echo "No records matching your query were found."; >> else < echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); >// Close connection mysqli_close($link); ?>connect_error); > // Attempt select query execution $sql = "SELECT * FROM persons"; if($result = $mysqli->query($sql))< if($result->num_rows > 0)< echo ""; echo ""; echo "id "; echo "first_name "; echo "last_name "; echo "email "; echo " "; while($row = $result->fetch_array())< echo ""; echo "" . $row['id'] . " "; echo "" . $row['first_name'] . " "; echo "" . $row['last_name'] . " "; echo "" . $row['email'] . " "; echo " "; > echo "
"; // Free result set $result->free(); > else < echo "No records matching your query were found."; >> else< echo "ERROR: Could not able to execute $sql. " . $mysqli->error; > // Close connection $mysqli->close(); ?>setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > catch(PDOException $e)< die("ERROR: Could not connect. " . $e->getMessage()); > // Attempt select query execution try< $sql = "SELECT * FROM persons"; $result = $pdo->query($sql); if($result->rowCount() > 0)< echo ""; echo ""; echo "id "; echo "first_name "; echo "last_name "; echo "email "; echo " "; while($row = $result->fetch())< echo ""; echo "" . $row['id'] . " "; echo "" . $row['first_name'] . " "; echo "" . $row['last_name'] . " "; echo "" . $row['email'] . " "; echo " "; > echo "
"; // Free result set unset($result); > else < echo "No records matching your query were found."; >> catch(PDOException $e)< die("ERROR: Could not able to execute $sql. " . $e->getMessage()); > // Close connection unset($pdo); ?>Explanation of Code (Procedural style)
In the example above, the data returned by the mysqli_query() function is stored in the $result variable. Each time mysqli_fetch_array() is invoked, it returns the next row from the result set as an array. The while loop is used to loops through all the rows in the result set. Finally the value of individual field can be accessed from the row either by passing the field index or field name to the $row variable like $row[‘id’] or $row[0] , $row[‘first_name’] or $row[1] , $row[‘last_name’] or $row[2] , and $row[’email’] or $row[3] .
If you want to use the for loop you can obtain the loop counter value or the number of rows returned by the query by passing the $result variable to the mysqli_num_rows() function. This loop counter value determines how many times the loop should run.