Stuff (Tag: mysql)

Getting a development webserver set up at home with Xubuntu

I had an old DELL 4300 lying around at home and thought it was about time I put it to some use by setting it up as a LAMP server to work on little PHP projects from home. Since it is quite a low spec machine I decided to go with Xubuntu on this one. The latest release is 9.10, but I use Ubuntu 9.04 on my laptop so thought I would go for the same version of Xubuntu for this one since I had the CD lying around within yards of the server-to-be anyway. I am not knocking Ubuntu 9.10, I upgraded my workstation at work to be running this version and it is great, although I haven't quite figured out the quirky behaviour surrounding the new UbuntuOne service, but that is another story.

First thing you will want to do is go to Xubuntu.org and get a release of Xubuntu. The following should work regardless of whether you decide on 9.10 or 9.04.

Get your old machine out and wire it up to the network, a monitor and a keyboard. Follow the installation steps for Xubuntu and once it is started for the first time you can log in. Here you will probably find that there are updates to get for the distro so just follow the prompts until the system stops quizzing you.

A little churnering and a couple of restarts later you will be ready to get started installing the server software.

Hit Alt+F2, enter 'xterm' and hit 'Run'. A terminal window will open. From here enter:

sudo apt-get install ssh

This will prompt you for your password and then install an SSH server on your machine. Brilliant! Make sure your server-to-be is set up with a static ip so you know how to find it easily. You can read a good guide here.

From here you can remove the keyboard and monitor from you server-to-be, hide it somewhere out of your better half's sight and go to your laptop or whatever you normally work on. I went to my laptop of course.

Connect to your server-to-be via SSH

ssh username@host

Now from here you can start installing other server software, starting with Apache, MySQL, PHP:

sudo tasksel install lamp-server

You will be prompted for your password then a blue screen will appear and begin downloading and installing. At some point you will be prompted to choose a new password for the root mysql user, don't forget this one.

That is it really. You are pretty much ready to go from here. Going to the servers IP address in your web browser should present you with a page saying 'It Works!' in large type.

Your document root is under /var/www . All you will find there is a file called index.html with the contents described above.

One other thing you may want that is not included in the default installation here is mod_rewrite. To activate this enter:

sudo a2enmod rewrite

then restart Apache

sudo /etc/init.d/apache2 restart

I use phpMyAdmin to work with MySQL databases. This tool has endless value and if you haven't already thought of it, I reccommend installing it:

sudo apt-get install phpmyadmin

During installation you will be prompted for the MySQL root users password as well as being asked to set up a new password for phpmyadmin.

Next you will need to include the configuration for phpmyadmin to be used with Apache. Open /etc/apache2/apache2.conf in vi

sudo vi /etc/apache2/apache2.conf

Add the following line to the end of the file:

Include /etc/phpmyadmin/apache.conf

Save :w and quit :q. Now restart Apache once more:

sudo /etc/init.d/apache2 restart

Browsing to yourserversip/phpmyadmin, you should be presented with the log in screen for phpMyAdmin.

You are up and running. Install you favourite version control system and you are away. Enjoy.

Remember, this is by no means a secure installation of a webserver and is only intended for use at home on your personal projects.

By Rick Vause Tagged: , , , , , , ,

SQLite Class for PHP

When I started working on projects in PHP/MySQL I found my self writing the same bits of code over and over again to connect, update, debug what was going on with my database.

About a year ago now I came across this class by Micah Carrick, shared.

I started using it and since then have never looked back, it is simple, but extremely useful. Felt as if it was made for me.

So yesterday when I started on a project that will involve usage to SQLite, I instantly started to miss the MySQL class I had been using all this time.

So I decided it would be worth investing a little time in modifying Micah Carrick's class to support SQLite databases. That way I can work the same way I have been for a while now with MySQL in SQLite.

Brilliant. An SQLite class for PHP just like Micah Carrick's MySQL class for PHP.

Click here to Download it Zipped up. I am satisfied it is complete, but just in case, look out for updates here.

By Rick Vause Tagged: , ,

sagetomysql.php

Complete Sage Line 50 Database translation.

Sorry it has been a little longer than I expected to be posting this file since I completely forgot about it.

I have been using this script for well over a month now, every hour, on the hour translating a Sage Line 50 SALES LEDGER table into my MySQL table that holds a copy of that data and updates it.

It is a shame that is written in PHP, but I needed the added advantage of easily running the script on a web server.

Python script coming soon...

Download sagetomysql.php (tarred and gzipped)

By Rick Vause Tagged: ,

Connecting, Reading and Translating a SAGE Line 50 Database Using PHP5 / Apache2 on Windows XP/Vista

I decided to write this guide and post it here on my website as there is a severe lack in information out the on the web for the intermediate Windows user. Having very little idea about how ODBC connections work and how I would go about pulling data from a SAGE Line 50 database to my MySQL database served on a Linux platform.

This guide will be based on the assumption that you are aware of simple configuration tasks in Windows XP/Vista and that you have a good understanding and control over your own server where we are moving the data to whatever platform you are running it on.

In my case I was moving data from a Sage Line 50 database hosted on a Windows Vista machine to a MySQL 5 server running on Ubuntu Server Edition 9.04. I used a third machine to translate the data which was running XAMPP (Apache2 with PHP) on Windows Vista. You could use another XP/Vista machine like I did or you could use the machine that Sage Line 50 is on. I looked for a SAGE Line 50 ODBC driver for the Linux platform but did not find anything so it was nessessary for me to use a middle man machine of the Sage Machine itself. If anyone knows of a Sage Line 50 ODBC driver for Linux, please contact me as I would be very keen to hear about this. This was annoying as I had to set up another server. Even though I was only using PHP's CLI I used XAMPP for the set since there is no configuration required and it is easy to remove afterwards.

Now onto the how to:
The first thing that you will need to do is set up the Sage Line 50 ODBC driver on the computer that will be doing the 'translation'. You will find an install package for the Sage Line 50 ODBC driver inside the Sage Line 50 directory on the machine that Sage Line 50 is installed on.
Once the install package is complete you will need to set the up the data path for the Sage Line 50 ODBC driver from the Windows Control Panel. Go to the Windows XP/Vista Control Panel (You will have to select something like 'View Classic Mode' somewhere to see all the functions in the Control Panel). Open up the ODBC configuration function. You will see in the list something like 'SageLine50v13'. Edit this entry to set the data path. You need to point to the network location where the Sage Line 50 data is stored. Usually under the Sage Line 50 installation directory in a folder called ACCDATA. To access this over the network, Windows file sharing will have to be set up (READ ONLY) on that directory. So set your data path as something like '\\192.168.1.100\SAGE\ACCDATA' .

Once that has been done you can start writing your PHP script. Here's an example:

<?php
$odbc['user'] = ?username?;
$odbc['pass'] = ?password?;
$odbc['dsn'] = ?SageLine50v13?;
$odbc['table'] = ?SALES_LEDGER?;
$fields = array();
$results = array();
$conn = odbc_connect($odbc['dsn'],$odbc['user'],$odbc['pass']);
if (!$conn) die(?Error connecting to: ?.$odbc['dsn']);
$sql = ?SELECT * FROM ?.$odbc['table'];
$r = odbc_exec($conn,$sql);
if (!$r) die(?Error executing query: ?.$sql);
for ($i=1;$i<=odbc_num_fields($r),$i++)
$fields[odbc_field_name($r,$i)] = odbc_field_type($r,$i);
$x=0;
while (odbc_fetch_row($r)) {
for ($i=1;$i<=odbc_num_fields($r),$i++)
$results[$x][odbc_field_name($r,$i)] = odbc_result($r,$i); $x++;
}
odbc_close($conn);
?>

So now you have two arrays, $fields and $results, with all the information in to move the data over to your MySQL database.

It is just a case of structuring the table using the $fields array and then looping through all the data in results and adding it to your new table on your MySQL database. Pretty simple really.

I am currently fine tuning the script I am using for this, since it will eventually be set up on the Sage Line 50 machine to run as a Scheduled Task in windows to INSERT or UPDATE data on an hourly basis. Once I have completed this script I will share it here will all the nessesary information.

It is a shame that there is little to no information for us casual developers to access on Sage and it is a shame that the Developer Community Subscription is so expensive. I hope that my article will help someone trying to do something similar.

Clear applications for this that I can think of right away would be if you needed your Sage Line 50 data linked in with your osCommerce data.

By Rick Vause Tagged: , , ,

Sub Navigation: