Hướng dẫn dùng w3schools database trong PHP

---

With PHP, you can connect to and manipulate databases.

Nội dung chính

• What is MySQL?
• PHP + MySQL Database System
• Database Queries
• Download MySQL Database
• Facts About MySQL Database
• Examples in Each Chapter
• SQL Exercises
• Test Yourself With Exercises
• SQL Examples
• SQL Quiz Test
• My Learning
• SQL References
• SQL Data Types
• Kickstart your career
• Select Data From a MySQL Database
• Select Data With MySQLi
• Example (MySQLi Object-oriented)
• Example (MySQLi Procedural)
• Example (MySQLi Object-oriented)
• Select Data With PDO (+ Prepared Statements)
• Example (PDO)

MySQL is the most popular database system used with PHP.

---

What is MySQL?

• MySQL is a database system used on the web
• MySQL is a database system that runs on a server
• MySQL is ideal for both small and large applications
• MySQL is very fast, reliable, and easy to use
• MySQL uses standard SQL
• MySQL compiles on a number of platforms
• MySQL is free to download and use
• MySQL is developed, distributed, and supported by Oracle Corporation
• MySQL is named after co-founder Monty Widenius's daughter: My

The data in a MySQL database are stored in tables. A table is a collection of related data, and it consists of columns and rows.

Databases are useful for storing information categorically. A company may have a database with the following tables:

• Employees
• Products
• Customers
• Orders

---

PHP + MySQL Database System

• PHP combined with MySQL are cross-platform (you can develop in Windows and serve on a Unix platform)

---

Database Queries

A query is a question or a request.

We can query a database for specific information and have a recordset returned.

Look at the following query (using standard SQL):

SELECT LastName FROM Employees

The query above selects all the data in the "LastName" column from the "Employees" table.

To learn more about SQL, please visit our SQL tutorial.

---

Download MySQL Database

If you don't have a PHP server with a MySQL Database, you can download it for free here: http://www.mysql.com

---

Facts About MySQL Database

MySQL is the de-facto standard database system for web sites with HUGE volumes of both data and end-users (like Facebook, Twitter, and Wikipedia).

Another great thing about MySQL is that it can be scaled down to support embedded database applications.

Look at http://www.mysql.com/customers/ for an overview of companies using MySQL.

SQL is a standard language for storing, manipulating and retrieving data in databases.

Our SQL tutorial will teach you how to use SQL in: MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems.

Start learning SQL now »

---

Examples in Each Chapter

With our online SQL editor, you can edit the SQL statements, and click on a button to view the result.

Click on the "Try it Yourself" button to see how it works.

---

SQL Exercises

Test Yourself With Exercises

Exercise:

Insert the missing statement to get all the columns from the Customers table.

Start the Exercise

---

---

SQL Examples

Learn by examples! This tutorial supplements all explanations with clarifying examples.

See All SQL Examples

---

SQL Quiz Test

Test your SQL skills at W3Schools!

Start SQL Quiz!

---

My Learning

Track your progress with the free "My Learning" program here at W3Schools.

Log into your account, and start earning points!

This is an optional feature, you can study W3Schools without using My Learning.

---

SQL References

At W3Schools you will find a complete reference for keywords and function:

SQL Keyword Reference

MYSQL Functions

SQLServer Functions

MS Access Functions

SQL Quick Reference

---

SQL Data Types

Data types and ranges for Microsoft Access, MySQL and SQL Server.

SQL Data Types

Kickstart your career

Get certified by completing the course

Get certified

w3schoolsCERTIFIED.2022

---

Select Data From a MySQL Database

The SELECT statement is used to select data from one or more tables:

SELECT column_name(s) FROM table_name

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();
?>

Run example »

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";
}

mysqli_close($conn);
?>

Run example »

---

---

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 "

";
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "";
  }
  echo "

ID	Name
".$row["id"]."	".$row["firstname"]." ".$row["lastname"]."

";
} else {
  echo "0 results";
}
$conn->close();
?>

Run example »

---

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)

echo "

";
echo "";

class TableRows extends RecursiveIteratorIterator {
  function __construct($it) {
    parent::__construct($it, self::LEAVES_ONLY);
  }

  function current() {
    return "

";
  }

  function beginChildren() {
    echo "

";
  }

  function endChildren() {
    echo "

" . "\n";
  }
}

$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();

  // set the resulting array to associative
  $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
  foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
    echo $v;
  }
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
$conn = null;
echo "

Id	Firstname	Lastname
" . parent::current(). "

";
?>

Run example »