MySQL Connect with PHP

MySQL Connect with PHP

MySQL can be used with many languages, including C, C ++, JAVA, and PHP. In these languages, MySQL is most commonly used in PHP web development

• PHP mysqli_connect() function is used for opening a new MySQL server connection.
• 
  
  
• 
  
• Before using the MySql database, one must be aware of establishing the connections to MySql with PHP. If you have MySql and PHP installed on your system, you can use localhost for hostname and the password will be none.
• The function to connect to MySQL is called mysqli_connect(). This function returns a resource which is a pointer to the database connection.

The mysqli_connect function takes mainly four arguments; hostname, username, password, and database name.

Syntax:

mysqli_connect(hostname,username,password,dbname); 

Arguments	Description
hostname	Either a hostname or an IP address
username	The MySQL username
password	MySQL user password
dbname	The default database to be used when performing queries

Note: There are more than two available parameters, but the above are the most important ones.

When the PHP script and MySQL are on the same machine, you can use localhost as the address, and the default password is none. If your MySQL service is running at a separate location, you will need to insert the IP address or URL in place of localhost.

Example:

<?php
// Database connection establishment
 $con=mysqli_connect("example.com","alex","qwerty","my_database");
 
// Check connection
 if (mysqli_connect_errno($con)) {
 echo "MySQL database connection failed: " . mysqli_connect_error();
 }
?> 

Close a Connection

When script execution ends, the connection will automatically close. To close the connection before, use mysqli_close() function:

Example:

<?php
// Create connection
 $con=mysqli_connect("example.com","alex","qwerty","my_database");
 
// Check connection
 if (mysqli_connect_errno($con)) {
 echo "Failed to connect to MySQL: " . mysqli_connect_error();
 }

// Close Connection
 mysqli_close($con);
?> 
PHP Connect to MySQL Server
In this tutorial you will learn how to connect to the MySQL server using PHP.
Ways of Connecting to MySQL through PHP
In order to store or access the data inside a MySQL database, you first need to connect to the MySQL database server. PHP offers two different ways to connect to MySQL server: MySQLi (Improved MySQL) and PDO (PHP Data Objects) extensions.
While the PDO extension is more portable and supports more than twelve different databases, MySQLi extension as the name suggests supports MySQL database only. MySQLi extension however provides an easier way to connect to, and execute queries on, a MySQL database server. Both PDO and MySQLi offer an object-oriented API, but MySQLi also offers a procedural API which is relatively easy for beginners to understand.
Tip: The PHP's MySQLi extension provides both speed and feature benefits over the PDO extension, so it could be a better choice for MySQL-specific projects.
Connecting to MySQL Database Server
In PHP you can easily do this using the mysqli_connect() function. All communication between PHP and the MySQL database server takes place through this connection. Here're the basic syntaxes for connecting to MySQL using MySQLi and PDO extensions:
Syntax: MySQLi, Procedural way
$link = mysqli_connect("hostname", "username", "password", "database");
Syntax: MySQLi, Object Oriented way
$mysqli = new mysqli("hostname", "username", "password", "database");
Syntax: PHP Data Objects (PDO) way
$pdo = new PDO("mysql:host=hostname;dbname=database", "username", "password");
The hostname parameter in the above syntax specify the host name (e.g. localhost), or IP address of the MySQL server, whereas the username and password parameters specifies the credentials to access MySQL server, and the database parameter, if provided will specify the default MySQL database to be used when performing queries.
The following example shows how to connect to MySQL database server using MySQLi (both procedural and object oriented way) and PDO extension.
Example
Procedural Object Oriented PDO
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "");
 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
 
// Print host information
echo "Connect Successfully. Host info: " . mysqli_get_host_info($link);
?>
Note: The default username for MySQL database server is root and there is no password. However to prevent your databases from intrusion and unauthorized access you should set password for MySQL accounts.
Tip: Setting the PDO::ATTR_ERRMODE attribute to PDO::ERRMODE_EXCEPTION tells PDO to throw exceptions whenever a database error occurs.
Closing the MySQL Database Server Connection
The connection to the MySQL database server will be closed automatically as soon as the execution of the script ends. However, if you want to close it earlier you can do this by simply calling the PHP mysqli_close() function.
Example
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "");
 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
 
// Print host information
echo "Connect Successfully. Host info: " . mysqli_get_host_info($link);
 
// Close connection
mysqli_close($link);
?>

Step 1: Creating the Database Table
Execute the following SQL query to create the users table inside your MySQL database.
Example
CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
Please check out the tutorial on SQL CREATE TABLE statement for the detailed information about syntax for creating tables in MySQL database system.
Step 2: Creating the Config File
After creating the table, we need create a PHP script in order to connect to the MySQL database server. Let's create a file named "config.php" and put the following code inside it.
Example
<?php
/* Database credentials. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'demo');
 
/* Attempt to connect to MySQL database */
$link = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
?>