Cut Hosting Costs! Submit Query Today!

How to Connect MySQL Database with PHP?

One of the fundamental abilities each web developer should have is MySQL with PHP. The former is a scripting language used on servers. The latter is an open-source RDBMS. They are commonly combined to build dynamic and data-focused websites.

This knowledge base guide will help you install the development environment to run simple SQL queries.

Setting Up Your Development Environment

Before you start, ensure the following components are installed on your system:

1. PHP

2. MySQL

3. Web Server

In a single installation, you can install these components individually or use a pre-packaged solution like XAMPP or WAMP, which includes PHP, MySQL, and Apache.

Creating a MySQL Database

Create a database in MySQL. You can do this using:

1. phpMyAdmin

2. The MySQL command line.

Using phpMyAdmin

1. Open phpMyAdmin in your web browser.

2. Click on the 'Databases' tab.

3. Enter a name for your database and click 'Create'.

Using MySQL Command Line

1. Open your terminal or command prompt.

2. Log in to MySQL using the command:

mysql -u root -p.

3. Create a database using the command.

CREATE DATABASE my_databas;.

Creating a Table

Next, create a table within your database to store data.

Using phpMyAdmin

1. Select your database from the list.

2. Click on the 'SQL' tab.

3. Enter the following SQL command and click 'Go':

CREATE TABLE users (

id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(30) NOT NULL,

email VARCHAR(50),

reg_date TIMESTAMP

);

Using MySQL Command Line

1. Select your database using the command: USE my_database;.

2. Create a table using the command above.

Connecting to the MySQL Database with PHP

You’ll write a PHP script to connect to your MySQL database. PHP provides two main extensions for connecting to MySQL:

- MySQL Improved

- PHP Data Objects

Using MySQLi

Procedural Style:

$servername = "localhost";

$username = "root";

$password = "";

$database = "my_database";

// Create connection

conn = mysqli_connect (servername, username, password, database);

// Check connection

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

echo "Connected successfully";

Object-Oriented Style

$servername = "localhost";

$username = "root";

$password = "";

$database = "my_database";

// Create connection

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

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

echo "Connected successfully";

Using PDO

$servername = "localhost";

$username = "root";

$password = "";

$database = "my_database";

try {

$conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);

// Set the PDO error mode to exception

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

echo "Connected successfully";

} catch(PDOException $e) {

echo "Connection failed: " . $e->getMessage();

}

Executing SQL Queries

Once connected, you can execute SQL queries to interact with your database.

Inserting Data

Using MySQLi Procedural

$sql = "INSERT INTO users (username, email) VALUES ('JohnDoe', '[email protected]')";

if (mysqli_query($conn, $sql)) {

echo "New record created successfully";

} else {

echo "Error: " . $sql . "
" . mysqli_error($conn);

}

mysqli_close($conn);

Using MySQLi Object-Oriented

$sql = "INSERT INTO users (username, email) VALUES ('JohnDoe', '[email protected]')";

if ($conn->query($sql) === TRUE) {

echo "New record created successfully";

} else {

echo "Error: " . $sql . "
" . $conn->error;

}

$conn->close();

Using PDO

$sql = "INSERT INTO users (username, email) VALUES ('JohnDoe', '[email protected]')";

$conn->exec($sql);

echo "New record created successfully";

Selecting Data

Using MySQLi Procedural

// Establish a connection to the database

$conn = mysqli_connect("hostname", "username", "password", "database");

// Check the connection

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

// SQL query to select id, username, and email from users table

$sql = "SELECT id, username, email FROM users";

$result = mysqli_query($conn, $sql);

// Check if the query was successful

if ($result) {

// Check if there are any rows returned

if (mysqli_num_rows($result) > 0) {

// Fetch and output the rows

while ($row = mysqli_fetch_assoc($result)) {

echo "id: " . htmlspecialchars($row["id"]) . " - Name: " . htmlspecialchars($row["username"]) . " - Email: " . htmlspecialchars($row["email"]) . "
";

}

} else {

echo "0 results";

}

} else {

echo "Error: " . mysqli_error($conn);

}

// Close the database connection

mysqli_close($conn);

Using MySQLi Object-Oriented

$conn = new mysqli("hostname", "username", "password", "database");

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

$sql = "SELECT id, username, email FROM users";

$result = $conn->query($sql);

if ($result->num_rows > 0) {

while ($row = $result->fetch_assoc()) {

echo "id: " . htmlspecialchars($row["id"]) . " - Name: " . htmlspecialchars($row["username"]) . " - Email: " . htmlspecialchars($row["email"]) . "
";

}

} else {

echo "0 results";

}

$conn->close();

Handling Errors

It is very important to handle errors correctly to identify problems and ensure your application is fine.

Using MySQLi

if (mysqli_connect_errno()) {

printf("Connect failed: %s\n", mysqli_connect_error());

exit();

}

Using PDO

try {

$conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Your code here

} catch(PDOException $e) {

echo "Connection failed: " . $e->getMessage();

}

To Sum it Up!

Connecting PHP to a MySQL database is vital for any web developer. With or without MySQLi or PDO, these connections help you fashion powerful, data-driven applications. By following the steps summarised above, you can

- Set up a robust connection between PHP and MySQL

- Execute various SQL queries

- Handle errors

This foundational knowledge opens the door to more advanced database interactions and web development techniques.

Cut Hosting Costs! Submit Query Today!

How to Connect MySQL Database with PHP?

Setting Up Your Development Environment

Creating a MySQL Database

Using phpMyAdmin

Using MySQL Command Line

Creating a Table

Using phpMyAdmin

Using MySQL Command Line

Connecting to the MySQL Database with PHP

Using MySQLi

Using PDO

Executing SQL Queries

Inserting Data

Using PDO

Selecting Data

Handling Errors

To Sum it Up!

Related Questions

Cut Hosting Costs! Submit Query Today!

Grow With Us

Cut Hosting Costs! Submit Query Today!

How to Connect MySQL Database with PHP?

Setting Up Your Development Environment

Creating a MySQL Database

Using phpMyAdmin

Using MySQL Command Line

Creating a Table

Using phpMyAdmin

Using MySQL Command Line

Connecting to the MySQL Database with PHP

Using MySQLi

Using PDO

Executing SQL Queries

Inserting Data

Using PDO

Selecting Data

Handling Errors

To Sum it Up!

Related Questions

Cut Hosting Costs! Submit Query Today!

Grow With Us

We use cookies