For my first project with the PHP scripting language and MySQL database, I put together a dynamic Web site that could handle posts and administrative modifications from, shall we say, non-technical people. The project turned out to be much easier than I expected.
My humble goal was to create three similar pages, a general “What’s New” page, an “Events” page, and a “Sports Broadcasts” page for my school’s (mostly) student-run radio station, WGAJ. I would need a simple database, capable of holding the information required for the three pages, plus various other pages for administration, where one could add and remove posts. Speed and scalability were not an issue due to the low traffic to the site, but price and simplicity were; I had to do this on my own, and our yearly budget was nearly gone.
I began by reading up on PHP and MySQL. The PHP site has a full language synopsis and tutorial, and is very useful for looking up functions. The MySQL site also has a nice SQL language reference.
Installation
My first step was to install the software. Although I was running Debian, I avoided using apt-get and went for source instead, knowing that, even though it might be harder to configure, the chances of success would be greater. At www.php.net I downloaded the source tarball for PHP 4.3.2. Heading over to www.mysql.com, I ended up downloading a binary of the older 3.23 version (having not scrolled all the way down and seen the source tree).
In my eagerness, I compiled PHP first. Knowing that the PHP tag was <? … ?> and a few basic commands, I created making a quick test.php page and tried it. Nothing. After a Google search and some IRC questioning, I found that to integrate PHP with Apache, I needed something called apxs, and it needed to be linked in during the ./configure stage of installation. A quick apt-cache search revealed that the mysterious apxs was a part of the Apache development package. I downloaded it, ran a quick updatedb and locate apxs, and found it in /usr/bin. After a recompile using --with-apxs=/usr/bin/apxs I edited my /etc/apache/httpd.conf to include:
AddType application/x-httpd-php .php .phtml
AddType application/x-httpd-php-source .phps
I restarted the Apache daemon and tried my test.php page again. Success!
To get MySQL running, I unpacked it, entered the folder, and doing the first logical thing that came to mind, ran ./configure. Text spewed across my screen, telling me that this was a pre-compiled version but that it would now start for me anyway. Following this was a cryptic error message, essentially telling me configure didn’t go as planned. I thought, okay, that won’t work, guess I have to read the instructions. I opened up the INSTALL instructions, did what it said, and tried again. This time I didn’t get an error message, I just got “030725 02:30:14 mysqld ended.” After some hunting I found the the error log in the data directory. The log claimed that it couldn’t connect to port 3306. A helpful IRC user told me to try the command fuser -v 3306/tcp, which revealed that mysqld, the MySQL daemon, was still running from the first time I tried it, though wasn’t doing anything. After squashing its processes, I could finally run the recommended ./bin/safe_mysql command successfully.
(I found IRC a good place to turn for help, notably in the #mysql and #php IRC channels of irc.freenode.net, but you have to be patient sometimes.)
I soon discovered I had to recompile PHP again, this time linking it to mysql with the option --with-mysql=/usr/local/mysql. My advice: install MySQL first.
The database
The next step, creating the database, was probably the easiest part of the operation. After browsing some tutorials, I simply typed mysqladmin -uroot create wgaj_db to make a database (WGAJ is the station’s call sign), followed by mysql and connect wgaj_db to get inside and edit it.
Before you make any tables, you need to know what you want in them. (I learned that the hard way, deleting and recreating tables a half dozen times.) My first of three tables, for simple posts, was the easiest. It needed a good amount of space for comments, and smaller text space for signatures. My first attempt was to do the text as characters, specifying text char(1024). This didn’t work out so well, since the char date type has a maximum of 255 bytes. However, I found that I could use the text date type, which holds more than the char type. And so, without too much hassle, I had my first database: a single table with an integer key for reference, a text block, and char(255) for the signature. My other two tables, for the Events and Sports pages, were equally simple to create, containing only a couple more variables each.
PHP
If you have experience coding in C-based languages, PHP is breeze. If you don’t, it’s still a breeze. PHP integrates well into HTML and the MySQL calls are logical and easy to follow.
A PHP page is much like an HTML page with .php file type. The second is that it uses <? … ?> tags, inside of which go PHP code. You can send HTML tags and text into PHP with the echo command. However, the real power of PHP lies in its ability to work with MySQL databases.
To connect to a database and read a table, all you need is four quick and easy lines inserted in your PHP file:
$db = mysql_connect("server", "user", "password");
mysql_select_db("database_name", $db);
$query = mysql_query("SELECT * FROM table_name", $db);
while ($tablerow = mysql_fetch_array($query)) { ..... }
The while loop executes for every row in the table, and table variables can be accessed from the array $tablerow[‘var_name‘].
To integrate with an HTML form is just as easy. If you have a page called update.php, and inside you have a form with its action attribute set to update.php, it refreshes itself when the site visitor submits the form, and the values of all the form items are put into the array $_POST[], which can be accessed with $_POST[‘name_of_form_item‘].
The hack
Soon I ran into a tight spot. I wanted to display pages that showed information about upcoming events, so I needed to list items by the date they would occur. I decided to store the date in the database as an eight-digit integer in the YYYYMMDD form, so that sorting it by that number would result in a chronological order. The hard part was then taking that integer and turning into a readable date. At first I thought I could get the day number by taking my number and doing modulus by 100, and work from there. There were two problems with that. First, I knew it would be inefficient. Second, as far as I could tell from the PHP Web site, PHP lacks a modulus function, and I didn’t feel like making one. Back to the drawing board.
My next idea was to treat the eight digits as a string and break it into parts. This should be simple and relatively fast, I thought. I didn’t have to worry about explicitly turning my date code into a string because PHP is like Perl in that it has no data types; there are only scalars and arrays. I checked the list of functions again, looking for something roughly equivalent to the substring method in Java, or the left and right functions in RealBasic that make a string out of the first X chars starting from the defined side. Yet again I could find nothing that suited my needs.
I did find a chunk_split(X,Y,Z) function, which inserts the character Z into string X every Y characters, and split(X,Y), which breaks up string Y into smaller smaller strings every time there’s an X. With them I patched together a hack: I would split the integer in the middle with a space with chunk_split and separate it into two strings with split and the equally handy list function:
$temp = chunk_split($mysql_row['date'], 4, " ");
list($year, $monthday) = split($temp, " ");
I did the same thing with $monthday, leaving me with three variables that I could format to my liking.
To output the data onto the page, I used echo $month . " " . $day . ", "$year; (the PHP string concatenater is period).
Looking back, I am positive there are simpler, faster, more efficient ways to do this, but hey, if it isn’t ugly, I didn’t write it. The site won’t start taking any real traffic until the school year starts up again, but so far our general manager is pretty darned happy!
If you want to make a dynamic Web page but are scared by the scope of such a project, don’t be. MySQL and PHP will get up running in no time, they’re free (gotta love OSS!), they’re easy to learn, and they’re powerful enough to do just about anything you want them to.
Sean Morton is a student at Deerfield Academy.
Category:
- Open Source
