Get MySql and PHP to Work Together in Raspberry Pi

lampSetting Up

I’ve installed PHP and Mysql on dozens of different systems ranging from a MS PC running Windows, to a Linux box to a Mac with OS X. However, I’ve never quite run into the problems I did with Raspberry Pi. It’s not that it is more difficult—in fact it is quite simple—I just got off on the wrong foot. In this short post, I want to provide a clear and simple set of steps for installing LAMP. The one caveat is that you should install everything at once. The installation includes Apache Server, PHP and MySql. The whole kit and caboodle should be installed together on top of a Debian Linux system. (If you have most other Linux systems, everything discussed generally applies to your system. If you have a Mac or Windows PC, when you connect up with the MySql administration module, you will find the MySql commands pretty much the same as those discussed here; so don’t feel left out.)

L.A.M.P.: Presto! You’re a Web Server

LAMP is an acronym for

  • Linux (OS)
  • Apache (Web server)
  • MySql (Database server)
  • PHP (Server-side Language)

By placing LAMP on your Raspberry Pi, you can do far more than without it. Not only do you get a Web server, but you get a database system and a programming language. In a previous post on this blog, you have seen how to set up the Raspberry Pi with PHP and work it in your file system. In this post I hope to show how to add MySql and use it as a database with PHP on a Raspi.

This process is for Raspberry Pi users who have the Debian distribution already installed. (That’s the ‘L’ in LAMP).

Assuming that you have not yet installed Apache server or PHP or MySql enter the following code in the Root Terminal:

sudo apt-get update
sudo apt-get install apache2 php5 php5-mysql mysql-server

Wait patiently until you are asked for a password for your ‘root’ user. Enter a password you can remember (and write it down somewhere you won’t lose it).

Testing MySql

To see is your MySql is working correctly, you will need to log in from your LXTerminal. So open your LXTerminal and enter:

mysql -u root -p

You should see the following screen after entering your password:

Figure 1: Log into MySql

Figure 1: Log into MySql

If you get an error try again and make sure you have entered your password correctly.

Once you’ve successfully logged into MySql, the cursor and prompt change to:

mysql>

When you want to log out of MySql just type:

mysql>quit

Viewing and Creating Databases

The first MySql command to try out is:

mysql>SHOW DATABASES;

Placing MySql commands in All Caps helps to differentiate them from PHP commands. You will see something like the table in Figure 2:

Figure 2: Showing current databases

Figure 2: Showing current databases

One thing, you will not see is the database named billz. That’s because I added it after the initial creation of the MySql on Raspi. One of the default databases is ‘test.’ However, you probably want to have at least one database beyond the default ones. While still in the mysql mode, type in,

mysql>CREATE DATABASE develop;

In this case the name of the new database is “develop.”

One important detail is getting the MySql version right. Documentation on MySql is organized along version lines. So if you have MySql 5.5 and the manual is MySql 5.1, you need to get the right one. Enter the following for both the version and current date:

mysql>SELECT VERSION(), CURRENT_DATE;

Figure 3 shows the version and the current date in a Raspberry Pi window:

Figure 3: MySql version and Date

Figure 3: MySql version and Date

As you can see in Figure 3, the MySql version used in my Raspberry Pi is 5.5.31-0+wheezy1. In the MySql documentation, I will click on the MySQL 5.5 Manual in the Documentation Library to get the right version. Usually, there’s not a lot of differences in the basic commands over the versions, but you might as well get the right one for your system.

Creating Users in MySql and Employing them with PHP

The next step is creating users (in addition to “root”) and using them in a PHP program to connect PHP with MySql. First create a new user with the command:

mysql>CREATE USER 'willie'@'localhost' IDENTIFIED BY 'begoode';

Next grant privileges to the new user:

mysql>GRANT ALL PRIVILEGES ON *.* TO 'willie'@'localhost'
-> WITH GRANT OPTION;

Figure 4 shows this process in a Raspberry Pi LXTerminal:

Figure 4: Creating new user and granting privileges

Figure 4: Creating new user and granting privileges

At this point you have enough material that you can do the rest using PHP. However, first there’s one more MySql command to use that shows MySql tables. In the last part of this post, you’ll see how to create a table using PHP, but for now type in the following except rather than “bills” choose a database of your own:(The notice that you’ve changed databases appears automatically.)

mysql>USE billz;
Database changed
mysql>SHOW TABLES;

On my Raspberry Pi I will see the table I created using PHP as shown in Figure 5:

Figure 5: Displaying tables in billz database

Figure 5: Displaying tables in billz database

The PHP-MySql Connection

At this point you want to make sure your Apache server and PHP are working correctly and you have a workable environment for your Raspberry Pi computer (Actually, it’s now a computer, server and database system!). So check out Easy Writer: Setup for Raspberry Pi on this blog. Test the PHP sample programs on your system.

Once everything is set up on your system, take a look at Static Connection Objects: Classes, Properties and Methods. You can download the object oriented connection tools here (click the Download button.)
Download

Once you place the downloaded files in the PHP folder on your Raspi, you will need to configure only one file, IConnectInfo.php. The following listing shows a typical set up for Raspi:

<?php
//Filename: IConnectInfo.php
interface IConnectInfo
{
	const HOST ="localhost";
	const UNAME ="root";
	const PW ="yourPassword";
	const DBNAME = "test";
 
	public static function doConnect();
}
?>

The username “root” is built-in and so is the “test” database. The host on your Raspi will be “localhost” by default as well. So the only thing you need to remember is the password you used when you initially loaded the LAMP package into your Raspi. Be sure that the three files that you downloaded for the connection test (they were unzipped in a folder named “NoSurprises”) are in the PHP folder in the path /var/www/php in your Raspi File Manager. Then from your browser run,

http://localhost/php/NoSurprises/ConnectTest.php

If everything is correctly set up (and your remembered your password!), you’ll see:

Successful connection to MySQL:
You’re hooked up, Ace!

From this point on, you should be able to use your Raspberry Pi as a Web server and database and write PHP programs that use the MySQL database. You can get started writing database programs in PHP with the basics on this blog found here.

Share

Creative Commons License
Get MySql and PHP to Work Together in Raspberry Pi by William Sanders, unless otherwise expressly stated, is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.

2 Responses to “Get MySql and PHP to Work Together in Raspberry Pi”


  • Hey Bill,

    I know this series is a bit dated, but let me ask a question on performance: Can you point out some example test programs that will measure (in some standard manner) the performance throughput of the LAMP stack on a pi?

    Thanks for any insights.
    Jason

  • Hi Jason,

    That’s a great question, and the closest answer I could find is at the Raspi site with responses by engineers http://www.raspberrypi.org/forums/viewtopic.php?f=36&t=60691

    As you can probably tell, I’m pretty much of a software guy and a programmer more than an IT Tech or engineer. If I can find the hooks to build a PHP program to measure LAMP throughput on a Raspi (or any other system) putting together such a program would be an ideal Raspi project.

    I’d be very interested in what you discover as well.

    Kindest regards,
    Bill

Leave a Reply