Creating and configuring a new MySQL database via the command line

When developing almost any application, you will need to have database of some type to store the data created and used by the application and its users. The most popular open-source database is MySQL. Of course, there are other well-known open-source database platforms, such as PostgreSQL and SQLite, as well as so-called NoSQL databases like MongoDB, since MySQL is ubiquitous, we’ll work with it.

Just as there are many different database platforms, there are many ways to interact with MySQL. There are web-based tools, like phpMyAdmin and Adminer, and regular client applications, such as DBeaver and SQuirrel SQL, all of which are free and open-source. I use and can recommend all of the tools mentioned. However, since you may not always have access to these tools, it’s always good to know how to use the default command-line/shell tools for MySQL.

To log into MySQL at the command line, enter this command:

mysql -uusername -ppassword

Note that there is no space between the -u and -p and the username and the password, respectively. (Technically, you can put a space between -u and username, but I find it difficult to remember which one you can and which one you can’t, so I just do them both the same way!) Assuming that you enter a valid username and password, you will be logged in and at the MySQL command prompt: mysql>.

All MySQL commands end with ; (semi-colon). To try things out, let’s look at the databases that you already have:

mysql> SHOW DATABASES;

Running this command should return something like this:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.40 sec)

Every MySQL database server will have an information_schema and a mysql database. These databases hold metadata about the configuration of the MySQL instance itself. While you won’t often use these databases, you might want to know a little about them.

Let’s create a new database and grant privileges (permissions) to that database to a specific (new) user. I prefer to create a user with the same name as the database itself, but you can use any username that you like. For this example, we’ll the username will be different from the database name to avoid confusion. Of course, to maintain security, you should usually not use the MySQL root user for access to application databases.

mysql> CREATE DATABASE `laravel` CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> CREATE USER 'laravel_user'@'%' IDENTIFIED BY 's3cr3t';
mysql> GRANT ALL ON `laravel.*` TO 'laravel_user'@'%';

After each command above, you should get a response message of Query OK followed by the number of rows affected (usually 0 or 1 for DDL commands like these). Also, if you get the message ERROR 1046 (3D000): No database selected when running the GRANT command, this means that MySQL doesn’t know what database you are referring to. You can execute USE `laravel`; to select the laravel database as the default.

Let’s look at the details of each of these commands in turn. The CREATE DATABASE command does just that: it creates a new database with the given name. Note that we included the name (laravel) in a pair of back ticks (`). While this isn’t absolutely necessary in most cases, it’s good practice since it allows you to use non-ASCII characters in the name. Likewise, we specified the optional CHARACTER SET and COLLATE parameters. By using UTF8 character set, this ensures that our database can store non-ASCII characters properly. Essentially, we are making our database more flexible.

Next, we turn to the CREATE USER command. Obviously, this creates a new user in our database platform. However, initially, this user (laravel_user) can’t actually do anything (other than log into MySQL). The username should be contained in single quotes (') [not back ticks!). Also, you are probably wondering about the @'%' following the username. This defines the scope of accessiblity for this user and % essentially means that this user can connect to the database from any host (machine). If you want to restrict connections to only the host where MySQL is running, such as in a development environment, you can specify @'localhost' (or @'127.0.0.1') instead of @'%'. Finally, the IDENTIFIED BY clause specifies the password (called authentication_string in MySQL terminology) for the user. In this case, we simply specify the actual password (s3cr3t) enclosed in single quotes. MySQL hashes passwords so that they are not stored in plain text in the database. You can see the hashed value for this user (laravel) by running:

mysql> select authentication_string from mysql.user where user = 'laravel_user';

You should get a result similar to this:

+-------------------------------------------+
| authentication_string                     |
+-------------------------------------------+
| *58C036CDA51D8E8BBBBF2F9EA5ABF111ADA444F0 |
+-------------------------------------------+
1 row in set (0.08 sec)

Obviously, the actual value will (or should!) be different on your system.

Finally, we come to the GRANT command, which explicitly gives priveliges (permissions) to a user for certain databases or objects in a database. In this case the ALL specification means that all privileges (permissions) available are being given (“granted”) to the specified user. If you want to grant only certain permissions, such as to allow “read-only” access, you can specify any list of the other privileges, such as SELECT, CREATE, UPDATE, and DELETE. (See MySQL documentation on GRANT for full list.) The specification after the ON indicates the scope of the privileges that are granted. In this case, by specifying `laravel.*`, we are granting these permissions to all objects (not only tables) in the laravel database. If you want to limit permissions to only a specific table, you could specify that, such as `laravel.user`. And, last but not least, the TO clause indicates which user (or users) these permissions should be given to. Again, the format of the user follows that of the format for CREATE USER. You can specify multiple usernames by separating each with a comma (,).

To log out of the MySQL command prompt, run:

mysql> exit;

Now, we have our database created and ready to use. In Laravel, you need to update the .env file in the root directory of your project with the appropriate parameter values to match your new database. Using the database above (and assuming that MySQL server is running on the same machine as our Laravel project), our settings would be:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel
DB_USERNAME=laravel_user
DB_PASSWORD=s3cr3t

Hope you enjoyed this article. Please provide feedback/questions in the comments.

Advertisements

Install PHP 5.6 (or 5.5) in Ubuntu 16.04 LTS (Xenial Xerus)

Ubuntu has released the latest LTS (Long-Term Support) version 16.04 (Xenial Xerus) of their distribution. The official Ubuntu repositories include only PHP 7.0, since it is the official version (at the time of the 16.04 release). However, many developers still use PHP 5.5 or 5.6 in their production environments, so developing on these versions is frequently necessary. As usual, the PPAs come to our rescue for installing PHP 5.5 or 5.6 on Ubuntu 16.04. Here’s how.

First, we add the PHP 5 PPA maintained by Ondrej Sury and update the repository data on your Ubuntu installation:

sudo apt-add-repository -y ppa:ondrej/php
sudo apt-get -y update

If you are familiar with these repositories, you’ll notice that the PHP version is not included in the PPA repository name. For Ubuntu 16.04, both PHP 5.5 and 5.6 are included in the PPA. (If you need PHP 5.4 or earlier, you’ll need to stay with Ubuntu 14.04 LTS Trusty Tahr.)

Now, we can install both PHP 5.6 (or 5.5) and 7.0, along with support for Apache 2 and MySQL, including various PHP modules, such as those required for Laravel framework:

sudo apt-get -y install php7.0 php5.6-mysql php5.6-cli php5.6-curl php5.6-json php5.6-sqlite3 php5.6-mcrypt php5.6-curl php-xdebug php5.6-mbstring libapache2-mod-php5.6 libapache2-mod-php7.0 mysql-server-5.7 apache2

As with any LAMP installation on Ubuntu, you’ll be prompted for the administrator (root) password for MySQL. Also, note that the package for XDebug (php-xdebug) does not include a version, since it supports both PHP 5.6 (or 5.5) and 7.0. Most other packages are PHP version-specific. If you aren’t sure, just enter part of the name on the apt-get command line and press <Tab> to see the various options.

Once we have PHP 5.6 (or 5.5) and 7.0 installed, we can easily switch between them from the shell using these commands. To enable PHP 5.6 (and disable PHP 7.0) use this command:

sudo a2dismod php7.0 ; sudo a2enmod php5.6 ; sudo service apache2 restart ; echo 1 | sudo update-alternatives --config php

Similarly, to switch from PHP 5.6 to PHP 7.0, use this command:

sudo a2dismod php5.6 ; sudo a2enmod php7.0 ; sudo service apache2 restart ; echo 2 | sudo update-alternatives --config php

Or, better yet, why not set up a couple of simple Bash aliases in your .bashrc to take care of this for you:

alias phpv5='sudo a2dismod php7.0 ; sudo a2enmod php5.6 ; sudo service apache2 restart ; echo 1 | sudo update-alternatives --config php'
alias phpv7='sudo a2dismod php5.6 ; sudo a2enmod php7.0 ; sudo service apache2 restart ; echo 2 | sudo update-alternatives --config php'

Now, you can just run phpv5 or phpv7 to switch to PHP 5.6 or PHP 7.0, respectively.

In the commands above to switch/re-start PHP versions, the update-alternatives command is used switch the version of PHP that is used when running PHP from the shell/command line. This is especially important when using PHP command-line tools, such as Composer and the Laravel Artisan commands.

Update

If you run into problems starting Apache 2 web server (sudo service apache2 restart) with the error “Apache is running a threaded MPM, but your PHP Module is not compiled to be threadsafe. You need to recompile PHP.”, then run the following commands in Ubuntu:

sudo a2dismod mpm_event
sudo a2enmod mpm_prefork
sudo service apache2 restart

This should work for both PHP 5.6 and PHP 7.0; therefore, you should not need to run this again when switching between PHP versions.

Let me know if you have any questions in the comments below.

Increase session time-out in phpMyAdmin

If you do any development with PHP and MySQL, undoubtedly, you are familiar with phpMyAdmin, which is a web-based tool for managing and editing MySQL databases. While phpMyAdmin is an excellent tool, one of it’s annoying aspects is the default session time-out of 30 minutes. However, you can make a quick configuration change to extend the time-out to most any value that you like.

To do this, just open the config.inc.php file for phpMyAdmin (which is in the /etc/phpmyadmin directory on Ubuntu Linux) in an editor as the administrative user (i.e., use sudo in Linux). Then, add these lines within the 'Servers' configuration section:

/* Set session time out to 8 hours (28800 seconds) */
$cfg['Servers'][$i]['LoginCookieValidity'] = 28800;

Save the changes and restart the Apache web server:

sudo service apache2 restart

The next time that you log into phpMyAdmin, you will stay logged in for 8 hours (or whatever duration you specified). For more information, see phpMyAdmin documentation for LoginCookieValidatity.

Install latest version of Adminer MySQL administration tool on Ubuntu Linux

Adminer is a lightweight, PHP-based MySQL administration tool that is a great alternative to PHPMyAdmin. It comes as a single file and can easily be installed globally on your Ubuntu Linux box, including a Vagrant box.

The prerequisites for installing and using Adminer are PHP, MySQL, and Apache, the so-called LAMP stack. To install them on Ubuntu, in the terminal, run:

sudo apt-get install php5 php5-cli php 5-mysql mysql-server apache2

You’ll be prompted for configuration information during the installation.

Once the LAMP stack is installed, install Adminer by running these commands in the terminal:

sudo mkdir /usr/share/adminer
sudo wget -O /usr/share/adminer/latest.php "http://www.adminer.org/latest.php"
sudo ln -s /usr/share/adminer/latest.php /usr/share/adminer/adminer.php
echo "Alias /adminer.php /usr/share/adminer/adminer.php" | sudo tee /etc/apache2/conf.d/adminer.conf
sudo service apache2 restart

After installing Adminer, open a web browser and navigate to http://localhost/adminer.php. The Adminer login screen should be displayed. You can now log in to any of the MySQL databases configured.

If you want to upgrade to a new version of Adminer later, since you created a symlink to the latest.php, you can simply run this command in the terminal:

sudo wget -O /usr/share/adminer/latest.php "http://www.adminer.org/latest.php"

Create model classes for Laravel e-commerce application

In our last post, we created Laravel migrations to generate our database tables. Now that we have these tables, we need to create corresponding model classes for each of the tables.

Before we get into creating the model classes, let’s look at what models are used for. Models are the ‘M’ in MVC, the model-view-controller design pattern. The fundamental role of the model is to manage how your application interacts with the database. In general, you will have one model for each table in the database. In addition to database interaction, the model will hold the business rules, including relationships between the various entities represented by the various models.

In our e-commerce application, we will be creating four models corresponding to each of the database tables: account (customer), order, order_item, and product. All of our model files will be in the app/model directory. Typically, the model file and class names follow the name of the corresponding table, except they usually don’t use underscores. Furthermore, while not strictly required, I suggest that you specify the corresponding table name explicitly in the $table attribute (variable) of the class.

To begin, we’ll use the Generators package again to help us create our model classes. (We used Generators to generate the framework for our database migrations classes previously.) As you recall, the account table looks like:

mysql> describe account;                                                                  
+-----------------------------+------------------+------+-----+---------+----------------+
| Field                       | Type             | Null | Key | Default | Extra          |
+-----------------------------+------------------+------+-----+---------+----------------+
| account_id                  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| email_addr                  | varchar(50)      | NO   |     | NULL    |                |
| password                    | varchar(30)      | NO   |     | NULL    |                |
| admin_ind                   | tinyint(1)       | NO   |     | 0       |                |
| address1                    | varchar(50)      | YES  |     | NULL    |                |
| address2                    | varchar(50)      | YES  |     | NULL    |                |
| city                        | varchar(20)      | YES  |     | NULL    |                |
| state                       | varchar(2)       | YES  |     | NULL    |                |
| postal_code                 | varchar(20)      | YES  |     | NULL    |                |
| created                     | datetime         | NO   |     | NULL    |                |
| created_by_account_id       | int(11)          | NO   |     | NULL    |                |
| last_modified               | datetime         | NO   |     | NULL    |                |
| last_modified_by_account_id | int(11)          | NO   |     | NULL    |                |
+-----------------------------+------------------+------+-----+---------+----------------+
13 rows in set (0.01 sec)                                                                 

To generate the model class for the account table, simply run:

php artisan generator:model Account

This will create the “shell” Account model class file in app/models/Account.php. Open this file in your editor, such as Aptana and update the content. Here is the complete Account class that we’ll use:

<?php
	use Illuminate\Auth\UserInterface;
	use Illuminate\Auth\Reminders\RemindableInterface;
	
	class Account extends Eloquent
		implements UserInterface, RemindableInterface
	{
		/**
		 * The database table used by the model.
		 *
		 * @var string
		 */
		protected $table = "account";
		
		/**
		 * The primary key (PK) column in database table.
		 *
		 * @var string
		 */
		protected $primaryKey = "account_id";
		
		/**
		 * The attributes excluded from the model's JSON form.
		 *
		 * @var array
		 */
		protected $hidden = array("password", "admin_ind",
			"created", "created_by_account_id",
			"last_modified", "last_modified_by_account_id");
		
		/**
		 * The attributes excluded from mass assignment.
		 *
		 * @var array
		 */
		protected $guarded = array("account_id", "admin_ind");
		
		/**
		 * The attributes *explicitly* included for mass assignment.
		 *
		 * @var array
		 */
		protected $fillable = array("email_addr", "address1", "address2",
			"city", "state", "postal_code");
		
		protected $softDelete = true;
		
		/**
		 * Each account (customer) can have many order items.
		 *
		 * @var array
		 */
		public function orders() {
			return $this->hasMany("Order", "account_id");
		}
		
		/**
		 * Get the unique identifier for the account.
		 * Required by Illuminate\Auth\UserInterface.
		 *
		 * @return mixed
		 */
		public function getAuthIdentifier()
		{
		  return $this->getKey();
		}
 
		/**
		 * Get the password for the account.
		 * Required by Illuminate\Auth\UserInterface.
		 *
		 * @return string
		 */
		public function getAuthPassword()
		{
		  return $this->password;
		}
 
		/**
		 * Get the e-mail address where password reminders are sent.
		 * Required by Illuminate\Auth\Reminders\RemindableInterface.
		 *
		 * @return string
		 */
		public function getReminderEmail()
		{
		  return $this->email;
		}
		
		/**
		 * Some "user-friendly" aliases for the interface methods.  🙂
		 */
		public function getAccountId()
		{
			return $this->getAuthIdentifier();
		}
		
		public function getEmailAddr()
		{
			return $this->getReminderEmail();
		}
		
		public function getPassword()
		{
			return $this->getAuthPassword();
		}
	
	}
?>

You can refer to the Eloquent documentation on the Laravel web site for more details, but let’s explain a few of the concepts here.

  • $table – This specifies the name of the database table that this model corresponds to. If the class name matches the table name, it is not strictly required to specify the table, but I find it best to be explicit about this.
  • $primaryKey – The default convention for Laravel is for each table to have a column named id which is the primary key (PK). I prefer to include the table name in the PK column, so we must specify this column explicitly in our class defintion.
  • $hidden – This is a list (array) of the columns in the table that are note returned from the JSON API. In our example, we would not want to expose the password!
  • $guarded – Laravel supports the concept of mass (bulk) assignment of attributes. However, we may want to prevent mass assignment of certain attributes for security reasons. The $guarded array is a list of such columns.
  • $fillable – These are the “opposite” of the $guarded columns in that these are explicitly allowed to be mass assigned.
  • Next, we have the orders() method:
    		public function orders() {
    			return $this->hasMany("Order", "account_id");
    		}	
    	

    This is the paradigm for how Laravel defines relationships between models and tables. In this case, we are indicating that a given account (user) can have multiple (hasMany) orders. You will notice that the reference is actually to the Order model (and not strictly to the order table). Furthermore, we are explicitly noting that the foreign key column to account on order is the account_id column. Also, note that since we are using the hasMany relationship, we specified the method name using the plural form: orders. For an excellent discussion about the various relationships provided by Eloquent, see Visualizing Laravel Relationships.

    In addition, when we get to the Order model, you will see that we have a reciprocal relationship defined back to the account using the (singular) belongsTo method.

  • Finally, we have the three get...() methods. The methods and especially their names may seem a bit awkward. However, you’ll notice that on the class declaration, we have included implements UserInterface, RemindableInterface (and referenced those interface libraries via use directives at the top of the class). An interface is kind of like a template for a class. Essentially, it tells Laravel (or really PHP itself!) that the class that implements the interface must include certain attributes (variables) and/or methods (functions). See the PHP documentation on interfaces for more information. The most common use for interfaces in Laravel is to handle inversion of control (IoC) design pattern, which is useful for application testing.

    You will also notice that I created three additional “helper” methods with more “user-friendly” names, which simply call the methods required by the interfaces. This is just to allow us to use names that are more meaningful when calling the methods ourselves.

This takes care of the Account model. Here are the complete definitions for our other three model classes: Order, OrderItem, and Product. Note that the “headers” for these look a bit different, since none of them implement any interfaces.

Order Model

<?php
	
	class Order extends Eloquent
	{
		/**
		 * The database table used by the model.
		 *
		 * @var string
		 */
		protected $table = "order";
		
		/**
		 * The primary key (PK) column in database table.
		 *
		 * @var string
		 */
		protected $primaryKey = "order_id";
		
		/**
		 * The attributes excluded from the model's JSON form.
		 *
		 * @var array
		 */
		protected $hidden = array("created", "created_by_account_id",
			"last_modified", "last_modified_by_account_id");
		
		/**
		 * The attributes excluded from mass assignment.
		 *
		 * @var array
		 */
		protected $guarded = array("account_id", "order_id");
		
		/**
		 * The attributes *explicitly* included for mass assignment.
		 *
		 * @var array
		 */
		protected $fillable = array("order_status", "order_subtotal", "order_net",
			"order_gross", "shipping_fee", "shipping_method", "order_notes");
		
		protected $softDelete = true;
		
		
		/**
		 * Each order belongs to EXACTLY one customer (account).
		 *
		 * @var array
		 */
		public function account() {
			return $this->belongsTo("Account", "account_id");
		}		

		/**
		 * Each order contains one or more order items.
		 *
		 * @var array
		 */		
		public function orderItems() {
			return $this->hasMany("OrderItem", "order_id");
		}
		
		public function getAccountId()
		{
		  return $this->account_id;
		}
		
		public getOrderId()
		{
		  return $this->getKey();
		}
	
	}
?>

OrderItem Model

<?php
	
	class OrderItem extends Eloquent
	{
		/**
		 * The database table used by the model.
		 *
		 * @var string
		 */
		protected $table = "order_item";
		
		/**
		 * The primary key (PK) column in database table.
		 *
		 * @var string
		 */
		protected $primaryKey = "order_item_id";
		
		/**
		 * The attributes excluded from the model's JSON form.
		 *
		 * @var array
		 */
		protected $hidden = array("created", "created_by_account_id",
			"last_modified", "last_modified_by_account_id");
		
		/**
		 * The attributes excluded from mass assignment.
		 *
		 * @var array
		 */
		protected $guarded = array("product_id", "order_id");
		
		/**
		 * The attributes *explicitly* included for mass assignment.
		 *
		 * @var array
		 */
		protected $fillable = array("qty", "extended_price");
		
		protected $softDelete = true;
		
		/**
		 * Each order item belongs to EXACTLY one order.
		 *
		 * @var array
		 */
		public function order() {
			return $this->belongsTo("Order", "order_id");
		}
		
		/**
		 * Each order item references EXACTLY one product.
		 *
		 * @var array
		 */
		public function product() {
			return $this->hasOne("Product", "product_id");
		}
		
		public function getProductId()
		{
			return $this->product_id;
		}
		
		public function getOrderId()
		{
			return $this->order_id;
		
		public getOrderItemId()
		{
			return $this->getKey();
		}
	
	}
?>

Product Model

<?php
	
	class Product extends Eloquent
	{
		/**
		 * The database table used by the model.
		 *
		 * @var string
		 */
		protected $table = "product";
		
		/**
		 * The primary key (PK) column in database table.
		 *
		 * @var string
		 */
		protected $primaryKey = "product_id";
		
		/**
		 * The attributes excluded from the model's JSON form.
		 *
		 * @var array
		 */
		protected $hidden = array("created", "created_by_account_id",
			"last_modified", "last_modified_by_account_id");
		
		/**
		 * The attributes excluded from mass assignment.
		 *
		 * @var array
		 */
		protected $guarded = array("product_id");
		
		/**
		 * The attributes *explicitly* included for mass assignment.
		 *
		 * @var array
		 */
		protected $fillable = array("product_code", "product_name", "product_description");
		
		protected $softDelete = true;
		
		/**
		 * Each product can be used (referenced) by many order items.
		 *
		 * @var array
		 */
		public function orderItems() {
			return $this->belongsToMany("OrderItem", "product_id");
		}
		
		public getProductId()
		{
			return $this->getKey();
		}
	
	}
?>

Next time, we’ll look at adding some test data to the database, which is called “seeding” the database in Laravel. See you then!

References

Laravel 4 Generators package on Github
Setting up your first Laravel 4 Model

Correct Laravel application directory structure for proper security

In our first tutorial about Laravel development on Ubuntu, I had recommended moving the contents of the public folder in the Laravel installation up to the Laravel “root” directory. As I’ve been working on my application and these tutorials, I’ve realized that this was a short-sighted approach in the interest of expediency for development, which will cause difficulty when we want to deploy our application later. In particular, we want to apply the principle of least privilege and, accordingly, only make the necessary files available to our users. In general, this would be the contents of the public folder of our Laravel project.

For this example, we are going to consider the common scenario where we are using shared hosting. In this situation, on the hosting provider server, we typically have a “public” directory, often named www or public_html or similar, and other folders which are “private”. We will configure our development environment similarly by putting the contents of the Laravel public folder in the Ubuntu standard Apache “public” site folder (/var/www/html) and the remainder of the Laravel application files in a folder (sub-directory) under our projects folder.

Let’s begin by assuming that we have a standard “clean” Laravel installation in ~/projects/laravel-project. By “clean”, we mean that the public folder is unchanged from the installation using composer create-project laravel/laravel laravel-project --prefer-dist. In the Terminal, run:

sudo mkdir /var/www/html/project
sudo chown -hR username:username /var/www/html/project
cp -R ~/projects/laravel-project/public/* /var/www/html/project
sudo chown www-data:www-data /var/www/html/project/.htaccess
rm -rf ~/projects/laravel-project/public

where username is your Linux user ID. We are simply copying the contents of the Laravel public folder to the new directory for this particular application (project) in the standard Apache “public” directory. I recommend using similar naming between the main (base) Laravel directory and the “public” directory with laravel in the name for the application directory and the “simple” name (without laravel) for the “public” directory. For example, in this case we have laravel-project and project, respectively. Also, note that we must set the ownership of the .htaccess file to the Apache account (www-data).

Next, we’ll set up a specific Apache named virtual host for this directory (/var/www/html/project), which we’ll call project.dev. Create (using sudo!) a new configuration file named /etc/apache2/sites-available/project.dev.conf and add the following to the file:

<VirtualHost *:80>
	ServerAdmin webmaster@localhost
	ServerName project.dev
	DocumentRoot /var/www/html/project

	<Directory "/var/www/html/project/">
		Options Indexes FollowSymLinks MultiViews
		AllowOverride All
		Order allow,deny
		allow from all
	</Directory>

	LogLevel warn
	ErrorLog ${APACHE_LOG_DIR}/error.log
	CustomLog ${APACHE_LOG_DIR}/access.log combined
</VirtualHost>

Also, add project.dev as an alias for 127.0.0.1 to /etc/hosts so that we can use URL http://project.dev/ in the web browser. Finally, activate the new named virtual host and restart Apache server:

sudo a2ensite project.dev.conf
sudo service apache2 restart

Now, we need to update the paths in two of the files that are part of application:

  • ~/projects/laravel-project/bootstrap/paths.php
  • /var/www/html/project/index.php

In each of these files, we are going to set the fully-qualified path names for the relevant files. You can certainly use the __DIR__ PHP “magic” constant and relative path names, but it often gets tricky about the number of levels that you need to move.

In ~/projects/laravel-project/bootstrap/paths.php, change these attributes in the array to:

'app' => '/home/username/projects/laravel-project/app',
'public' => '/var/www/html/project',
'base' => '/home/username/projects/laravel-project',
'storage' => '/home/username/projects/laravel-project/app/storage',

Again, change username in these to your Linux user ID.

Similarly, in /var/www/html/project/index.php, change the require and require_once directives to:

require '/home/username/projects/laravel-project/bootstrap/autoload.php'; 
$app = require_once '/home/username/projects/laravel-project/bootstrap/start.php';

After you have made these changes, you should be able to successfully run the application by going to http://project.dev/ in the web browser. This should display the standard “You have arrived” Laravel page. It should NOT be necessary to include /index.php in the URL, since the .htaccess file in /var/www/html/project removes it.

If you have trouble, make sure that the Apache mod_rewrite extension is enabled:

sudo a2enmod rewrite
sudo service apache2 restart

Likewise, ensure that you have AllowOverall All set in your virtual host configuration (or globally via the /etc/apache2/apache.conf file in the <Directory /var/www/> section).

With these changes, you should now be able to update your Laravel application files in ~/projects/laravel-project and changes to the “public” files in /var/www/html/project should not be necessary. Each time you make a change to the application, just refresh http://project.dev/ in your browser to observe the change.

To confirm this, open the app/routes.php file and add a new route to the end of the file:

Route::get('test', function()
{
	echo 'This is the "test" route!';
});

In the web browser, enter the URL http://project.dev/test (without index.php or trailing slash). The browser should simply display the text This is the “test” route!.

In addition, you will need to update the debugging configuration in Aptana (or Eclipse) to reference the appropriate URL (http://project.dev/). You will probably want to create a new External Web Server Configuration and set the Base URL to http://project.dev and the Document Root to /home/username/projects/laravel-project. In your Debug Configuration, set the Initial Script to bootstrap/start.php. Likewise, you will want to uncheck (disable) URL Auto Generate and leave the URL with just the base URL of http://project.dev/.

References

Setup Laravel 4 in Shared Hosting While Securing Laravel base files
Ubuntu 12.04 Laravel and mod rewrite

Create database tables for Laravel e-commerce application

Now that we have our requirements for our Laravel e-commerce application, we are ready to get started with actually developing the application. (Finally!)

We will use the powerful Laravel “migrations” feature to create our database tables and relationships. Migrations may seem like an odd name, but essentially it allows us to have some version control for the changes that we make to our database over the lifecycle of the project. Likewise, it is helpful for allowing others to take our source code and get their database up and running quickly, even if they are using a completely different database engine, since the migrations files are independent of the database platform that you are using.

In general, I like to use the migrations tool to create a template file and then manually customize the details to fit my preferences. In particular, I prefer to include the table name as part of the primary key column in the tables, whereas Laravel simply names the column ‘id’. Likewise, if you have some “boilerplate” columns that you like to include in all of your tables, using the template helps to ensure consistency of these column names.

  1. To begin, we need to create a database for our application. Since we are using the LAMP stack, we’ll create the database in MySQL. You can create the database in the Terminal (see below) or, if you prefer, you can use other tools, such as phpMyAdmin.

    For this example, we’ll name the database ‘laravel’ and simply use the MySQL ‘root’ user account. Likewise, I’m assuming that the MySQL ‘root’ account does not have a password. Obviously, this is VERY insecure, but it is fine for your local development environment. In a production scenario, you would want to create a specific user and grant that user limited permissions to the database.

    To log into MySQL, run:

    mysql -u root
    

    If the MySQL ‘root’ user has a password, you can simply append -p to the command and you’ll be prompted to enter the password.

    At the mysql> prompt, we will create our database and add a new user, with a password of ‘password’, with full privileges to the database.

    CREATE DATABASE laravel DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;
    CREATE USER 'laravel_crud'@'localhost' IDENTIFIED BY 'password';
    GRANT ALL PRIVILEGES ON laravel.* TO 'laravel_crud'@'localhost';
    

    After each command, MySQL should respond with Query OK and some other relevant information. In the last statement, laravel.* limits the command to the laravel database, but to all tables in that database.

    We chose the user name ‘laravel_crud’ to indicate that this user will have full privileges on this database. ‘CRUD’ is an acronym for ‘Create-Read-Update-Delete’, which are all of the typical operations on database tables.

    That’s all we need for the database for now! Simply log out of MySQL by entering exit;.

  2. Now that we have our database, we need to configure Laravel to access the database. In Aptana (or another text editor), open the app/config/database.php file and locate the 'connections' array. In the 'connections' array, update the 'mysql' element array:

    'mysql' => array(
       'driver'    => 'mysql',
       'host'      => 'localhost',
       'database'  => 'laravel',
       'username'  => 'laravel_crud',
       'password'  => 'password',
       'charset'   => 'utf8',
       'collation' => 'utf8_unicode_ci',
       'prefix'    => '',
    ),
    

    You can quickly confirm that the configuration is valid by displaying the site in your web browser (e.g., http://projects.local/laravel/). As long as the configuration is correct, the page should display correctly. If there is a configuration error, you’ll get a nice Laravel error message.

  3. Laravel’s built-in migration functionality will simply create a “shell” of the class for us. We will supplement this with the excellent Laravel 4 Generators package by Jeffrey Way. To add this (or any third-party) package, in the Terminal, navigate to the root of the Laravel project (e.g., ~/projects/laravel) and run:

    composer require "way/generators":"2.*" --prefer-dist
    

    This will add the package to your composer.json file. Now we just need to run a Composer update to install the new package and its dependencies, if any.

    composer update
    

    To activate the new Generators package, open the app/config/app.php file and add this line to the end of the providers array:

    'Way\Generators\GeneratorsServiceProvider',
    

    If you like, you can also add an alias, such as Generator to the aliases array. Having an alias makes it simple to reference the classes by the short name, instead of the fully-qualified name.

  4. Based on our requirements, we need the following tables (and associated Laravel models):

    • account
    • product
    • order
    • order_item

    Let’s start by creating a migration for the account table. We’ll then use this migration class as a template for the other tables.

    First, we need to create the migrations table in our database. This table simply keeps track of the various changes that we’ve made to the actual tables used by our application. To create the table, run:

    php artisan migrate:install
    

    To generate (using the Generators package which we added above) the migration for the account table, in the Terminal, from the project root directory, run:

    php artisan generate:migration create_account_table --fields="email_addr:string, password:string"
    

    This will create a migration file in the app/database/migrations with the name YYYY_MM_DD_HHMMSS_CreateAccountTable.php where YYYY is the 4-digit year, MM is the month, DD is the day and HHMMSS is the hour/minute/second that the migration was created. (Note that the HHMMSS is the UTC time, based on your system’s time zone setting.) For example, 2014_08_05_203018_CreateAccountTable.php. Open this file in your editor (Aptana, etc.). The generated file will look like:

    <?php
    
    use Illuminate\Database\Migrations\Migration;
    use Illuminate\Database\Schema\Blueprint;
    
    class CreateAccountTable extends Migration {
    
    	/**
    	 * Run the migrations.
    	 *
    	 * @return void
    	 */
    	public function up()
    	{
    		Schema::create('account', function(Blueprint $table)
    		{
    			$table->increments('id');
    			$table->string('email_addr');
    			$table->string('password');
    			$table->timestamps();
    		});
    	}
    
    
    	/**
    	 * Reverse the migrations.
    	 *
    	 * @return void
    	 */
    	public function down()
    	{
    		Schema::drop('account');
    	}
    
    }
    

    As you can see, this class essentially consists of the up() method, which creates the table in the database, and the down() method, which drops the table. When you apply the migration (see below), the up() method will create the account table in the database.

    We want to make a few customizations to the class for our specific needs. The only changes that are required are to the up(). Change this method as follows.

    	public function up()
    	{
    		Schema::create('account', function(Blueprint $table)
    		{
    			$table->increments('account_id');
    			$table->string('email_addr', 50);
    			$table->string('password', 30);
    			$table->boolean('admin_ind')->default(0);			
    			$table->string('address1', 50)->nullable();
    			$table->string('address2', 50)->nullable();
    			$table->string('city', 20)->nullable();
    			$table->string('state', 2)->nullable();
    			$table->string('postal_code', 20)->nullable();
    			$table->dateTime('created');
    			$table->integer('created_by_account_id');
    			$table->dateTime('last_modified');
    			$table->integer('last_modified_by_account_id');
    		});
    	}
    

    Now that we have a basic template for our migrations, we can use it for the other tables that we need for our database. Here are the details for each of the other classes. Again, we’ll only show the up() method, but you need to update the class name and the down() method for each, appropriately. Likewise, each class should be saved in it’s own file.

    product table

    	public function up()
    	{
    		Schema::create('product', function(Blueprint $table)
    		{
    			$table->increments('product_id');
    			$table->string('product_code', 20);
    			$table->string('product_name', 50);
    			$table->string('product_description');
    			$table->string('media_type', 10);
    			$table->integer('unit_qty');
    			$table->float('unit_price');
    			$table->string('download_directory', 100)->nullable();
    			$table->boolean('available_ind')->default(1);
    			$table->dateTime('created');
    			$table->integer('created_by_account_id');
    			$table->dateTime('last_modified');
    			$table->integer('last_modified_by_account_id');
    		});
    	}
    

    order table

    	public function up()
    	{
    		Schema::create('order', function(Blueprint $table)
    		{
    			$table->increments('order_id');
    			$table->integer('account_id')->unsigned();  // FK to account table
    			$table->string('order_status');
    			$table->float('order_gross')->nullable();
    			$table->float('order_net')->nullable();
    			$table->float('discount')->nullable();
    			$table->float('transaction_fee')->nullable();
    			$table->string('payment_gateway_txn_id', 30)->nullable();
    			$table->string('payment_gateway_order_id', 30)->nullable();
    			$table->dateTime('created');
    			$table->integer('created_by_account_id');
    			$table->dateTime('last_modified');
    			$table->integer('last_modified_by_account_id');
    		});
    	}
    

    order_item table

    	public function up()
    	{
    		Schema::create('order_item', function(Blueprint $table)
    		{
    			$table->increments('order_item_id');
    			$table->integer('order_id')->unsigned();  // FK to order table
    			$table->integer('product_id')->unsigned();  // FK to product table
    			$table->integer('qty');
    			$table->float('extended_price');
    			$table->dateTime('created');
    			$table->integer('created_by_account_id');
    			$table->dateTime('last_modified');
    			$table->integer('last_modified_by_account_id');
    		});
    	}
    
  5. Once we have all of the migrations classes created for our tables, we are ready to apply the migrations to create the tables in our database. Actually, running the migrations is very simple:

    php artisan migrate
    

    Laravel will list the migrations that are executed in the terminal. Likewise, you can check the migrations table in the database to see them. The batch column in the migrations table gets incremented for each run.

    If you need to revert any updates made via the migration, you run the migrate:rollback command which reverts only the last migration. If you want to start over completely, use the migrate:reset command. In addition, migrate:refresh will do a reset and then load the current migrations.

    To get a list of all of the migrations commands run php artisan list command. Most of the migrations commands start with ‘migrate’.

    To learn more about Laravel migrations, I highly recommend the article “Managing Databases with Migrations”.

That’s it for this topic. Next time, we’ll start creating the Laravel models corresponding to our database tables.