Archive for the 'MySql Connection' Category

Sandlight CMS VI: Strategy Administrative Module

admin A CMS is only as good as the administrative module, and the one provided here is fully functional based on a simple work flow. You can change the UI and work flow to best fit your own, but you have the flexibility design patterns offer; so making changes to fit your needs (or those of your customers) should not be difficult.

One of the key features of a dynamic Web page is that it can be updated easily, and by using a MySQL table and database, all of the update information can be stored in a single location (table.) Updating the page is simply a matter of updating the data in a record or by adding new records to the table. The administrative module for this CMS has the capacity to update data in an existing record, but the page presented is always the newest record. The CMS has certain drawbacks because you cannot delete records without throwing the system out of whack, but that was done for the purposes of ease of use. (Remember, you’re a programmer and can change whatever you want!)

Set the Table

Setting up the MySQL table involves three files: 1) an interface with the MySQL connection information; 2. a connection class; and a table-creation class. The following three classes: (Use your own MySQL connection information in the IConnectInfo interface.)

< ?php
//Filename: IConnectInfo.php
interface IConnectInfo
{
	const HOST ="your_host";
	const UNAME ="your_username";
	const PW ="your_pw";
	const DBNAME = "your_dbname";
	public static function doConnect();
}
?>
 
< ?php
//Filename: UniversalConnect.php
ini_set("display_errors","1");
ERROR_REPORTING( E_ALL | E_STRICT );
include_once('IConnectInfo.php');
 
class UniversalConnect implements IConnectInfo
{
	private static $server=IConnectInfo::HOST;
	private static $currentDB= IConnectInfo::DBNAME;
	private static $user= IConnectInfo::UNAME;
	private static $pass= IConnectInfo::PW;
	private static $hookup;
 
	public static function doConnect()
	{
		self::$hookup=mysqli_connect(self::$server, self::$user, self::$pass, self::$currentDB);
		try
		{	
			self::$hookup;
			//Uncomment following line for develop/debug
			echo "Successful MySql connection:<br />";
		}
		catch (Exception $e)
		{
			echo "There is a problem: " . $e->getMessage();
			exit();
		}
		return self::$hookup;
	}
}
?>
 
< ?php
include_once('IConnectInfo.php');
include_once('UniversalConnect.php');
class CreateTable
{
	private $drop;
 
	public function __construct()
	{
		$this->hookup=UniversalConnect::doConnect();
		$this->tableMaster="your_table_name";
		$this->dropTable();
		$this->makeTable();
		$this->hookup->close();	
	}
 
	private function dropTable()
	{
		$this->drop = "DROP TABLE IF EXISTS $this->tableMaster";
 
		try
		{
			$this->hookup->query($this->drop) === true;
			printf("Old table %s has been dropped.<br />",$this->tableMaster);
		}
		catch (Exception $e)
		{
			echo "Here is why it did not work:  $e->getMessage() <br />";
		}
	}
 
	protected function makeTable()
	{
		$this->sql = "CREATE TABLE $this->tableMaster (
			id SERIAL,
			topic NVARCHAR(24),
			header NVARCHAR(120),
			graphic NVARCHAR(60),
			story BLOB,
			PRIMARY KEY (id))";
		try
		{
		  $this->hookup->query($this->sql);
 
		}
		 catch (Exception $e)
		{
			echo 'Here is why it did not work: ',  $e->getMessage(), "<br />";
		}
		echo "Table $this->tableMaster has been created successfully.<br />";
	}
}
 
$worker=new CreateTable();
?>

The connection routines have been improved upon over time, and you can find out more about it in the original post on the subject on this blog. For now, Play the application and Download the source files. (When you click the Play button, you enter the Login UI. Use the same un/pw combination from the Functional Protective Proxy post on this blog. (Hint, you can find it in the ILogin abstract class.)
PlayDownload

In creating the table at first I used the TEXT data type for a large block of text, but then decided that a BLOB type may have a slight advantage. The BLOB is a VARBINARY and TEXT is VARCHAR, and BLOB seemed to have a slight advantage. However, the advantage may be smaller than I originally thought. Check out the MySQL docs on these two data types and you can see the differences for yourself. Figure 1 shows what the editor in the administration module looks like:

Figure 1: Administrative Module Editor

Figure 1: Administrative Module Editor

From Proxy to Strategy

The login module of this CMS is based on the Proxy pattern. Now, as in previous CMS examples, the administrative module is based on a Strategy pattern. Each object in the Strategy pattern represents an algorithm to accomplish a different task. In this case, the tasks include:

  • Entering data
  • Editing data
  • Displaying data
  • Displaying a page based on stored data.

All requests go through a context relying on a Strategy interface. In this case, I used an abstract class which allowed the addition of several protected properties and a constant with the name of the table. This is all in addition to the main method in an abstract public function, the algorithm method, executeStrategy(). Following the Strategy design pattern, begin with the Context. Continue reading ‘Sandlight CMS VI: Strategy Administrative Module’

Share

PHP-MySql Universal Connection: The Try/Catch Exception Tweak

universal2In 2012, I posted a little piece on a PHP-MySql connection utility. Since the time of the original posting, I’ve used that utility again and again. It’s been used in my own applications (many of which have appeared on this blog), with my clients, and with others who I was able to help. Other than minor tweaking with the replacement of a conditional if statement with the try/catch exception, it has remained largely unchanged.

Recently, John Slegers from Belgium provided some thoughtful comments for refactoring the little utility. (Being a big Salamander fan [Netflix], I listen to what the PHP folks from Belgium have to say!) His comments inspired some re-thinking, and while I made some changes, they were minor and along different lines. Download the revised version and take a look at it:
Download

Object Thinking

In developing software, I think in terms of objects and object structures. That is, rather than thinking in lines of code and statements, I find it easier to think in terms of what an object does and how it fits into a larger object structure rather than how statements are used. So, in using PHP with MySql, I tend to view connections to the database as something an object handles and I don’t need to worry about otherwise. Recently I was working on an Observer design patter using the built-in SplSubject and SplObserver. It occurred to me that the concrete subject might pull data out of a MySql database, and so I just popped a copy of the universal connect utility in the folder and it’s all set. Figure 1 illustrates it use:

Figure 1: Adding connection utility to small observer

Figure 1: Adding connection utility to small observer

The utility is very small—a single interface and class—but it’s very useful.

The Code

First of all, an interface stores connection information in constants, and as long as you’re using the same database, you can re-use the values. The single method is defined as public and static so that a single line of code can launch the method that uses the constants for connection. It is largely unchanged from the original.

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

The UniversalConnect class does all of the work for making a connection using the constants in the interface. Again, static declarations mean that the class need not be instantiated to use the method:

<?php
ini_set("display_errors","1");
ERROR_REPORTING( E_ALL | E_STRICT );
include_once('IConnectInfo.php');
 
class UniversalConnect implements IConnectInfo
{
	private static $server=IConnectInfo::HOST;
	private static $currentDB= IConnectInfo::DBNAME;
	private static $user= IConnectInfo::UNAME;
	private static $pass= IConnectInfo::PW;
	private static $hookup;
 
	public static function doConnect()
	{
		self::$hookup=mysqli_connect(self::$server, self::$user, self::$pass, self::$currentDB);
		try
		{	
			self::$hookup;
			//Comment out / Uncomment following line for develop/debug
			echo "Successful MySql connection:<br />";
		}
		catch (Exception $e)
		{
			echo "There is a problem: " . $e->getMessage();
			exit();
		}
		return self::$hookup;
	}
}
?>

The try/catch exception stops everything cold if the connection is not made, and the Exception provides the information you need to correct it. If the try is successful, it returns the connection to the client.

That’s pretty much it. During development, I like to see the Successful MySql connection message, but once everything is ready to go, I just comment it out. The following little class tests the connection and should not be included in a development folder:

<?php
include_once('UniversalConnect.php');
class UseMySQLConnect
{
	private $doConn;
 
	public function __construct()
	{
		try
		{
			$this->doConn=UniversalConnect::doConnect();
			echo "Succesful connection";
			$this->doConn->close();
		}
		catch (Exception $e)
		{
			echo "There is a problem: " . $e->getMessage();
			exit();
		}
	}
}
$worker=new UseMySQLConnect();
?>

The line,

$this->doConn=UniversalConnect::doConnect();

takes care of all connection steps for you, and instead of having your code piled up with the same code block required by mysqli() (PHP’s improved MySql function), you just make sure the include_once makes the UniversalConnect class available, and you can use it wherever you need MySql.

Share

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
Continue reading ‘Get MySql and PHP to Work Together in Raspberry Pi’

Share

Basic PHP-MySql OOP

basicMySQLGetting the Basic with PHP-MySql

Design Patterns are general OOP solutions for any computer language, and yet each language has different typical uses. One of the most important uses of PHP is to communicate with MySql. As the “middleman” between HTML and MySql, developers need to juggle three languages well in the HTML< ->PHP< ->MySql sequence and shuffle. All of the OOP work, though, belongs to PHP since neither HTML nor MySql is object oriented. This post is dedicated to the fundamental OOP questions surrounding HTML< ->PHP connections, instantiating class instances and creating re-usable MySql classes. I’d also like to touch on using comments in code and perhaps stir up a little controversy with comments about certain “standards” for comments in PHP.

The first thing to do is to take the program for test drive and look at the code. Run the program by clicking the Play button and click the Download button to get the files.
PlayDownload

The HTML Communication

Object oriented programming is all about objects communicating with one another. The first communication is between HTML and the PHP object it first calls. As the user interface (UI), the HTML document must get data from the user and pass it to PHP for processing. In PHP object communication, each object can call the object as a whole or specify a method or property in the other object to perform a specific task. With HTML, we pretty much have to be satisfied with a call to an object (or object trigger) and depend on PHP to carry the ball from that point on. However, if an HTML document has several forms, each form can call a different object, or each form submit button can have a different name. By using the unique name for each submit button a single Client class can determine which operation the user requests. The following HTML is set up for that purpose:

< !doctype html>
<html>
<head>
<link rel="stylesheet" type="text/css" href="easy.css"/>
<meta charset="UTF-8"/>
<title>Basics of PHP OOP</title>
</head>
<body>
<img class="fl" src="snap150.jpg" alt="snap"/>
<header>
  <h1>Basic Classes for Basic Tasks I</h1>
  Keeping objects clear and focused on a single task</header>
<div>
  <h2>Entering Data into a Table and Retrieving it</h2>
</div>
<div>Data are entered into a table through a UI, but automatically generated data also may be entered.</div>
<br />
<form action="Client.php" method="post" target="feedback">
  <div><strong>Developer Info:</strong><br />
    <label for="name">Name:</label>
    <input type="text" name="name" id="name"/>
    <br />
    <label for="email">Email:</label>
    <input type="email" name="email" id="email"/>
    <p></p>
  </div>
  <div><strong>Programming Language Used Most</strong><br />
    <input type="radio" name="lang" id="cs" value="C#"/>
    <label for="cs">C#:</label>
    <br />
    <input type="radio" name="lang" id="js" value="JavaScript"/>
    <label for="js">JavaScript:</label>
    <br />
    <input type="radio" name="lang" id="php" value="PHP"/>
    <label for="php">PHP:</label>
    <br />
    <input type="radio" name="lang" id="python" value="Python"/>
    <label for="python">Python:</label>
  </div>
  <br />
  <div>
    <input type="submit" name="insert" value="Insert Data into Table"/>
  </div>
</form>
<p></p>
<div><strong>Display All Data in Table with a new Form</strong>
  <p></p>
  <form action="Client.php" method="post" target="feedback">
    <input type="submit" name="all" value="Display all data"/>
  </form>
</div>
<br />
The PHP class uses 4 MySql fields to store and retrieve the information:
<ul>
  <li>id</li>
  <li>name</li>
  <li>email</li>
  <li>lang</li>
</ul>
<iframe name="feedback" seamless width="300" height="200">PHP</iframe>
<div>
  <p>
  <a href="EasyUpdateOOP.html">Update and Drop Files</a>
  </p>
</div>
</body>
</html>

You may also want to take a quick look at the CSS file that goes with the HTML.

@charset "UTF-8";
/* CSS Document */
/*66635D (gray)  E54140 (red) F7EBD5 (cream) 6CC5C1 (aqua) 000000 (black)   */
body
{
    background-color: #F7EBD5;
    font-family: sans-serif;
    color: #66635D;
}
a
{
    color: #E54140;
    text-decoration: none;
    font-weight: bold;
}
 
.warning
{
    color: #E54140;
    font-weight: bold;
}
 
h1
{
    font-family: "Arial Black", Verdana, Helvetical,sans-serif;
     background-color: #6CC5C1 ;
     color: #E54140;
     text-align: center;
}
header
{
    background-color: #E54140;
     color: #E54140;
     padding:1em;
     color: #F7EBD5;
     text-align: center;
}
 
.fl
{
    float: left;
}
 
div
{
    padding-left: 1em;
}
h2
{
    color: #000000;
}
 
li
{
    font-weight: bold;
}
iframe
{
    background-color:#6CC5C1;
}

One thing that may stir the pot (among developers) found in the HTML document is the use of iframe tags. HTML5 retained iframes, and in the jQuery Mobile documentation, you will find the use of iframes. They no longer present a hazard to SEOs; and so I don’t see the harm using them to display PHP output. Besides, I could not find a suitable replacement. (I’m not about to mix PHP and HTML to create a workable hack straight out of the 9th circle of Hell.) So see if you can live with iframes for now, and we’ll get smelling salts for the fainters.

The Client Sorts it Out

When the UI has many options that must be handled by a client object to make the appropriate request from a collection of objects, each with a single responsibility, there’s going to be some kind of conditional statement to sort out which object to use. For lots of good reasons that I won’t go into here, switch/case and if/ifelse statements are to be used sparingly and judiciously in OOP and design pattern programming. An alternative approach would be to add several methods to the Client class and then have individual trigger files launched from the different forms in the HTML document call the appropriate Client method. That’s messy, but because HTML cannot address a class method, that may actually be a more orthodox approach. (Keep those smelling salts handy.)

The basic logic of the approach used is this:

  • When a submit button is pressed it can be trapped by the isset() method
  • Provide a name for each submit button
  • Test for variable being set and not NULL
  • Once tested use unset() to remove set state
  • Call object of set variable

Now take a look at the Client class to see how that reasoning has been implemented:

< ?php
ERROR_REPORTING( E_ALL | E_STRICT );
ini_set("display_errors", 1);
function __autoload($class_name) 
{
    include $class_name . '.php';
}
class Client
{
        //Variable to select the correct class
	private $task;
 
        //Which submit button used?
	public function __construct()
	{
	    if(isset($_POST['insert']))
            {
                unset($_POST['insert']);
                $this->task= new DataEntry();   
            }
            elseif(isset($_POST['all']))
            {
                unset($_POST['all']);
                $this->task= new DataDisplay();
            } 
            elseif(isset($_POST['update']))
            {
                unset($_POST['update']);
                $this->task= new DataUpdate();
            }
            elseif(isset($_POST['kill']))
            {
                unset($_POST['kill']);
                $this->task= new DeleteRecord();
            } 
	}	
}
$worker = new Client();
?>

The Client has four operations reflecting the four classes:

  1. DataEntry()
  2. DataDisplay()
  3. DataUpdate()
  4. DeleteRecord()

That’s all there is to the communication between HTML and the PHP Client class. The Client works (as do all client classes) to make requests. The origin of the request is the user through the HTML UI.
Continue reading ‘Basic PHP-MySql OOP’

Share

Easy Writer: Setup for Raspberry Pi PHP

RaspPHPFocus on writing PHP Code

In fiddling around with PHP on a Raspberry Pi running on a Debian Linux OS more or less directly from a terminal mode, I realized that the focus (in my case) was getting the Linux commands right and very little with actually writing PHP programs. Most queries about getting the setup right involved Linux system administration and not PHP programming.

This short post is for Raspberry Pi users (and perhaps Linux users in general), and it focuses on setting up your Raspberry Pi so that you can use default Raspberry Pi editors (LeafPad) and the File Manager to work with PHP programs. Once set up, you will find the process of creating server-side programs in PHP much easier with no need to use the  terminal editors after setting up your system.

Installing Apache and PHP

Because PHP is a server-side language, you will need both a server and PHP installed on your Raspberry Pi. Using the Root Terminal (Accessories → Root Terminal), enter the following line:

sudo apt-get install apache2 php5 libapache2-mod-php5

Press the Enter key and patiently wait until it’s completed the setup. Once done, you computer will have both a web server and PHP installed. To test whether installation was successful, open a browser from the main menu (Internet → NetSurf Web Browser) and enter the following:

  http://localhost/

If everything works, you will see the message shown in Figure 1 on the right:

Figure 1: Default Web Page

Figure 1: Default Web Page

This location (http://localhost) is the root for your Web pages—PHP and any other Web page you decide to put on your Raspberry Pi Apache server. The name of the file is index.html. On your Raspberry Pi, in the Linux file system, the address is:

  /var/www/

Open the File Manager (Accessories → File Manager) and in the window where you see /home/pi enter /var/www. You will now be able to see the icon for the html file that was automatically created when you installed the Apache server.
Continue reading ‘Easy Writer: Setup for Raspberry Pi PHP’

Share