PHP MySQL - using MySQLi

PHP has three different ways to connect to and interact with a MySQL database: the original MySQL extension (with functions), MySQL Improved (MySQLi, object-oriented), or PHP Data Objects (PDO, object-oriented).
They can't be mixed in the same script. The original MySQL extension is no longer actively developed and is not recommended for new PHP-MySQL projects.
The PHP documentation describes MySQLi as the preferred option recommended by MySQL for new projects.

Connecting with the MySQL

Before you can access data in a database, you must create a connection to the MySQL server.
To connect to a MySQL server with PHP and MySQLi, you have to create a mysqli object by passing your data connecting to new mysqli().
  - Syntax:

$conn = new mysqli($servername, $username, $password, $database, $port);

- $servername - Specifies the server to connect to. If you pass the NULL value or an empty string "", the server will use the default value: "localhost"
- $username - Specifies the MySQL username to log in with. Default value is the name of the user that owns the server process
- $password - Specifies the password to log in with. Default is "" (empty string)
- $database - Optional. If provided, will specify the database to be used when performing queries
- $port - Optional. Specifies the port number to attempt to connect to the MySQL server, default 3306
  - Example:

<?php
// connect to the server
$conn = new mysqli('localhost', 'root', 'pass', 'dbname');

// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());
}
?>

- This connects to a database called "dbname", and stores the connection object as $conn.
If there is any error connecting, mysqli_connect_errno() returns the error code.

Closing a Connection

The connection will be closed automatically when the script ends. It's a good practice to close the conection earlier, when the script no longer uses it. This will release the memory used for the connection. To close the connection before, use the close() method of the MySQLi class.
  - Example:

<?php
// connect to the server
$conn = new mysqli('localhost', 'root', 'pass', 'dbname');

// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());
}

// some code

$conn->close();         // close the connection

// other PHP code
?>

Creating database with MySQLi

To store data in MySQL, you need to use a database.
The CREATE DATABASE statement is used to create a database in MySQL.
  - Syntax:

CREATE DATABASE database_name

To get PHP to execute the SQL instructions, first you must create a mysqli object with the conection to the server, then use the query() method of the MySQLi class.
  - Syntax:

mysqliObj->query($sql_query)

- mysqliObj - is the mysqli object created with new mysqli()
- $sql_query - is a string with SQL instructions.
This method sends a query or command to a MySQL connection, will return a result object, or TRUE on success. FALSE on failure.

The fallowing example create a database called "tests":

<?php
// connect to the MySQL server
$conn = new mysqli('localhost', 'root', 'pass');

// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());
}

// sql query with CREATE DATABASE
$sql = "CREATE DATABASE `tests` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";

// Performs the $sql query on the server to create the database
if ($conn->query($sql) === TRUE) {
  echo 'Database "tests" successfully created';
}
else {
 echo 'Error: '. $conn->error;
}

$conn->close();
?>

- When you want to create a new database, you must add only the first three arguments to the mysqli object (servername, username andpassword), but, if you have to use a specific port, add an empty string "" for the database-name argument:
                  Example:   new mysqli('localhost', 'root', 'pass', '', port)
- The extra instruction: DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci creates the database to use UTF-8 charset for encoding characters.
The example above attempt to create a database called "test", outputs "Database "tests" successfully created" on succes, or an error message on failure.
$conn->error (or also can be used mysqli_error($conn)) returns a string description of the last error (if exists).

SQL commands are case-insensitive, so, you can use "CREATE DATABASE" or "create database". But the name of tables and columns are case-sensitive.

Create a table

Once you have set a connection to a database, you can create tables in that database. Tables are the elements that store data in databases.
To create a table in MySQL use the CREATE TABLE statement, than pass it to the query() method.
  - Syntax:

CREATE TABLE `table_name`
(
`column_name1` data_type,
`column_name2` data_type,
`column_name3` data_type,
....
)

The data type specifies what type of data the column can hold. For a list of the MySQL data types, see the previous lesson: PHP MySQL Introduction, Data Types.
After the data_type, you can specify other optional attributes for each column:

• NOT NULL - Each row must contain a value for that column, null values ​​are not allowed.
• DEFAULT value - Set a default value that is added when no other value is passed
• UNSIGNED - Can be used for number types, limits the stored data to positive numbers and zero.
• AUTO INCREMENT - MySQL will automatically increases the value of the field by 1 each time a new record is added.
• PRIMARY KEY - Is used to uniquely identify the rows in a table. The column with PRIMARY KEY setting is often an ID number, and is often used with the AUTO_INCREMENT

Each table should have a primary key column. Its value must be unique for each record in the table.

  - Example:

<?php
// connect to the MySQL server
$conn = new mysqli('localhost', 'root', 'pass', 'tests');

// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());
}

// sql query for CREATE TABLE
$sql = "CREATE TABLE `users` (
 `id` INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 `name` VARCHAR(25) NOT NULL,
 `pass` VARCHAR(18) NOT NULL,
 `email` VARCHAR(45),
 `reg_date` TIMESTAMP
 ) CHARACTER SET utf8 COLLATE utf8_general_ci"; 

// Performs the $sql query on the server to create the table
if ($conn->query($sql) === TRUE) {
  echo 'Table "users" successfully created';
}
else {
 echo 'Error: '. $conn->error;
}

$conn->close();
?>

- This code will create a table called "users" in the "tests" database, with five columns (id , name , pass , email and reg_date), sets the 'id' column as the primary key field.
In the image below you can see the description of the "users" table.

In SQL statements is recommended that the names of the tables and columns to be added within apostrophes ` ` (carefully, not single quotes, but the character near the key with the number 1 on the keyboard). This is the correct syntax, but is not strictly required.