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 IS A POPULAR OPEN-SOURCE DATABASE SYSTEM
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 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 NEATLY STORE DATA IN ROWS AND COLUMNS
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.