Laravel 4 and database seeding

I’ve been playing around with Laravel 4 for a few days now and have been very impressed. Once I figured outhow to install it, so far the learning curve from Codeigniter seems to be small. Right now I’m taking an existing project written with Codeigniter and creating a Laravel version for it. I got the configurations set up, now to move on to the database. The migration feature is great. I’ve been able to create my database and rollback with ease. The next step is to take data from an existing database and import it over. I could dump it into an SQL file and be done with it. But the source project is live and data changes. I’d like to use migration to get the latest data off the source.

The solution is to use Laravel’s seeding. Migration and seeding work with each other. Unfortunately, the sample code and demo I’ve seen so far uses arrays to populate (seed) the database. It’s a good sign though because all I have to do is query the existing database and populate an array with the resultset. The problem is how and what the syntax is. After a few hours of researching online and testing, I’ve got the solution.

Let’s say your source database is MySQL and you have a client table with client_id and client_name columns. On your Laravel project you have a clients table with id, client_name, created_at, and updated_at columns. Add a new connection to your /app/config/database.php. If you have multiple MySQL databases, it’s okay, just append 2… so you have a mysql and mysql2 connection. So your mysql array connection holds your Laravel database and mysql2 holds your database connection information where you’ll be getting data from.

Next edit the /app/database/seeds/DatabaseSeeder.php file. Here’s what I have.

<?php
class DatabaseSeeder extends Seeder {

	/**
	 * Run the database seeds.
	 *
	 * @return void
	 */
	public function run()
	{
		Eloquent::unguard();

		$this->call('ClientTableSeeder');
	}

}

class ClientTableSeeder extends Seeder
{

	public function run()
	{
                // truncate the table before inserting imported data
		DB::table('clients')->truncate();

                // use mysql2 connection then query the database, then put resultset in array
		$clients = DB::connection('mysql2')->select('SELECT `client_id`, `client_name` FROM `client`');
		$now = date('Y-m-d H:i:s');

                // loop through the resultset and insert into laravel database
		foreach ($clients as $client)
		{
			Client::create([
				'id'			=> $client->client_id,
				'client_name' 	=> $client->client_name,
			    'created_at' 	=> $now,
			    'updated_at' 	=> $now
			]);
			
		}
	}
}

Installing Phalcon in Ubuntu

Phalcon is a PHP Framework I wanted to play with. I have recently switched one of my PC from Windows 8.1 to a Linux distro – for development and everyday use. Slowly but surely I’m finding alternative software and loving it. I’m only going to use Windows for gaming. For now, I will be using Ubuntu 14.04 LTS. The version of Phalcon for this post is 1.3.1.

There is a little guide that can be found on their website that helps install Phalcon, but just like Laravel’s instructions, it’s somewhat incomplete. Which is why, for Laravel, I had to post an add-on guide to their guide. If you follow that guide, you will first run into problems in the compilation section where they want you to run the install command. It will complain about pcre. You can read more about the error athttp://stackoverflow.com/questions/22555561/error-building-fatal-error-pcre-h-no-such-file-or-directory. The solution is also there.

Once you install libpcre3-dev, you can continue with the install. The next trouble you may run into is trying to load the phalcon extension. Again, the guide is a little vague with its instructions. The solution to this is found athttp://codingtip.blogspot.com/2014/03/install-phalcon-phalcon-developer-tool.html.

	Create phalcon.ini file in /etc/php5/apache2/conf.d/phalcon.ini add : extension=phalcon.so in this file.

Hope this helps you. I get lost easily when I play with new things. If loaded correctly, you should see this section in your php.ini.

phpinfo

WIMP on Windows 7 – Windows, IIS, MySQL and PHP

In the past it was always a bit challenging to get WIMP installed, at least for me it was. I ended up using installers for PHP and even then which version would I use, fast cgi or that other one (can’t seem to remember the name, it’s been a while). Recently, I had to create an environment that uses WIMP. I had to take old data from MSSQL 2008 and convert it over to MySQL. I tried to use MySQL Workbench’s migration tool but it failed on 5 of the tables. So my plan is to use PHP and somehow import the data over. Well I would need to use SQLSRV driver for PHP5 to connect to MSSQL 2008. Enough of the back story, here’s what I did.

First off, I am using Windows 7 Professional 64bit. You will need to enable IIS in the Control Panel by turning it on in the Windows features. Here’s what mine looks like.

windows_features

You don’t need all of the checked features but you will need CGI under Application Development Features – this is how PHP will run within IIS. Leave the others default. Once installed, there may be new Windows updates related to what you just enabled. It’s a good idea to patch them up. The version of IIS I’m running during time of writing is 7.5. Screenshots and feature location may differ from your version.

Now download PHP5 the version you wish to use from http://windows.php.net/download. What I downloaded and used was 5.4.27. I needed a version from 5.4 because of the SQLSVR I mentioned earlier. I haven’t tested to see if the driver works on PHP5.5+. Also, you want to download the NTS (non-thread safe) version. In my case, it’s VC9 NTS for PHP5.4.27. Unzip the file and put it in C:PHP5. You can put it anywhere but I just wanted it more accessible.

Inside this folder, look for a file called php.ini-development. Copy and rename the file to php.ini. I chose development because my environment is for development only. There is a production version with slightly different settings.

Here are the entries that need modification within the php.ini files.

  • extension_dir = “ext” (enable this if you’re going to use extensions)
  • fastcgi.impersonate = 1 (enable this)
  • cgi.fix_pathinfo=1 (enable this)
  • cgi.force_redirect = 1 (enable this)

Save the file and close it.

Now we need to let IIS know how to serve PHP pages. Open up IIS Manager. Highlight the entire Server to apply to all websites hosted or specific ones – in this case, highlight Default Web Site. Next, double-click on Handler Mappings and Add Module Mappings. Here’s how mine looks.

module_mapping

Restart IIS. Create a test file using phpinfo(). You should see something like this if all is in working order.

phpinfo

To install MySQL, just download an installer from http://dev.mysql.com/downloads/windows/installer. The great thing about the installer is that it comes with other goodies such as MySQL Workbench and connectors such as ODBC (which I needed to get the migration tool from Workbench to connect to MSSQL server). If you are going to use MySQL, don’t forget to enable the extension(s) in the php.ini file. You can find more detailed instructions from http://www.iis.net/learn/application-frameworks/install-and-configure-php-applications-on-iis/using-fastcgi-to-host-php-applications-on-iis.

Installing PHP driver for MongoDB in WAMP

I’ve been getting more curious about MongoDB. The problem is I’ve been struggling trying to create a development environment. I’ve tried OSX, Ubuntu, and Windows. I always get stuck at a step. I think I found the right recipe.

First, I’m running Windows 7 Professional 64 bit virtually. You don’t have to run Windows virtuall. I’m mostly on my Mac and I prefer to use virtual environments for development so it’s okay if I screw around with things. Then I installed Wamp. The key here is to use the 32bit version of Wamp. I’ve tried the 64bit version of Wamp and could not get it to work. Then, you need to download the latest driver from the MongoDB website. You will get a NTS (non-thread safe) and TS (thread safe) driver. The folders are labeled as follows “mongo-[version]-php5.3vc9” and “mongo-[version]-php5.3vc9ts. Remember “ts” = “thread safe”. I used the non-thread safe DLL file. Take that DLL file and copy and paste it in the “ext” folder within the PHP installation directory of Wamp. If you installed using default settings it should be “c:wampbinphpphp5.3.xext”. Open the php.ini file and add the php_mongo.dll extension. Restart Apache and it should show up in your phpinfo() page.

I will be using MongoHQ to try things out. I’m not sure why I couldn’t get it to work in a 64bit web server environment. Mongo looks very cool and if it can help me decrease development time, I’m going to build future web apps with it.

MSSQL Driver for PHP5.3

Before using PHP5.3, I never had to worry about MSSQL support. PHP included a dll file to use with MSSQL Server 2000 and up. This is no longer true if you decide to upgrade to PHP5.3.x. For months I looked for ways to connect to our MSSQL Server using PHP5.3.x. I finally found the driver and it works. It’s called SQLSRV and you can download it from http://sqlsrvphp.codeplex.com. It’s easy to install, just read the CHM file.

  • Run the exe to unpack it
  • Rename the folder to Microsoft SQL Server Driver for PHP (optional)
  • Move this to your %Program Files% directory (optional)
  • Inside that directory you will find several dll files. It supports both NTS (Non Thread Safe) and TS (Thread Safe). You also need to choose between VC6 or VC9 compiler. This will determine which file will be used.
  • Copy that file to %Installation Directory%PHPext (assuming you are using the ext directory for your extensions.
  • Edit your php.ini. In the extensions section add the following extension=php_sqlsrv_53_ts_vc6.dll – or whatever file you copied to your ext directory.
  • Restart your web server and that should be it.

Here’s a quick sample code to get you started.

<?php

$servername = "SQLSERVER_NAME_OR_IP";
$uid = "db_username";
$pwd = "db_passwd";
$connectionInfo = array (
"UID"=>$uid,
"PWD"=>$pwd,
"Database"=>"database_name"
);
$conn = sqlsrv_connect($serverName, $connectionInfo);

if ($conn === false) {
echo "ERROR: DB Connection";
die(print_r(sqlsrv_errors(), true));

}
$stmt = sqlsrv_query($conn, "SELECT * FROM table");

if ($stmt) {
while ($row = sqlsrv_fetch_array($stmt)) {
echo $row[0].'<br />';
}
}

?>

That should do it. I have only tested this on Windows 7 Professional (32bit), MSSQL 2005 (32bit) Standard on a remote Windows 2008 Server Standard R2, PHP5.3.3, Apache2.2.15, and driver version 1.1 (version 2 is now available) . Make sure your MSSQL server can accept remote connections if it is on a different server than your web server. The above instructions shouldn’t be too different on other versions.

If you have a phpinfo() page, you will see the image below.

sqlsrv

Hope this helps you get started. Happy coding.

Connect to MSSQL 2005 using PHP

Here’s a couple of quick tips to connecting to a MSSQL 2005 server using PHP on a Windows webserver. You will need to enable the php_mssql.dll in your php.ini file.

Here’s the code to connect to the server.

<?php

$conn = mssql_connect('my_server', 'db_username', 'db_password');
if (!$conn) { die('ERROR: Unable to connect to the database.'); }
mssql_select_db('database_name');

?>

Here is a helpful function I found that will help escape strings. I found this at http://stackoverflow.com/questions/574805/how-to-escape-strings-in-mssql-using-php.

<?php

function ms_escape_string($data) {
//if ( !isset($data) or empty($data) ) return ";
if ( !isset($data) or empty($data) ) return """;         // modified to handle empty $data
if ( is_numeric($data) ) return $data;

$non_displayables = array(
  '/%0[0-8bcef]/', // url encoded 00-08, 11, 12, 14, 15
  '/%1[0-9a-f]/', // url encoded 16-31
  '/[x00-x08]/', // 00-08
  '/x0b/', // 11
  '/x0c/', // 12
  '/[x0e-x1f]/' // 14-31
);
foreach ( $non_displayables as $regex ) {
  $data = preg_replace( $regex, ", $data );
  $data = str_replace("'", """, $data );
  //return $data;
  return "'".$data."'";        // modified to handle empty $data
}

?>

Here is a sample query to the database.

<?php

$id = ms_escape_string($_GET['id']);    // escape variable
$sql = 'SELECT id FROM table_name WHERE id='.$id.";
$rs = mssql_query($sql);

if (!$rs)
{
  echo 'ERROR: Unable to get records. '.mssql_get_last_message();    // equivalent to mysql_error()
}else{
  while ($row = mssql_fetch_assoc($rs))
  {
    echo 'The id = '.$row['id'].'<br />';
  }
}

?>

Another thing I learned from the article is the equivalent of “mysql_insert_id()”. It is “SELECT @@IDENTITY”. To handle dates and make them friendly to PHP use CONVERT(VARCHAR, [date], 20). This will display it as if you are using date(‘Y-m-d H:i:s) – 24 hour format. So an example would be…

SELECT CONVERT(VARCHAR, getdate(), 20) AS now

I tried to use PDO but was having a hard time getting it to work. I kept getting errors that had something to do with functions. I searched for solutions and tried them but none worked for me. Also, the rowCount() will always return less than or equal to 0.

I had trouble on both sides PHP and Microsoft. This seems to be the best solution that works for me. Hope it helps others.

Installing PEAR on Godaddy shared accounts

I recently found out how to install PEAR on a Godaddy shared hosting account.

  1. Visit http://pear.php.net/go-pear. Save the text displayed onto your desktop and name it go-pear.php. Upload this to your server. If you’re hosting multiple domains on the account, it’s best that you should upload this file in your root folder. Run this file on your browser and follow the on-screen instructions.
  2. After completing the installation, it is time to edit your php.ini file. If you are running PHP4, there should be a file in the root directory called php.ini. If you are running PHP5, there should be a file in the root directory called php5.ini. If not, create one and add the following: include_path = “.:/usr/local/php5/lib/php:/home/content/s/a/m/sample/html/PEAR”. Keep in mind that /s/a/m/sample is just an example. Doing this will ensure that every page, you create, will look in the PEAR directory so that you it will use the installed packages.
  3. This installation will include Pear_Frontend_Web which is the web-based admin interface. It may have created an index.php file in the directory where PEAR is installed (root). If not you can get a copy from PEAR/docs/PEAR_Frontend_Web/docs/index.php.txt. I would suggest creating a folder called pear_admin in the root directory and storing this file there. If you didn’t install PEAR in the root directory, you may need to edit this file accordingly.
  4. You will need to create a .htaccess and .htpasswd file in the same directory as the index.php file. An example of how the .htaccess file should look as follows:
    AuthUserFile /home/content/s/a/m/sample/html/pear_admin/.htpasswd
    AuthType Basic
    AuthName “Web-based PEAR Frontend”
    Require valid-user

    An example of how the .htpasswd file should look as follows:

    admin:cGyUX9QugYMgE

    This will create “admin” as the user name and “password” as the password. You can generate your own by going to this link – http://www.htaccesstools.com/htpasswd-generator/

    Be aware that files beginning with a dot are invisible. You may have to edit your settings on the FTP app you’re using so that you can see them.

Once the files are created and saved, you can now go to http://your-domain.com/pear_admin/index.php. It will ask for the user name and password. Once you are logged in, you can now manage Pear via web browser. That’s it! Now you can run PEAR on a shared account from Godaddy. One less complaint 🙂