MySQL and FuelPHP

Database Configuration for FuelPHP


Fuelphp can be configured to use MySQL. For any framework to successfully connect to your MySQL server, We have to provide the database host as well as the credentials to it. Fuel follows the Development/Production -> App ->Core hierarchy to load database configurations. the files for these are provided in the following paths :

Fuel first checks if the current application is in the development or production phase. This can easily be toggled in the fuel config file. Depending on which phase it is in, It first checks either the development or production db.php file. By default these are set to localhost. You will have to set these to your credentials. If you want one rule to apply to all versions of your application, you can set development and production to return an empty array and set your configurations in the App specific file. If you want all your apps to have to same config, you can set your configurations in the Core db.php file. Please note that for this to work, your development, production and all app db.php files have to return an empty array else that would override configurations set in the Core db.php file. Sample code snippits are as follows :

Development DB file (/fuel/app/config/development/db.php)
			
				return array(
					'default' => array(
						'connection'  => array(
							'dsn'        => 'mysql:host=faure.cs.colostate.edu;dbname=aaronper',
							'username'   => 'aaronper',
							'password'   => 'p@ssw0rd',
						),
					),
				);
			
		
App DB file (/fuel/app/config/db.php)
			

				return array(
					'development' => array(
						'type'           => 'mysqli',
						'connection'     => array(
							'hostname'       => 'faure.cs.colostate.edu',
							'port'           => '3306',
							'database'       => 'aaronper',
							'username'       => 'aaronper',
							'password'       => 'p@ssw0rd',
							'persistent'     => false,
							'compress'       => false,
						),
						'identifier'     => '`',
						'table_prefix'   => '',
						'charset'        => 'utf8',
						'enable_cache'   => true,
						'profiling'      => false,
						'readonly'       => false,
					),
				);
			
		
Core DB file (/fuel/core/config/db.php)
			
				/**
				 * Base MySQLi config
				 */	
					'default' => array(
						'type'           => 'mysqli',
						'connection'     => array(
							'hostname'       => 'faure.cs.colostate.edu',
							'port'           => '3306',
							'database'       => 'aaronper',
							'username'       => 'aaronper',
							'password'       => 'p@ssword',
							'persistent'     => false,
							'compress'       => false,
						),
						'identifier'     => '`',
						'table_prefix'   => '',
						'charset'        => 'utf8',
						'enable_cache'   => true,
						'profiling'      => false,
						'readonly'       => false,
					),
			
		

Querying MySQL using FuelPHP


Now that we have set up fuel to communicate with our database, it's now time to go ahead and interact with our database using Fuel. We will create a simple example where we will first create one table in our database and then go ahead and read and write data from and to this database.

Let us first create a table in MySQl and enter some data into this table. Connect to MySQL from the command line using "mysql -u ename -D ename -h faure -p" and run the following commands on it. This should create a database called "courses" and insert some data into this table.

Enter the following in MySQl CMD
			
				use [DBname];
				create table courses (courseID int PRIMARY KEY auto_increment, courseName varchar(256), courseNumber varchar(256), numOfAssignments int);
				insert into courses values (123,'Web Development','CT-310',8),(124,'Introduction to Machine Learning','CS-445',8),(125,'Image Computation','CS-510',10),(126,'Big Data','CS-535',3),(127,'Introduction to Artificial Intelligence','CS-440',6);
			
		

Now that we have our table set up, let us go ahead and define a simple model with two methods. One will read from the database and the other will write to the database. We will also add the following files to our path.

The flow for this simple use case would be as follows. User lands on the index page. This index page calls the getCourses() method from the model and passes information to the view. The view then loads all the courses and displays them. The add action triggers a blank view to load. The user can type out new course details on the provided form. On submission, these details will be passed to the saveCourse() method in the model that saves the newly entered course. The Index page is then called which loads all courses including the newly added course.

Create a model file named courses.php with the following content
			
				<?php 

				namespace Model;

				class Courses {
					
					//this method will return the courses from the database 
					public static function getCourses()
					{	
						//Note: we have to use global scope ('\') while calling DB object
						$query = \DB::select('*')->from('courses')->execute();
						return $query;
					}

					//this method saves a given course
					public static function saveCourse($courseName, $courseNumber, $assignments)
					{
						//Note: we have to use global scope ('\') while calling DB object
						$query = \DB::insert('courses');

						// Set the columns and values
						$query->set(array(
							'courseName' => $courseName,
							'courseNumber' => $courseNumber,
							'numOfAssignments' => $assignments,
						));
						
						$query->execute();
					}

				}
			
		
Create a controller file named course.php with the following content
			
				<?php

				use Model\Courses;

				class Controller_Course extends Controller
				{
					/**
					 * Shows a list of all Courses
					 */
					public function action_index()
					{
						//load the layout
						$layout = View::forge('courses/layoutfull');
						
						//load the view
						$content = View::forge('courses/index');
						
						//get all courses
						$courses = Courses::getCourses();
						
						$CoursesString;
						
						//this loop converts all courses to a single string and stores them in $CoursesString
						foreach($courses as $key=>$course)
						{
							$CoursesString[$key] = $course['courseID']." ".$course['courseName']." ".$course['courseNumber']." ".$course['numOfAssignments'];
						}
						
						//set the courses to the view for printing
						$content->set_safe('demos', $CoursesString);

						//forge inner view
						$layout->content = Response::forge($content);

						return $layout;
					}

					public function get_add()
					{
						//load the layout
						$layout = View::forge('courses/layoutfull');

						//load the view
						$content = View::forge('courses/add');

						//forge inner view
						$layout->content = Response::forge($content);

						return $layout;
					}

					public function post_add()
					{
						//extract course name, number and assignments from the input parameters
						$name = $_POST['name'];
						$number = $_POST['number'];
						$assignments = $_POST['assignments'];
						
						//call the static method that saves a course
						Courses::saveCourse($name, $number, $assignments);
						
						//reload the index page with the newly saved view
						Response::redirect('index.php/course');
					}

				}

			
		
Create a view folder named 'courses' and inside that, a file named layoutfull.php with the following content
			
			<!DOCTYPE html>
			<html>
				<head>
					<meta charset="utf-8">
					<title>CT310 Examples</title>
					<?php echo Asset::css('main.css'); ?>
				</head>
				<body>
					<div id="head">
						<h1>CT310 Examples</h1>
					</div>
					<div id="mainContent">
						<?=$content; ?>
					</div>
					<div id="footer">
						Part of a <a href="https://www.cs.colostate.edu/~ct310/">CT310</a> Course Project
					</div>
				</body>
			</html>

			
		
Create a view file inside 'courses' named index.php with the following content
			
								<h2>
					Index of Demos
					<span class="floatRight">
						<a href="<?=Uri::create('index.php/course/add'); ?>">+ Add Demo</a>
					</span>
					<span class="floatClear"></span>
				</h2>

				<h2>CT-310 Example</h2>

				<div class="h2Content">
					<?php foreach($demos as $demo): ?>
							<?=$demo; ?><br>
					<?php endforeach; ?>
				</div>

			
		
Create a view file inside 'courses' named add.php with the following content
			
				<h2>
					<a href="<?=Uri::create('index.php/course/index'); ?>">Courses</a>
					» Add
				</h2>
				<div class="h2Content">
					Add Course
					<form method="post">
						<label for="id">Course Name</label>
						<input type="text" name="name" />
						<br />
						<label for="id">Course Number</label>
						<input type="text" name="number" />
						<br />
						<label for="id">Number of assignments</label>
						<input type="text" name="assignments" />
						<br />
						<input type="submit" value="Add Course" />
					</form>
				</div>

			
		

ORM and FuelPHP


Firstly, to enable ORM in Fuelphp, we have to modify the fuel config file.

Modify /fuel/app/config/config.php and uncomment the following lines
			
				'always_load'  => array(
					'packages'  => array(
						'orm',
					),
				),

			
		

Let us now create an ORM version of our previous model

Create a new model file named ormcourses.php with the following content
			
				<?php 

				namespace Model;

				class Ormcourses extends \Orm\Model
				{
					protected static $_properties = array('courseID', 'courseName', 'courseNumber', 'numOfAssignments');
					protected static $_table_name = 'courses';
					protected static $_primary_key = array('courseID');
				}

			
		

Let us now create new version of our controller that uses the newly created ORM object

Create a new controller file named orm.php with the following content
			
				<?php

				use Model\Ormcourses;

				class Controller_Orm extends Controller
				{
					/**
					 * Shows a list of all Courses
					 */
					public function action_index()
					{
						//load the layout
						$layout = View::forge('ormcourses/layoutfull');
						
						//load the view
						$content = View::forge('ormcourses/index');
						
						//get all courses using the ORM object
						$courses = Ormcourses::find('all');
						
						$CoursesString;
						
						//this loop converts all courses to a single string and stores them in $CoursesString
						foreach($courses as $key=>$course)
						{
							$CoursesString[$key] = $course['courseID']." ".$course['courseName']." ".$course['courseNumber']." ".$course['numOfAssignments'];
						}
						
						//set the courses to the view for printing
						$content->set_safe('demos', $CoursesString);

						//forge inner view
						$layout->content = Response::forge($content);

						return $layout;
					}

					public function get_add()
					{
						//load the layout
						$layout = View::forge('ormcourses/layoutfull');

						//load the view
						$content = View::forge('ormcourses/add');

						//forge inner view
						$layout->content = Response::forge($content);

						return $layout;
					}

					public function post_add()
					{
						//extract course name, number and assignments from the input parameters
						$name = $_POST['name'];
						$number = $_POST['number'];
						$assignments = $_POST['assignments'];
						
						//create a new ORM object and populate it
						$new = new Ormcourses();
						$new->courseName = $name;
						$new->courseNumber = $number;
						$new->numOfAssignments = $assignments;
						
						//save the ORM object
						$new->save();
						
						//reload the index page with the newly saved view
						Response::redirect('index.php/orm');
					}

				}


			
		

Let us also create a new views folder named ormcourses with the following files

layoutfull.php
			
			<!DOCTYPE html>
			<html>
				<head>
					<meta charset="utf-8">
					<title>CT310 Examples</title>
					<?php echo Asset::css('main.css'); ?>
				</head>
				<body>
					<div id="head">
						<h1>CT310 Examples</h1>
					</div>
					<div id="mainContent">
						<?=$content; ?>
					</div>
					<div id="footer">
						Part of a <a href="https://www.cs.colostate.edu/~ct310/">CT310</a> Course Project
					</div>
				</body>
			</html>

			
		
index.php
			
				<h2>
					Index of Courses
					<span class="floatRight">
						<a href="<?=Uri::create('index.php/orm/add'); ?>">+ Add Demo</a>
					</span>
					<span class="floatClear"></span>
				</h2>

				<h2>CT-310 Example</h2>

				<div class="h2Content">
					<?php foreach($demos as $demo): ?>
							<?=$demo; ?><br>
					<?php endforeach; ?>
				</div>

			
		
add.php
			
			<h2>
				<a href="<?=Uri::create('index.php/orm/index'); ?>">Courses</a>
				» Add
			</h2>
			<div class="h2Content">
				Add Course
				<form method="post">
					<label for="id">Course Name</label>
					<input type="text" name="name" />
					<br />
					<label for="id">Course Number</label>
					<input type="text" name="number" />
					<br />
					<label for="id">Number of assignments</label>
					<input type="text" name="assignments" />
					<br />
					<input type="submit" value="Add Course" />
				</form>
			</div>