Laravel - Configuring Database

1) Database is an essential support for many applications.

2) Laravel provides a database configuration file that allows the developer to easily create connection to the database server such as sqlite, mysql, pgsql, and sqlserv).

3) Open {laravel root}\application\config\database.php.

4) Scroll down to Default Database Connection (e.g. Line no. 35++). Notice that the default database is mysql.



5) Scroll down to Database Connections (e.g. Line no. 49++). Look for mysql connection (e.g. Line no. 70).

5a) Edit your mysql database connections. Take note of the host, database, username and password.



6) Create laraveldb and laraveladmin in MySQL. We will be using Command Window as it provides a quick and flexible way of achieving our task objectives.

(Create Database Setup)
6a) Run a Command Window.
6b) Log in to MySQL as root.
6c) Create a new database, laraveldb
6d) Create a user laraveladmin with password p@ssw0rd
6e) Grant ALL Privilege on laraveldb to laraveladmin
6f) Log out of MySQL.

(Check Database Setup)
6g) Run a Command Window (or continue from step 6f)
6h) Log in as laraveladmin.
6i) Check available databases.

>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 519
Server version: 5.5.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdcol              |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
| webauth            |
+--------------------+
7 rows in set (0.00 sec)

mysql> create database laraveldb;
Query OK, 1 row affected (0.00 sec)

mysql> create user 'laraveladmin'@'localhost' identified by 'p@ssw0rd';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on laraveldb.* to 'laraveladmin'@'localhost' identif
ied by 'p@ssw0rd';
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdcol              |
| laraveldb          |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
| webauth            |
+--------------------+
8 rows in set (0.00 sec)

mysql> exit
Bye

>mysql -h localhost -u laraveladmin -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 520
Server version: 5.5.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| laraveldb          |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql>

7) Test Database connection using PHP scripts.

7a) Create {laravel root}\public\testdbcon.php
7b) Paste the following codes to testdbcon.php

<?php
if( isset($_POST['submit']) )
{
    echo $_POST['submit'];


// The variables have not been adequately sanitized to protect against SQL Injection attacks: http://us3.php.net/mysql_real_escape_string

 $hostname = trim($_POST['hostname']);
 $username = trim($_POST['username']);
 $password = trim($_POST['password']);
 $database = trim($_POST['database']);

 $link = mysql_connect("$hostname", "$username", "$password");
  if (!$link) {
   echo "<p>Could not connect to the server '" . $hostname . "'</p>\n";
         echo mysql_error();
  }else{
   echo "<p>Successfully connected to the server '" . $hostname . "'</p>\n";
//   printf("MySQL client info: %s\n", mysql_get_client_info());
//   printf("MySQL host info: %s\n", mysql_get_host_info());
//   printf("MySQL server version: %s\n", mysql_get_server_info());
//   printf("MySQL protocol version: %s\n", mysql_get_proto_info());
  }
 if ($link && !$database) {
  echo "<p>No database name was given. Available databases:</p>\n";
  $db_list = mysql_query("SHOW DATABASES");
  echo "<pre>\n";
  while ($row = mysql_fetch_array($db_list)) {
       echo $row['Database'] . "\n";
  }
  echo "</pre>\n";
 }
 if ($database) {
    $dbcheck = mysql_select_db("$database");
  if (!$dbcheck) {
         echo mysql_error();
  }else{
   echo "<p>Successfully connected to the database '" . $database . "'</p>\n";
   // Check tables
   
   $result = mysql_query("SHOW TABLES FROM ".$database);
   
   if (mysql_num_rows($result)!=FALSE){
   
    echo "<p>Available tables:</p>\n";
    echo "<pre>\n";
    while ($row = mysql_fetch_row($result)) {
     echo "{$row[0]}\n";
    }
    echo "</pre>\n";
    
   } else {
    echo "<p>The database '" . $database . "' contains no tables.</p>\n";
    //echo mysql_error();
   
   }
  }
 }
echo "<a href=".$_SERVER['PHP_SELF'].">Back</a>";
}

 
 
 
 

else { ?>

<h1>MySQL connection test</h1>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>?action=test" id="connection" method="post">
   Hostname:<input name="hostname" type="text" /><br/>
   Username:<input name="username" type="text" /><br/>   
   Password:<input name="password" type="text" /><br/>   
   Database:<input name="database" type="text" /><br/>   
   <input type='hidden' name='submit' />
   <input name="submit" type="submit" value="Submit">
</form>


<?php } ?>

7c) Browse {laravel home}\testdbcon.php
7d) Enter connection details as stated in step 5a.


7e) You should get a success response.



7f) Remember to exclude testdbcon.php from Production Environment.


8) Database configuration has been set and tested. We are ready to start migrating Laravel data to the laraveldb.