Database Connection Setup in Laravel

Lraravel supports MySQL, PostgreSQL, SQL Server and SQLite database. Database setup is stored in .env file and config/database.php.

The priority of database setup information is:
.env > config/database.php

Setup procedure for database connection:

SQLite:

1. Add a empty sqlite database file.
   > touch database/database.sqlite
2. Comment out:

   #DB_CONNECTION=mysql 
   #DB_HOST=127.0.0.1
   #DB_PORT=3306
   #DB_DATABASE=homestead
   #DB_USERNAME=homestead
   #DB_PASSWORD=secret

4. Add commends to .env file:

   DB_CONNECTION=sqlite  
   DB_DATABASE=database/database.sqlite

5. php artisan migrate

MySQL:

1. Add a new database in mysql server.
2. Edit .env file:

   DB_CONNECTION=mysql 
   DB_HOST=127.0.0.1
   DB_PORT=3306
   DB_DATABASE=xxxxx
   DB_USERNAME=xxxxx
   DB_PASSWORD=xxxx

4. php artisan migrate

   Migration table created successfully.
   Migrating: 2014_10_12_000000_create_users_table
   Migrated:  2014_10_12_000000_create_users_table
   Migrating: 2014_10_12_100000_create_password_resets_table
   Migrated:  2014_10_12_100000_create_password_resets_table

Using artisan command to create a database in mysql.

What if I want to create a database in Laravel?
Please refer to this article Using an Artisan command to create a database in Laravel or Lumen

Insert and Update An Image to MySQL Server Field with Blob Data Type in PHP

Normally the images are stored in a file directory, not in the database. It is because that storing image data in database will make database bulky. In addition, there is some techniques to take care of. How to store and retrieve an image from MySQL server? Here is how:

1. Blob data type in MySQL server

First, create a blob type field in MySQL server:

mysql> describe products;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field           | Type                  | Null | Key | Default | Extra          |
+-----------------+-----------------------+------+-----+---------+----------------+
| prod_id         | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| prod_pic        | mediumblob            | YES  |     | NULL    |                |
| prod_pic_type   | varchar(11)           | YES  |     | NULL    |                |
+-----------------+-----------------------+------+-----+---------+----------------+

2. Insert into MySQL server:

Add enctype="multipart/form-data" in the <form> tag in order to enable the file upload functionality.
Set the type to ‘file’ for <input> tag.

Upload image file form:

<form action="index.php" method="post" enctype="multipart/form-data">
  <input type="file" name="prod_pic" accept=".jpg, .jpeg, .png">
  <button type="submit">Submit</button>  
</form>

Insert image into MySQL server in index.php

$prod_pic = $mysqli->real_escape_string(file_get_contents($_FILES['prod_pic']['tmp_name']));
$prod_pic_type = $_FILES['prod_pic']['type'];
 :
 :
$sql = "UPDATE products 
        SET prod_pic      = '{$prod_pic}',
            prod_pic_type = '{$prod_pic_type}'
        WHERE prod_id = {$prod_id}";
$mysqli->query($sql) or die($mysqli->connect_error);
$mysqli->close(); 

As you can see, the key part is:

file_get_contents($_FILES['prod_pic']['tmp_name'])

and

$mysqli->real_escape_string(......)

3. Retrieve an image from MySQL server:

There are two methods to retrieve images from MySQL server:

A. Get image with a function.

Somewhere in the page: (using Smarty template engine)

 <img src="get_image.php?prod_id={$product.prod_id}" />

get_image.php

<?php
  $mysqli = new mysqli( _DBHOST, _DBUSER, _DBPASSWD, _DBNAME);
  if ($mysqli->connect_error){
    die ('DB Error: ('.$mysqli->connect_errno.' )'.$mysqli->connect_error);
  } else {
    $mysqli->set_charset('utf8');
  }

  $prod_id = isset($_REQUEST['prod_id']) ? filter_var($_REQUEST['prod_id'], FILTER_SANITIZE_NUMBER_INT) : 0;

  $sql = "SELECT prod_pic, prod_pic_type FROM products WHERE prod_id = '{$prod_id}'";
  $result = $mysqli->query($sql) or die($mysqli->connect_error);
  $product = $result->fetch_assoc();
  $mysqli->close();

  header("Content-type: ".$product['prod_pic_type']);
  echo $product['prod_pic'];
?>

The last two lines of code is the key part.

header("Content-type: ".$product['prod_pic_type']);  
echo  $product['prod_pic'];

B. Using base64 encode to show the image

<img src="data:image/{$product.prod_pic_type};base64,{base64_encode($product.prod_pic)}" />

( using Smarty template engine )

4. Conclusion

Method B seems to be a better choice since it does not need to retrieve data from server twice as method A does. Also, method B is more concise and cleaner. It’s only one line of code and an extra PHP function is not necessary.

Deploy A PHP MySQL Application to Heroku

I usually put my apps on Heroku since I have developed apps in Ruby on Rails. It’s a reliable PaaS service and provides a free dyno for users. Therefore, Heroku is an excellent place to show your apps to others.

Heroku is known as a very friendly PaaS service for Ruby on Rail. Just few git commends, an app in RoR can be easily deployed to Heroku. Now it supports PHP as well. The following steps are the real operations of the deployment of my app to Heroku. It seems that very simple and similar to the deployment of RoR, only clearDB (MySQL) needs to be installed manually.

Install Composer

Composer is PHP’s package manager. Using brew to install composer is the easiest way in macOS.

$ brew install composer

Add An Empty composer.json File in the Root Directory

The Heroku PHP Support will be applied to applications only when the application has a file named composer.json in the root directory. Just add a text file with a pair of curly braces in the root directory if there is no depending package.

composer.json

{}

git commit

$ git add .
$ git commit -m ‘add composer.json file’

Heroku Login

Follow the instruction from: Getting Started with PHP : Set-up

$ heroku login
heroku: Enter your login credentials
Email [chxxxxxx@gmail.com]:
Password: *************
Logged in as chxxxxxx@gmail.com

Deploy the App

Create An App Name

$ heroku create
Creating app... done, ⬢ dry-crag-62595
https://dry-crag-62595.herokuapp.com/ | https://git.heroku.com/dry-crag-62595.git

or

$ heroku create [app_name] 

[app_name]: the name of the app

Show The git remote Configuration

$ git remote -v
heroku  https://git.heroku.com/dry-crag-62595.git (fetch)
heroku  https://git.heroku.com/dry-crag-62595.git (push)
origin  http://github.com/chaoyee/php_crud_demo.git (fetch)
origin  http://github.com/chaoyee/php_crud_demo.git (push)

Deploy the App

$ git push heroku master
Enumerating objects: 300, done.
Counting objects: 100% (300/300), done.
Delta compression using up to 8 threads
Compressing objects: 100% (295/295), done.
Writing objects: 100% (300/300), 1.03 MiB | 10.37 MiB/s, done.
Total 300 (delta 93), reused 0 (delta 0)
remote: Compressing source files... done.
remote: Building source:
remote:
remote: -----> PHP app detected
remote: -----> Bootstrapping...
remote: -----> Installing platform packages...
remote: NOTICE: No runtime required in composer.lock; using PHP ^7.0.0
remote: - nginx (1.8.1)
remote: - php (7.2.11)
remote: - apache (2.4.34)
remote: -----> Installing dependencies...
remote: Composer version 1.7.2 2018-08-16 16:57:12
remote: -----> Preparing runtime environment...
remote: NOTICE: No Procfile, using 'web: heroku-php-apache2'.
remote: -----> Checking for additional extensions to install...
remote: -----> Discovering process types
remote: Procfile declares types -> web
remote:
remote: -----> Compressing...
remote: Done: 16.2M
remote: -----> Launching...
remote: Released v3
remote: https://dry-crag-62595.herokuapp.com/ deployed to Heroku
remote:
remote: Verifying deploy... done.
To https://git.heroku.com/dry-crag-62595.git
* [new branch] master -> master

View Logs

If there is any error, we can view the log for more information.

$ heroku logs --tail

2018-11-05T08:24:32.207801+00:00 app[api]: Release v1 created by user chaoyee22@gmail.com
2018-11-05T08:24:32.313450+00:00 app[api]: Enable Logplex by user chaoyee22@gmail.com
2018-11-05T08:24:32.207801+00:00 app[api]: Initial release by user chaoyee22@gmail.com
2018-11-05T08:29:59.153108+00:00 heroku[web.1]: State changed from starting to up
:
:

Define A Procfile

A Procfile is a text file in the root directory of the application which defines process types and explicitly declares what command should be executed to start the application.

Procfile:

web: heroku-php-apache2

Change Apps Name

Since Heroku generates a random name for the app, we can change it if needed.

$ heroku apps:rename [newname]
$ git remote rm heroku
$ git remote add heroku https://git.heroku.com/[newname].git

[newname]: the new name of the app.

Create ClearDB (MySQL)

ClearDB is a database service based on MySQL Server for Heroku.

$ heroku addons:create cleardb:ignite

Creating cleardb:ignite on ⬢ php-crud-demo... free
Created cleardb-animated-20025 as CLEARDB_DATABASE_URL
Use heroku addons:docs cleardb to view documentation

Get the database url:

$ heroku config | grep CLEAR_DATABASE_URL

=== php-crud-demo Config Vars

CLEARDB_DATABASE_URL: mysql://bf4e1f86266e0e:xxxxxxxx@[us-cdbr-iron-east-01.cleardb.net/heroku_340c60cc91508db?reconnect=true

The format of CLEARDB_DATABASE_URL is:
CLEARDB_DATABASE_URL =>
mysql://[username}:[password]@[host]/[database name]?reconnect=true

Login to MySQL Server

$ mysql -u bf4e1f86266e0e -h us-cdbr-iron-east-01.cleardb.net -p
password: ******

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 114469329
Server version: 5.5.56-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Now, the MySQL Server is installed.

Create Tables and Insert Test data

Using Sequel Pro, MySQL Workbench, phpMyAdmin or other applications to execute sql script and create tables and insert test data.

Adding following PHP code to database configuration (ex. config.php) in the Application in order to connect to MySQL server.

<?php  
$url = parse_url(getenv("CLEARDB_DATABASE_URL"));  
define('_DBHOST', $url["host"]);  
define('_DBUSER', $url["user"]);  
define('_DBPASSWD', $url["pass"]);  
define('_DBNAME', substr($url["path"], 1));

$mysqli = new mysqli( _DBHOST, _DBUSER, _DBPASSWD, _DBNAME);
?>

Deploy Application to Heroku

$ git add .
$ git commit -m ‘add db connection’
$ git push heroku master

Open App at Heroku

$ heroku open

Now, the app starts up in the browser from Heroku service.
Here is the demo app php-crud-demo.

Reset AUTO_INCREMENT in MySQL

When creating a new record in MySQL server with a primary key, the “id” field (the primary key) is set to be auto incremented. Meaning the value of the primary key (“id” field) starts from 1, and the value of the primary key of the next record is increased by 1, which is 2. MySQL server takes cares of it without user interference.

What if the records have been deleted, and the primary key has to be reset to 1?

We can reset the counter with:

ALTER TABLE tablename AUTO_INCREMENT = 1

The primary key is reset to 1.

RENAME DATABASE name in MySQL

1. “RENAME” command

Syntax：

RENAME DATABASE db_name TO new_db_name

There is the “rename database” command for the early MySQL version, but it was removed after version 5.1.23. It was because the possibility of data lost when using it.

p.s. There shows an error when using "“rename database”:

mysql> rename database shop to mini_shop;  
ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use 
near 'database shop to mini_shop' at line 1

2. Create a new database, rename all tables of old database to new one. Then drop the old database.

CREATE DATABASE new_db_name;  
RENAME TABLE db_name.table1 TO new_db_name.table1,  
db_name.table2 TO new_db_name.table2;  
DROP DATABASE db_name;

Using Docker to Install MySQL server

1. Go to docker official site, download docker file and install in the local machine.

2. Start docker, type in the following command to ensure docker is running.

   docker images

3. Download MySQL image form docker hub with following commands.

   docker pull mysql/mysql-server

   docker pull mysql/mysql-server:5.7 (version 5.7)

4. Start MySQL server in the container, forward the port out for host connection.

   docker run --name mysqld -e MYSQL_ROOT_PASSWORD=1234 -d -p 3306:3306 mysql/mysql-server

   or

   docker run --name mysql57 -e MYSQL_ROOT_PASSWORD=1234 -d -p 3306:3306 mysql/mysql-server:5.7

   -e: Set environment variables

5. Host connects to MySQL server in docker.

   docker exec -it mysqld bash

   bash> mysql -u root -p (ask for password)

   Using sequel pro , a free GUI to manipulate MySQL server. MySQL Workbench is another good choice!

6. Stop the container.

   docker stop mysqld

7. Start the container.

   docker start mysqld

8. List port mappings or a specific mapping for the container

   docker port mysqld

9. Remove container.

   docker rm mysqld

10. Local host client can not connect to the MySQL server since ‘root’ is limited to localhost connection (within the container).

mysql> select host, user from mysql.user;  
+-----------+---------------+  
| host      |      user     |  
+-----------+---------------+  
| localhost | healthchecker |  
| localhost | mysql.session |  
| localhost | mysql.sys     |  
| localhost | root          |  
+-----------+---------------+  
4 rows in set (0.00 sec)

Change root’s access right for all machines.

mysql> update mysql.user set host='%' where user='root';

mysql> select host, user from mysql.user;  
+-----------+---------------+  
|   host    |     user      |  
+-----------+---------------+  
|    %      |     root      |  
| localhost | healthchecker |  
| localhost | mysql.session |  
| localhost | mysql.sys     |  
+-----------+---------------+  
4 rows in set (0.00 sec)

Restart docker afterward.

docker restart mysql57