FriconiX
Free collection of beautiful vector icons for your web pages.

Lesson 5.6. Getting data from MySQL in PHP

Foreword

We will assume on this page the existence of a $db variable which contains the database connection parameters which is created in accordance with the code presented on the MySQL Connection in PHP page.

In the examples of the previous course, we saw how to execute queries MySQL in PHP. In the case of an INSERT query, MySQL does not return any value. For the SELECT query, MySQL will return a series of rows containing data that should be retrieved in a PHP array.

Syntax

The syntax for reading data is similar to the previous queries. After calling the execute() method, you may call the fetchAll() method which will return an array containing the requested data:

$query = $db->prepare( "SELECT * FROM `users` WHERE lastname = :nom" );
$query->bindValue(':nom', 'Page');
$query->execute();
$result = $query->fetchAll(PDO::FETCH_ASSOC);

$result is an array whose first key is the row number (0, 1, 2, 3 ...). Each row contains several keys which correspond to the columns (or fields) of the table:

array (size=3)
  0 => 
    array (size=3)
      'id' => int 1
      'lastname' => string 'Page' (length=4)
      'firstname' => string 'Lary' (length=4)
  1 => 
    array (size=3)
      'id' => int 2
      'lastname' => string 'Page' (length=4)
      'firstname' => string 'Tournela' (length=8)
  3 => 
    array (size=3)
      'id' => int 2
      'lastname' => string 'Page' (length=4)
      'firstname' => string 'Marc' (length=4)

If the query does not return any value, the table is empty. It is a good habit to always check the contents of the table to avoid future errors (when displaying the table content for example).

Exercice

In phpMyAdmin, create a new database named myBase.

Run in phpMyAdmin the following SQL queries to create and populate a new table:

-- Create table
CREATE TABLE users (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  lastname VARCHAR(100),
  firstname VARCHAR(100),
  email VARCHAR(255)
);

-- Insert rows
INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES ('Lary', 'Page', 'lary.page@google.com');
INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES ('Sergueï', 'Brin', 'serguei.brin@google.com');
INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES ('Elon', 'Musk', 'elon.musk@tesla.com');
INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES ('Steve', 'Jobs', 'steve.jobs@apple.com');
INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES ('Noël', 'Père', 'santa@clause.com');
  1. After writing the code for connectiong to the database, write the code to get the content of the users table.

  2. Update the code of the query in order to display only users whose email address ends with @google.com.

See also


Last update : 02/05/2020