Simple Flat File Database

I had recently been refused access to a sql database for a project, this I started looking for other options. After a little searching I stumbled across txtSQL, which is a very powerful but easy to use flat file database.

To start off download the latest release (2.2 final at the time of writing this). Then read the read me in the doc folder it will walk you through setting up the include files you will need as well as the database folder, by default called data. One you have these placed correctly just like with mysql you will need to connect to the database.

include('includes/txtSQL.class.php');
$sql = new txtSQL('includes/data');  // Location of the database files
$sql->connect('root', ''); // This is the default name and password
$sql->selectdb('XXX'); // XXX is the database name

This will let you connect to the database you want to use, I do recommend changing the password from its default. However there is no database or tables right now so you should add these.

// txtSQL created a new database named testDatabase 
$sql->createdb(array('db' => 'XXX')) or die('Error creating txtSQL DB, txtSQL said: '.$sql->get_last_error()); 

// Create Table in new database
$columns = array(
'id'    => array('type' => 'int', 'auto_increment' => 1, 'permanent' => 1 
'name'  => array('type' => 'string', 'max' => 50), 
'email' => array('type' => 'string', 'max' => 50) ); 

$sql->createtable(
'db' => 'testDb', 
'table'   => 'testTable', 
'columns' => $columns); 

Now you have tables set up, unlike sql databases, this is all commands with arrays, so it takes a little getting used to, but is not to horrible to learn. Finally, you just need to manipulate the data.

// Select
$where = 'somthing = '.$_POST['something'];
$results = $sql->execute('select', array('table' => 'XXX', 'where' => array('0' => $where), 'limit'  => array(1)));

// Grab first result
$result = $results[0]; // Just want the first result

//or use them all
foreach($results as $result) // Grab each row from the result
{ 
//... Do something
}

// Insert
$insertvalues = array( 'name' => 'matt', 'date' => '9/8/06' );
$sql->execute('insert', array('table' => 'XXX', 'values' => $insertvalues));

The rest delete and update are also similar, and you can find example in the documentation included.
Although txtSQL is not a perfect replacement for a real sql database it does work rather well. However it is suggested that is only be used with smaller database needs, otherwise stick to a real real database management system. Still for what I needed it to do I am quite happy.

Making a code editor in a webpage

One of the big goals that I had over the winter break was to finish the upgrade of the Plymouth AMS website. Is started writing in in xml with a xsl style sheet. Although this is good for simper websites that I am going to be around to keep it up, because this is not the case I had to think of something else. After talking with a more experienced web programer in a workshop, I decided to re-write it in php as a web app. Thus after logging in you could edit the basic html on the site, yet this would leave the more advanced layout out of it. Perfect for what the AMS group will be doing. Ether way it is a huge advance from the front page created website they had before.

All that it will requite is a simple html/text editor written in php. This turned out to be a little more complex then I thought it would be. I started with a simple text area that filled the width of the page. Looks good. But after trying to edit 2 lines I got really annoyed. NO TABS, there was no way to tab, and a programer like me lives for code structure. So now I needed tabs... By default web pages use the tab key to go from one field to another not to add a tab character, luckily after some searching I found this very handy java script that would add tabs to text areas. It works very well, and now I have my beloved tabs again.

So first problem fixed, now another rears its head. This text area allows html code, which tends to have quotes here and there, the Mysql calls to the database I am storing the text in also has both double and single quotes. This does not mix well. Basically once it concatenates the strings together the quoted sections get completely screwed up by the new quotes added to my query. This must be a common occurrence so off I went searching for php functions that would take care of it.

As expected there was a function called htmlentities, basically it converts all applicable characters to HTML entities, aka the escaped form. This takes care of the quote problem, I just save this to the database and edit it when needed, as a bonus the text area field accepts these characters and shows them as they should be with out any problems. Thus there is no need to convert them back when editing the text, only when displaying them in html. See example below...

$query_string="UPDATE `***` SET `***` = '".htmlentities(stripslashes($_POST['***']),ENT_QUOTES)."' WHERE `***` = '***' LIMIT 1";

When displaying the html code from the database it is a little more complicated. First off I wanted this to be a hybrid text/html editor, this when you line return, it puts a <br /> just like a text editor would. To do this use the handy nl2br function, it converts the /n new line characters to <br />. Which looks really good, except that it does every line return, I only want non code style returns so then follow this by str_replace which replaces every "><br />" with just a ">" Thus removing all of the non needed breaks.

The last part is html_entity_decode which convert all HTML entities to their applicable characters. Thus the html is back to normal. You put this all together and it does the job very well.

$message = mysql_fetch_array($result, MYSQL_ASSOC);
$mconverted = nl2br( html_entity_decode($message['text']) ); // Convert back to html code
$removes = str_replace("><br />",">",$mconverted ); // Remove extra br due to code style
$xtpl->assign('message', $removes);

PHP & Mysql on Mac OSX

Now that have a running G4 tower that I can use as a server, all that is left is to set up the required items on it. My main goal is PHP and MYSQL so that I can start development on the next update to the PSU AMS groups website. From what I had learned from previous setups is that PHP is already installed in Mac OS 10.4, however it is not completely active. Luckily the process was very painless and straight forward. It also helped that I was not doing anything more then a simple setup.

I made the PHP setup a lot harder than it had to be and tried to build from the latest source. Sadly I did not have all of the required dependancy files for it and that quickly became a waist. Still it was a good idea to read the Apple developer page on PHP first here. Luckily someone had already made an installer for PHP, and I just had to find it at Entropy. It installed without any problems, just make sure to read the directions.

Mysql was next, just like PHP there was a handy installer for it. Apple's developer site has a great page on installing Mysql on Mac OS X here, that I started with. It boiled down to just get Mac installer from the Mysql site and install it. It also included a control panel for Mysql that I installed, it lets you set Mysql to start up on every time the computer is turned on.

One thing that was missing was the phpMyAdmin page for Mysql. I thought that it would be included, however after trying to get to lazurus.local~efeion/mysql, which is how I get to it from school, it quickly become apparent that was not the case. It is actually a separate project here. To make it similar to what I am use to using, after extracting the directory, I named it mysql and placed it in the sites folder in my directory. This is where apache is set to host websites by default, and is fine for what I am doing. Now if I went to lazurus.local/~efeion/mysql and the phpMyAdmin page would show up, but now complained about not being configured. Easiest way to fix that was to go to the mysql/scrips/setup.php page. This handy script will generate a config file for phpMyAdmin, just grab what it outputs and save it in a file called config.inc.php located in the main mysql folder. There is a sample version in that folder for the more advanced users to set up manually. However I am still new to this and did not want to dig that deep yet.

Now I had a PHP web server with a Mysql back end all set up, which was a lot easier than I expected.