Tag Archives: mysql

Creating your PHP Onboarding for Windows machines

There are many many ways to have a full LAMP stack on a Windows machine and use it for PHP development like WAMP, for example. This is ok if you are developing just for fun, but in corporate level an on-boarding environment is much preferred. Having a pre-defined server image with all that is necessary to almost replicate your production server on a developer machine helps on preventing errors and, most importantly, quickly have new developers up-to-speed with the corporation development environment.

Consider that you would need:

  • Apache
  • PHP
  • MySQL
  • Memcache
  • APC
  • some extra libs
  • GIT repository

Creating this under windows can be such a hassle and in cases like this bundled solutions does not work.

To create a full LAMP stack on your windows machine and have it 100% stand-alone I have used Vagrant. On the prior version of Vagrant (V1) you had to use Putty in order to ssh to the VM and that was a painful process, with the newer version (V2) you can use built in Vagrant SSH as long you install an SSH support library like the one from GIT. There is a trick on the GIT installation, you must choose the option with the Linux / Unix commands (it has a red warning message about overwriting windows libs).

Prior installing Vagrant you will need to install Virtual Box. It is a Oracle virtual box machine. Very useful if you are on OSX or Linux and need to run tests on Windows IE9 (sig). This part of the process is very well explained on the Vagrant documentation.

Now comes to Vagrant install. It is fairly easy. Install Vagrant, then open command line and run “vagrant init”. It will create a default vagrant file with most of the basic things defined. Here comes a trick. The default vagrant file tries to download the precise VM.  I have tried to contact Vagrant a couple times on Twitter and with no luck. There is an issue with the download of this box, the server resets the connection prior the download to be finished.

There is a way out of this: VagrantBox.es – Select the box that you best consider that matches your environment, add it on vagrant (vagrant box add) and finally update the vagrant file to use the new added box.

After this, everything is as simple as setting up the environment. Unless you have an specific need to add the GIT repository files under another folder you can set it all under the vagrant folder (same folder where the vagrant file is located).

I could use the GIT command line on windows to clone / commit my repositories, but using the GIT windows tool from GitHub is way too fun 😀

I know that most of PHP developers use either Linux or OSX for development. It is much easier to setup and use, but if you are stuck with Windows, you might as well benefit of this.

Just a note, off course the main environment mentioned here was LAMP, but this same process can be used for Ruby, Phyton and may other hipster languages available =D



I had a networking issue with the current box that I’m using. It seems that it is a common issue with some boxes as mentioned here – https://groups.google.com/forum/?fromgroups=#!topic/vagrant-up/Yeu6UF-GJO8

To fix run this: sudo /etc/init.d/networking restart

If you never installed a LAMP stack before, this article here will give you all the steps to install it correctly: http://www.howtoforge.com/installing-apache2-with-php5-and-mysql-support-on-ubuntu-12.04-lts-lamp


Simple MySQL Heartbeat monitoring

I had an interesting request today: create a script that would return a page header 200 or 500 for a heartbeat monitoring on a MySQL server for a site. The reason behind it was that the server was returning way too many MySQL connection errors during a small period of time for it to considered a simple app error.

This is what I have came for to help with the issue:

$dsn = "mysql:dbname={$dbName};host={$host}";
$user = 'mysql_username';
$pass = 'mysql_user_password';
try {
$dbh = new PDO($dsn, $user, $pass);
$dbh = null;
header("HTTP/1.1 200 OK");
} catch (Exception $e) {
header("HTTP/1.1 500 Internal Server Error");

With this simple code we can monitor the page status and if it returns 500 set an alert to tell us that MySQL server is down.

There are, off course, better ways to do this, but if you don’t have too many resources or is using a shared server, this might be a good solution meanwhile.

My overview on MongoDB

Recently I have done a online MongoDB certification developer course with 10Gen. Prior this certification I haven’t never tried or really used a NoSQL database and after it I can say that I would really think twice if I would only use a relational database on a big project.

There is a big change of concept here, differently from a relational database with MongoDB you are willing to give up on relationship and see data in a object way. Let me see if I can explain this better.

In a relational database you would have a set of tables like:

Users 1 – n Addresses 1 – n AddressTypes

In MongoDB you would have a Json Object that would have all data from the user including (or not) the addresses and it’s types. What you store for a given object is completely up to you and if you really want to keep a relation between objects (split the user from the address object for example) you can, but you would have to do it programatically.

It’s a bit hard of a concept to understand at first glance, another reason why I did really enjoy the online course that 10Gen gave, but once you understand it is only natural that you start reconsidering a lot of the decisions you had made with a relational database. It is not only a case of performance, it can also be a case of how you scope your objects and data on the database in a way that you can not only gain performance but also keep the necessary data in a single object.

Consider a real state agency website for example, A lot of the data that relates to a house can be hold into one single object. It not only could be held but also it can shrink the size of data since not every single house does offer the same standards or assets.

Aside of the concept that MongoDB brings it also brings 5 great features that can bring advantages depending of the project that you are dealing with:

  • Aggregation Framework (or it’s complex queries method) that use a concept similar to the Linux pipeline
  • 2D / 3D Geo-find – instead of doing insane big queries with math in it to find a geo-location, MongoDB already comes with this feature
  • Data connectors. It already have a ton of good connectors that works with some of the big languages (like PHP) and frameworks
  • GRIDFS or Blob Sharding  – splits big chunks of images in several collections making it easier to save and gather the image
  • Server Sharding – It is almost ridiculous how  easy is to setup several servers with voting capabilities to handle your MongoDB data, replication, etc

At the moment I’m not entirely sure if I could re-do a whole project only using MongoDB, but I would, given the time and goals, really consider it. The big reason is that I love the concept of storing an object instead of data parts.

Consider the following, if you need to store a whole session on the database which would be the best way to do it: a simple JSON object. With json_encode and json_decode from PHP, for example, you can easily create an array with the whole data.  If you can consider do this with a relational database, why not use a NoSQL database like MongoDB.

If you still have your doubts about MongoDB or even NoSQL databases I would give a check on the online training and their documentation. You probably will find it really fun to work with it.

Today I can say that I understand why MongoDB is becoming more and more popular by the day.

GeoCityLite SQL Db for CakePHP

Just created a new repo on GitHub with the Maxmind.com GeoCityLite data as MySQL sql dumps focused for CakePHP.

Pretty simple, but very useful.

It needs improvement. The current structure uses almost exactly the same logic as it comes from Maximind, so to bind cities to countries and regions you will need use region_id and country iso code.

The repo is open to public, so fork it, share it and help me improve it 😀


MySQL Tricks

This is a small trick with MySQL that will make your paginate methods and queries faster.

When doing a SELECT use SQL_CALC_FOUND_ROWS on it so after the select is done you will already have the count of the rows even if you are doing a limit.


SELECT SQL_CALC_FOUND_ROWS id, name, created FROM my_table LIMIT 0,10;

This will return the 10 first results from the table, but if you do


It will return the number of rows that it would have been if the prior select was done without the limit.

This small trick can speed up considerably the pagination with PHP and the speed of your queries since you now don’t have to do a SELECT count(id) FROM my_table to know how many records you have.

Using LEFT JOINS and Hellfire the MySQL performance guide

I occasionally play WOW (World of Warcraft) and as a “Lock” I know that I shouldn’t use hellfire in any instance (bg or not), is a waste of energy and give’s no result.

With MySQL LEFT JOINS is no different. Off course there will be cases where you will be required to use a LEFT JOIN (and in most of these, normalizing the tables would work), but if you can avoid it, the performance gain on the queries is considerable. There are options for not using left joins, but here are a couple:

1. INNER JOINS (should be your first and mostly only option)


I’m no MySQL master, but I have received a good article today that give’s a bunch of MySQL optimization tips. Check it out:


This article is dedicated to all my friends and co-workers that play with me on WOW and annoy the hell out me when I use a LEFT JOIN 🙂

Building High Performance and High Traffic PHP Applications with MySQL – Part 2: Best Practices – Slides

Building High Performance and High Traffic PHP Applications with MySQL  – Part 2: Best Practices

Download here: http://event.on24.com/event/33/24/48/rt/1/documents/slidepdf/081611_webinar2final.pdf

When MySQL connections max out can be a problem

This is an issue that you might face and sometimes an expert advice might help. Dealing with the MySQL maxout connections can go from simple to purely hell, so, after some digs I have found out a good article from Jeremy Zawodny that writes about some good hard experience that brought some good learning.

Sharing the knowledge.

Link: http://jeremy.zawodny.com/blog/archives/000173.html

PHP Series – Creating a database structure with MySql

MySQL is one of the most popular open sources databases in the world developed and maintained by Sun Microsystems. It is one of the easier to use databases in the World and most of the servers have it available as a “free” database to use.

If it is the first time that you are reading this article series, then you will need to install MySQL to your development machine. For that you can refer to the 3rd post of the series – Starting with PHP – where it shows detailed information about how to install and what else you will need to use PHP and MySQL.

As the series is almost finished we will create a small database on MySQL using  PHPMyAdmin or the MySQL GUI tools.

In order to keep things simple and direct, we will be creating a database with a small set of tables to control user information and login. After, when working with PHP accessing the database, the goal will be to log a registered user and display a welcome profile page.

Understanding the types of tables. In MySQL you have several table types, or Storage Engines, but usually applications use the MyISAM or the InnoDB Engines. When creating the tables is good to know some few characteristics of these engines because it can make your application faster or slower. For instance the MyISAM has as characteristics table lock level and is fast for reading and inserting; InnoDB in the other hand have row lock level and is fast for updating. This can make the InnoDB more suitable for transactions.

To better understand consider three tables:

  1. A table with the Person information
  2. A table with the Address information
  3. A table with relationship of Person-Address where a person can have several addresses
  1. If you consider this scenario having table 1 and 2 as InnoDB and table 3 as MyISAM can speed up a lot your application.

First let’s start with the database. Create a database called php_series. Make sure to add a user to this database where he can have full control over it.

[note:] As long you have a root user on the MySQL server, you can create a database via PHP scripting by:

$conn = mysql_connect(‘localhost’, ‘yourUser’, ‘yourPassword’);
catch (Exception $e)
echo ‘Unable to connect to the MySQL database – ‘, mysql_error();

$createDb = ‘CREATE DATABASE `mysqlExample1` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;‘;
$query = mysql_query($createDb, $conn);
catch(Exception $e)
echo ‘Unable to create the database. – ‘, mysql_error();
$sdb = mysql_select_db(‘mysqlExample1’, $conn);

The next step is to create the user table and before we really do some work with PHP / MySQL scripting, create the tables using your favorite DB Management Tool (phpMyAdmin, MySQLWorkbench, etc).

The user table will have the following fields:

  • userId – interger – autoincrement – unique – key
  • personId – interger – foreign key
  • datetime – datetime
  • username – string
  • password – string (yeah, string, but it will be encrypted, no worries)
  • enabled – boolean – default value true

The goal of this table is to store information about a user account that is related to a given person. This is pretty much the table that will control the login / loggout from our application.

On the end this is how your table should look like:

— —————————————————–
— Table `mydb`.`userAccount`
— —————————————————–
CREATE  TABLE IF NOT EXISTS `mydb`.`userAccount` (
`userId` INT NOT NULL ,
`username` VARCHAR(45) NOT NULL ,
`password` VARCHAR(255) NOT NULL ,
`enabled` TINYINT(1) NULL ,
`created` DATETIME NULL ,
PRIMARY KEY (`userId`) ,
INDEX `personId` () ,
REFERENCES `mydb`.`person` ()

On the table above we have inserted a Foreign key to relate the  user information to the person information. The person information is a simple table where it will store the first name, last name, gender, date of birth, etc. This is a simple script for an example of that table:

— —————————————————–
— Table `mydb`.`person`
— —————————————————–
CREATE  TABLE IF NOT EXISTS `mydb`.`person` (
`personId` INT NOT NULL ,
`firstName` VARCHAR(100) NOT NULL ,
`lastName` VARCHAR(100) NOT NULL ,
`gender` CHAR(2) NULL ,
`created` DATETIME NULL ,
PRIMARY KEY (`personId`) )

Not big of a database, but it’s the minimal necessary to create a login page and a profile page. We will need some much needed users to be using as example on the next part of the series, but we can’t create a password without encryption, so, using the mysql functions from PhP, let’s add one person and user on the database that we can use on the next part of the series.


$dbCon = mysql_connect(‘localhost’, ‘yourUser’, ‘yourPassword’) or die (‘Unable to connect to the database server: ‘ . mysql_error());
$sDB = mysql_select_db(‘mydb’, $dbCon) or die (‘Unable to connect to the database: ‘ . mysql_error());
// first insert the person:
$insertQuery = ‘INSERT INTO `person` (`firstName`, `lastName`, `dob`) VALUES (`Nine`, `Roman`, `1977-01-01 00:00:00`)’;
$query = mysql_query($insertQuery, $dbCon);

// now insert the user:
$password = md5(‘nine’);

$insertQuery = ‘INSERT INTO `userAccount` (`username`, `password`, `personId`) VALUES (`nine09`, `’. $password .’`, 1)’;
$query = mysql_query($insertQuery, $dbCon);

With this small script you have inserted the person and user information for our “Nine” person. Therefore there are some small items that need some focus:

  1. Since the fields are auto-increment there is not need to add them on the list of fields to be added, MySQL will automatically add those fields for you
  2. On the second query, note that the personId is already set to 1. Since there is only one person added, we can hard-code that value, but when it’s not the case it’s necessary to get the last_inserted_id to correctly associate the user account with the person account.
  3. Even knowing that when you are adding all fields there is no need to list the fields names that will be inserted, therefore this really does helps on speeding the insertion of the query.

The objective of this post was never to teach the full understanding of a MySQL database and for that a larger application with more Storage Engines and more complexity is necessary, therefore is strongly suggested that you check the MySQL Developer Zone witch has a lot of documentation and information about the MySQL servers.

Have fun.

PHP Basics Series – Starting with PHP

Hi and welcome to the second article of the PHP Basics Series. The first article – PHP Basics series – coding – was about coding best practices, standards and methods. A small overview on what you need to know to be a good coder.

In this second article we are going to talk about the very beginning of PHP, how it works, how to start coding on it and a bit of history.

PHP stands for Hypertext Preprocessor and it was created based on the C, Java and Perl (used until today on extra modules) languages. With many authors, editors, contributors, etc, is hard to name one as it’s creators, therefore you can point Rasmus Ledorf (who wrote the original gateway binaries) and Andi Gutmans and Zeef Suraski (both responsible for witting the parser that formed PHP3) as the original creators of PHP3.

If we had to create a in-line structure to show how it works, it would be something like this:

The client (your browser) requests a page to the server. The server reads the page and see if there are any PHP script and it will parse that script generating the correct result that can be embed to the HTML / XML / Javascript (JS) / etc.

Off course there is much more when we are talking about the way that PHP works, and it’s history, but I will focus more on the coding techniques.

Before we create our first code and I promise that it will not be “Hello World“, you will need to have PHP installed on your development machine or in a server where you can store your PHP files to be executed.

If you are on a Windows machine you can either install PHP manually adding it to IIS server or you can install a PHP bundle that will install for you the Apache server, PHP engine and MySQL (if you desire to, otherwise you can use PHP native SQLite).

Truly I’m not the best indicated to show how to install PHP on a IIS server, but there is a very good step by step – Vista – on Bills IIS Blog: How to install PHP on IIS.

Installing the bundle, for a development machine, at this point, is the most recommended. There is a great bundle, AppServ, which is pretty easy to install and configure. It will come with all of the most usual libraries that you need and you can also choose to install some extra ones.

In other hand, if you are on a Linux machine (Ubuntu, Xubuntu – love it, Suse, etc) most likely you already have PHP, Apache and MySQL installed. Therefore you can check by using the Synaptic Package Installer, and if is not there, install it. Just make sure that you have root permissions.

Now that you have PHP installed, let’s create our first code.

Note: If you don’t have an development editor, you can use Eclipse PDT. It’s a great editor and it’s free. Very similar to the Zend Studio. If you have any other editor that you like, such as Dreamweaver, Notepad, Edit+, etc, you can use it as well.

The first thing you need to know is how make the server to understand that it will be dealing with a PHP code. For that you will be using the PHP open / close tags. There are a set of 4 open / close tags for PHP, but we will focus on only one.

PHP holds as it’s open / close tags the following:

  1. <?php ?> – Full tag
  2. <? ?> – Short tags. As one of it’s variations you will have <?= that is the same as <? echo
  3. <% – ASP tags. More as a history point, but really, not useful at all.
  4. <script language=”php”></script> – besides the first one, the only one that you can trust to work.

While coding we could be using all of the 4 sets of PHP’s open / close tags, but, for keeping a standard and full compatibility on any PHP installation, we will always use the first set. The second set have some amazing features applied to it, but on the latest versions of PHP you need to enable it on the INI configuration and, if you are dealing with embending your code on a XML file you can have some parsing errors.

As I promised before, we are not going to write a “Hello World” application and we will code 2 types of application today. A standalone and a embed one. There is no real definition as standalone or embed types, but it will be easier, for now, to identify our files as standalone or embed. The standalone is a file that is purely PHP, no other code besides PHP. The embed is a HTML, XML, or any other type of file that have PHP code inside it.

Let’s take a look on a standalone file first: standalone.php (note the .php extension, whithout it, the server can’t recongnize that there is a PHP code inside it and it would only write the code on the screen as text).

$string    = ‘ This is a string and the number is: ‘;
$number = 7;
echo $string;
echo $number;

If you call the file from your browser (http://localhost/standalone.php) you will see the following result:

This is a string and the number is 7

As you may noticed by now, the close tag, ?>, for the above script was omitted. When you are dealing with only PHP code on a file, you don’t need to include the closing tag, the PHP engine will do that for you and it also is a simple performance and standard recomendation from PHP.

Among other uses from the standalone file, you can call it from the command prompt / command line: php /path/to/file/standalone.php. It’s results will be displayed on the command prompt screen. At first this does not seem too useful, but when getting more advanced, you will see that you can use this with cron jobs to increase your application power.

Let’s take a look now on the embed file, embed.php. Again the extension of the file must be .php otherwise the server will not understand that there is PHP code inside the file and it will not process it.


<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”&gt;
Page Title</title>
<!– other head data such as CSS styling and JS scripts –>


Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.

$string    = ‘ This is a string and the number is: ‘;
$number = 7;
echo $string;
echo $number;

This is a good example of a PHP script that is embed into a XHTML file. When you call the file on your browser you will have the following result:

Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.

This is a string and the number is 7

As you have notice, in the embed file the php close tag, ?>, is on place. Without it, the PHP engine would throw an error on the screen not letting the code to be parsed and the page to be displayed.

This is our first PHP application. For now on things will be increasing and we will focus a bit more on structures and tools that you can use while programming PHP.

For now on every time that we work with a PHP file that will be displayed on a browser or used on a cron job or for any other need, we will reference it as a application because that is what truly is.

See you on the next article, have fun.

%d bloggers like this: