MySql DB Connection

MySQL Connection: Building Bridges to Data Dreams

The first step to reading or inserting data is to establish a connection to your server/database. It’s a fairly straightforward process and it’s something that doesn’t change. The steps are always the same. Once you connect to it once, you’ll be able to reuse the code each time. The only thing that you’ll need to modify is your server, username, password, and database name.

Creating a Database

I’m assuming that you’re following along with the series. We have a Docker container and that container has the traditional LAMP setup (Linux, Apache, MySQL, and PHP). It also comes with phpMyAdmin

When you first run the docker container, it will generate a username and password for you.

docker run -i -t -p "80:80" -v ${PWD}/app:/app -v ${PWD}/mysql:/var/lib/mysql mattrayner/lamp:latest

You should see something like this with your own unique password.

====================================================================
You can now connect to this MySQL Server with OOwfvsr2soGW
mysql -uadmin -pE9bIyRDwzidT -h<host> -P<port>
Please remember to change the above password as soon as possible!
MySQL user 'root' has no password but only allows local connections
enjoy!

This is going to be the username/password that we can use to access phpMyAdmin.

  • admin
  • E9bIyRDwzidT

If you don’t see it, it’s most likely because you’ve done this before and you have a mysql folder already in your directory. Delete the mysql directory and run the docker command again. Remember, this is only for the test environment. Do not delete anything if you’re following along on a production environment. I highly encourage you not to experiment on the production environment.

Creating the database via phpMyAdmin

Go to your localhost phpMyAdmin installation. For me, it’s http://0.0.0.0/phpmyadmin.

Enter your credentials and click Go. You’ll enter the main phpMyAdmin site.

Click on the Databases tab, enter a name for the database. I entered dino_test_db. Click the Create button to create the database.

You’ll be redirected to your database where you can start adding tables. We’re not going to do that in this tutorial.

x

Creating the database through the Server

If you don’t have access to phpMyAdmin, you can log into your container and execute a script. You were already provided how to do this. To start off, visit Docker and click on your container.

Next, copy the connection to mysql script. For me it’s:

mysql -uadmin -pE9bIyRDwzidT

Click on the CLI tab and paste the script.

You’re connected to your MySQL server. To see which databases you currently have, type in: show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| dino_test_db       |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql>

There’s the database that we just created via phpMyAdmin. To create a new database, type in: create database dino_test_db_2;

mysql> create database dino_test_db_2;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| dino_test_db       |
| dino_test_db_2     |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
mysql>

That’s it. You’ve created a database without phpMyAdmin.

Creating the Database through the PHP script

Yes. There’s yet another way that you can do this. I don’t recommend it too often since you may forget to remove the script. But it definitely works.

<?php
$server   = "localhost";
$username = "admin";
$password = "E9bIyRDwzidT";

$connection = new mysqli( $server, $username, $password );

if ( $connection->connect_error ) {
    die( $connection->connect_error );
}

$sql = "CREATE DATABASE dino_test_db_3";

if ( $connection->query($sql) === TRUE ) {
    echo "Database created successfully";
} else {
    die($connection->error);
}

echo "Connection established successfully";

We’re just running the CREATE DATABASE db_name script like we did earlier. We pass that to the query method. If the query executes correctly, it returns true. Database created successfully.

Don’t get overwhelmed with what we just covered. We just went over three different ways to do the same thing: creating the database.

Connecting to the server

Time to connect to your server. We’ll do this the object oriented way. There is a procedural way to do it too, but we know OOP PHP, so we’ll do it that way. You’ll need to do this before you can connect to your database.

<?php
$server   = "localhost";
$username = "admin";
$password = "E9bIyRDwzidT";

$connection = new mysqli( $server, $username, $password );

if ( $connection->connect_error ) {
    die( $connection->connect_error );
}

echo "Connection established successfully";

We’ll use the mysqli class to create the $connection object. The mysqli constructor expects 3 arguments to be passed to it upon instantiation.

  • The server name. In most cases this will be localhost if your MySQL database is on the same server.
  • The username. We know that this is admin.
  • The password. We know what the password is as well: E9bIyRDwzidT.

And that’s it. You’ll check to see if there are any connection errors with the $connection->connect_error property. If there is an error, you kill the script. If there are no errors, you should get a Connection established successfully message.

Connecting to the Database

Now that we have created a database, we can connect to it. We just need to tweak our script slightly.

The mysqli constructor can accept the database name as well. We’ll add that as the last argument. Run the code and you’ll see that you’ll get the Connection established successfully message.

That’s all there is to it.

MySQL Introduction

MYSQL IS A POPULAR OPEN-SOURCE DATABASE SYSTEM

PHP – P82: MYSQL INTRODUCTION

It is an Oracle distributed database system that runs on a server. It uses SQL syntax and paired with PHP to the point that it’s not common to learn one without the other.

MySql DB Connection

MySQL Connection: Building Bridges to Data Dreams

PHP – P83: mysql db connection

The first step to reading or inserting data is to establish a connection to your server/database. It’s a fairly straightforward process and it’s something that doesn’t change.

MySQL Tables

MYSQL TABLES NEATLY STORE DATA IN ROWS AND COLUMNS

PHP – P84: MYSQL TABLES

It’s time to create some tables. If you remember the previous analogy, the database can be thought of as an Excel Workbook and the tables can be thought of as sheets within the Workbook.

Leave a Reply