Creating a MySQL database and connecting your site

Introduction: Why Creating a MySQL Database is the First Step for Dynamic Websites?

Every dynamic website (such as WordPress, Joomla, or online stores) needs a database to store its information. Creating a MySQL database and properly connecting it to the site is the most fundamental operation in setting up a website. Without this step, even the simplest content management system will not work. In this article, we will teach the complete process from creating a database and user to setting permissions and connecting with PHP code, practically and with technical details.

Step One: Creating a MySQL Database and User in cPanel

Most shared hosts use cPanel. If you use ServerNet or any other provider, the steps are similar. We assume you have logged into your cPanel.

1. Creating a New Database

In the MySQL Databases section (usually located in the Databases group), follow these steps:

1. In the New Database field, enter the database name. Note that cPanel usually adds a prefix like yourusername_ to the database name. For example, if you enter mydb, the final name will be yourusername_mydb.
2. Click the Create Database button. You will see a success message.

Important Note: Remember the database name. You will need it later to connect.

2. Creating a Database User

Now you need to create a user to access this database. On the same page, find the MySQL Users section:

1. In the Username field, enter the desired username (again, a prefix will be added).
2. In the Password field, choose a strong password. Use a combination of uppercase and lowercase letters, numbers, and symbols. You can use the Password Generator button for help.
3. Save the password in a secure place.
4. Click Create User.

3. Assigning Privileges to the User

Without assigning privileges, the user cannot work with the database. In the Add User to Database section:

1. From the dropdown menu, select the created user and database.
2. Click Add.
3. On the next page, specify the privilege level. For most dynamic sites (like WordPress), select the ALL PRIVILEGES option. This gives the user all possible privileges (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, etc.).
4. Click Make Changes.

Common Mistake: Some users only grant SELECT privilege, which causes errors when installing WordPress or adding content. Always use ALL PRIVILEGES for the main site.

Step Two: Obtaining Connection Details

To connect your application to the database, you need four values:

• Hostname: Usually localhost. But on some hosts, it might be an address like mysql.example.com. Find this value in the MySQL Hostname section in cPanel.
• Database Name: For example, yourusername_mydb
• Username: For example, yourusername_user
• Password: The password you set in the previous step.

Save this information in a secure text file. We will use it later to connect via PHP code.

Step Three: Connecting to the Database with PHP (MySQLi)

There are two main methods for connecting PHP to MySQL: MySQLi (MySQL improved) and PDO. Here, we use MySQLi in procedural style, which is simpler for beginners.

Sample Connection Code

Create a new file named db_connect.php and place the following code in it:

<?php
$host = 'localhost'; // or enter your host address
$dbname = 'yourusername_mydb';
$username = 'yourusername_user';
$password = 'your_strong_password';

// Create connection
$conn = mysqli_connect($host, $username, $password, $dbname);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

Upload this file to your site's root directory and run it through a browser. If you see the message "Connected successfully", everything is correct. Otherwise, check the error.

Troubleshooting Common Errors

• Error "Access denied for user": Usually due to incorrect username or password. Double-check the values.
• Error "Unknown database": You have entered the wrong database name. Remember to include the username_ prefix.
• Error "Connection refused": If you are using localhost and your host uses a non-standard port, you need to specify the port. For example, $host = 'localhost:3307';

Step Four: Using the Database in Your Site (Practical Example)

Now that the connection is established, you can use the database. Here is a simple example of storing and displaying user information.

Creating a Sample Table

First, create a table to store user information. You can do this via phpMyAdmin (available in cPanel) or with SQL code. Execute the following SQL code in phpMyAdmin:

CREATE TABLE users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Inserting Data into the Table

Place the following code in a file named insert_user.php:

<?php
include 'db_connect.php';

$firstname = "Ali";
$lastname = "Mohammadi";
$email = "ali@example.com";

$sql = "INSERT INTO users (firstname, lastname, email) VALUES ('$firstname', '$lastname', '$email')";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>

Security Note: In the code above, direct values are used. In a real project, be sure to use Prepared Statements to prevent SQL Injection.

Displaying Data

To display registered users, create a file named show_users.php:

<?php
include 'db_connect.php';

$sql = "SELECT id, firstname, lastname, email FROM users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}

mysqli_close($conn);
?>

Step Five: Secure Connection and Optimization

For real websites, following these tips is essential:

• Using SSL: If your site uses HTTPS, the database connection should also be encrypted. In MySQLi, you can use SSL options.
• Closing the Connection: Always close the connection with mysqli_close($conn) after finishing work.
• Error Management: Instead of displaying errors directly to the user, log them.
• Regular Backups: Take periodic backups of your database. cPanel has the Backup tool for this purpose.

Conclusion

In this article, we taught the complete process of creating a MySQL database and connecting it to a website from scratch. From creating a database and user in cPanel to writing PHP code for connection and performing CRUD operations. By following security tips and using correct methods, you can have a stable and secure database for your site. If you use web hosting services like ServerNet, database management tools like phpMyAdmin are usually available by default, making the job much easier.

For further learning, I recommend studying the official PHP documentation on MySQLi and familiarizing yourself with concepts like Prepared Statements and Transactions.