Category Archives: Databases

PHP and MySQL Tutorial – Lesson 3

Welcome to the third and final lesson for this tutorial. If you’ve gone through Lesson 1 and Lesson 2, you already know the essentials for installing and writing useful scripts with MySQL and PHP. We’re going to look at some useful PHP functions that should make your life a lot easier. First, let’s look at include files.

We all know the basics of includes, right? Contents of an external file are referenced and imported into the main file. It’s pretty easy:You call a file and it’s included. When we do this in PHP there are two functions we need to talk about:include() and require(). The difference between these two functions is subtle but important, so let’s take a closer look. The require() function works in a XSSI-like way; files are included as part of the original document as soon as that file is parsed, regardless of its location in the script. So if you decide to place a require() function inside a conditional loop, the external file will be included even if that part of the conditional loop is false.

A Place for Everything

The include() function imports the referenced file each time it is encountered. If it’s not encountered, PHP won’t bother with it. This means that you can use include in loops and conditional statements, and they’ll work exactly as planned.

Finally, if you use require() and the file you’re including does not exist, your script will halt and produce an error. If you use include(), your script will generate a warning, but carry on. You can test this yourself by trying the following script. Run the script, then replace include() with require() and compare the results.

01 <html>
02
03
04
05 <body>
06
07 <?php
08
09 include("emptyfile.inc");
10
11 echo "Hello World";
12
13 ?>
14
15 </body>
16
17 </html>

I like to use the suffix .inc with my include files so I can separate them from normal PHP scripts. If you do this, make sure that you set your Web server configuration file to parse .inc files as PHP files. Otherwise, hackers might be able to guess the name of your include files and display them through the browser as text files. This could be bad if you’ve got sensitive information – such as database passwords – contained in the includes.

So what are you going to do with include files? Simple! Place information common to all pages inside them. Things like HTML headers, footers, database connection code, and user-defined functions are all good candidates. Paste this text into a file called header.inc.

01 <?php
02
03 $db = mysql_connect("localhost", "root");
04
05 mysql_select_db("mydb",$db);
06
07 ?>
08
09 <html>
10
11 <head>
12
13 <title>
14
15 <?php echo $title ?>
16
17 </title>
18
19 </head>
20
21 <body>
22
23 <center><h2><?php echo $title ?></h2></center>

Then create another file called footer.txt that contains some appropriate closing text and tags.

Now let’s create a third file containing the actual PHP script. Try the following code, making sure that your MySQL server is running.

01 <?php
02
03 $title = "Hello World";
04
05 include("header.inc");
06
07 $result = mysql_query("SELECT * FROM employees",$db);
08
09 echo "<table border=1>n";
10
11 echo "<tr><td>Name</td><td>Position</tr>n";
12
13 while ($myrow = mysql_fetch_row($result)) {
14
15   printf("<tr><td>%s %s</td><td>%s</tr>n", $myrow[1], $myrow[2], $myrow[3]);
16
17 }
18
19 echo "</table>n";
20
21 include("footer.inc");
22
23 ?>

See what happens? The include files are tossed into the main file and then the whole thing is executed by PHP. Notice how the variable $title was defined before header.inc is referenced. Its value is made available to the code in header.inc; hence, the title of the page is changed. You can now use header.inc across all your PHP pages, and all you’ll have to do is change the value of $title from page to page.

Using a combination of includes, HTML, conditional statements, and loops, you can create complex variations from page to page with an absolute minimum of code. Includes become especially useful when used with functions, as we’ll see down the road.On to the exciting world of data validation.

Simple Validation

Imagine for a moment that we’ve got our database nicely laid out and we’re now requesting information from users that will be inserted into the database. Further, let’s imagine that you have a field in your database waiting for some numeric input, such as a price. Finally, imagine your application falling over in a screaming heap because some smart aleck put text in that field. MySQL doesn’t want to see text in that portion of your SQL statement – and it complains bitterly.

What to do? Time to validate.

Validation simply means that we’ll examine a piece of data, usually from an HTML form, and check to make sure that it fits a certain model. This can range from ensuring that a element is not blank to validating that an element meets certain criteria (for example, that a numeric value is stipulated or that an email address contains an @ for an email address).

Validation can be done on the server side or on the client side. PHP is used for server-side validation, while JavaScript or another client-based scripting language can provide client-side validation. This article is about PHP, so we’re going to concentrate on the server end of things.

Let’s ignore our database for the moment and concentrate on PHP validation. If you wish, you can add additional fields to our employee database quite simply by using the MySQL ALTER statement – that is, if you want to commit to the values that we’ll validate.

There are several useful PHP functions we can use to validate our data, and they range from simple to highly complex. A simple function we could use might be strlen(), which tells us the length of the variable.

A more complex function would be ereg(), which uses full regular expression handling for complex queries. I won’t delve into the complexities of regex here, as entire books have been written on the subject, but I will provide some examples on the next page.

Let’s start with a simple example. We’ll check to see whether a variable does or does not exist.

01 <html>
02
03 <body>
04
05 <?php
06
07 if ($submit) {
08
09      if (!$first || !$last) {
10
11    $error = "Sorry! You didn't fill in all the fields!";
12
13    } else {
14
15        // process form
16
17        echo "Thank You!";
18    }
19 }
20
21 if (!$submit || $error) {
22
23      echo $error;
24
25      ?>
26
27      <P>
28
29      <form method="post" action="<?php echo $PHP_SELF ?>">
30
31      FIELD 1:<input type="text" name="first" value="<?php echo $first ?>">
32
33      <br>
34
35      FIELD 2:<input type="text" name="last" value="<?php echo $last ?>">
36
37      <br>
38
39      <input type="Submit" name="submit" value="Enter Information">
40
41      </form>
42
43      <?php
44
45 } // end if
46
47 ?>
48
49 </body>
50
51 </html>

The keys to this script are the nested conditional statements. The first checks to see whether the Submit button has been pressed. If it has, it goes on to check that both the variables $first and $last exist. The || symbol means “or” and the ! symbol means “not.” We could also rewrite the statement to say, “If $first does not exist or $last does not exist, then set $error to the following.”

Next, let’s extend things a little by checking to see whether a string is a certain length. This would be ideal for passwords, since you don’t want some lazy user entering a password of only one or two letters. You’d rather it be, say, six or more characters.

The function for this is, as you already know, strlen(). It simply returns a number equal to the number of characters in the variable being tested. Here, I modified the script above to check the length of $first and$last.

01 <html>
02
03 <body>
04
05 <?php
06
07 if ($submit) {
08
09      if (strlen($first) < 6 || strlen($last) < 6) {
10
11    $error = "Sorry! You didn't fill in all the fields!";
12
13    } else {
14
15        // process form
16
17        echo "Thank You!";
18
19    }
20
21 }
22
23 if (!$submit || $error) {
24
25      echo $error;
26
27      ?>
28
29      <P>
30
31      <form method="post" action="<?php echo $PHP_SELF ?>">
32
33      FIELD 1:<input type="text" name="first" value="<?php echo $first ?>">
34
35      <br>
36
37      FIELD 2:<input type="text" name="last" value="<?php echo $last ?>">
38
39      <br>
40
41      <input type="Submit" name="submit" value="Enter Information">
42
43      </form>
44
45      <?php
46
47 } // end if
48
49 ?>
50
51 </body>
52
53 </html>

Run this script and try entering six or fewer letters to see what happens. It’s simple yet quite effective.

 

Not-So-Simple Validation

Let’s talk a bit about using regular expressions with the ereg() and eregi() functions. As I said earlier, these can be either quite complex or very simple, depending on what you need.

Using regular expressions, you can examine a string and intelligently search for patterns and variations to see whether they match the criteria you set. The most common of these involves checking whether an email address is valid (although, of course, there’s no fail-safe way of doing this).

Rather than delve into the mysteries of regular expressions, I’ll provide some examples. You can use the same form we created on the previous page – just paste in the lines below to see how they work.

First, let’s make sure that text only has been entered into a form element. This regular expression tests true if the user has entered one or more lowercase characters, from a to z. No numbers are allowed:

1 if (!ereg("[a-Z]", $first) || !ereg("[a-Z]", $last)) {

Now, let’s extend this expression to check whether the string is four to six characters in length. Usingalpha: is an easy way to check for valid alphabetic characters. The numbers in the braces check for the number of occurrences. And note that the ^ and $ indicate the beginning and end of the string.

1 if (!ereg("^[[:alpha:]]{4,6}$", $first) || !ereg("^[[:alpha:]]{4,6}$", $last)) {

Finally, let’s build a regular expression that will check an email address’ validity. There’s been plenty of discussion about the effectiveness of checking for email addresses in this way. Nothing’s completely foolproof, but what I have below works pretty well.

I took this gem from the PHP mailing list. It’s a great resource – use it. And yes, this is as scary as it looks.

1     if (!ereg('^[-!#$%&'*+./0-9=?A-Z^_`a-z{|}~]+'.
2
3 '@'.
4
5 '[-!#$%&'*+/0-9=?A-Z^_`a-z{|}~]+.'.
6
7 '[-!#$%&'*+./0-9=?A-Z^_`a-z{|}~]+$', $last)) {

Don’t spend too much time looking at this. Just move on to the next bit.

 

Functions

Enjoy that last regex expression? Fun, wasn’t it? Wouldn’t it be even more fun to enter that chunk on a dozen different pages that need to process email addresses?! Think about the joy of finding a typo in that mess – and doing it a dozen times no less. But of course, there’s a better way.

Remember when we talked about include files earlier in this lesson? They’ll allow us to create a piece of code like the email checker and include it multiple times across several pages. This way, when we want to change the code, we need edit only one file, not many.

But if we want to get this done, we’ll have to use functions.

We’ve already used functions plenty of times. Every time we query the database or check the length of a string we’re using functions. These functions are built into PHP. If you’re a keen coder, you can extend PHP with your own customized functions. But that’s a bit advanced for this tutorial. Instead we’ll create functions that will reside within our PHP script.

A function is simply a block of code that we pass one or more values to. The function then processes the information and returns a value. The function can be as simple or complex as we like, but as long as we can pass a value in and get one out, we don’t really care how complex it is. That’s the beauty of functions.

Functions in PHP behave similarly to functions in C. When we define the functions, we must specify what values the function can expect to receive. It’s tricky to get a handle on at first, but it prevents weird things from happening down the road. This is done because the variables inside a function are known as private variables. That is, they exist only inside the function. You may, for instance, have a variable in your script called $myname. If you created a function and expected to use the same $myname variable (with the same value), it wouldn’t work. Alternatively, you could have the variable $myname in your script and also create another variable called $myname in your function, and the two would co-exist quite happily with separate values. I do not recommend doing this, however! When you come back and edit it six months later, you’ll be breaking things left and right. There are exceptions to this rule as with all things, but that’s outside the scope of this article.

So let’s create a function. We’ll start simply. We need to give the function a name and tell it what variables to expect. We also need to define the function before we call it.

01 <html>
02 <body>
03
04 <?php
05
06 function  addnum($first, $second) {
07
08    $newnum = $first + $second;
09
10    return $newnum;
11
12 }
13
14 echo addnum(4,5);
15
16 ?>
17
18 </body>
19
20 </html>

That’s it! First, we created our function. Notice how we defined two new variables, called $first and$second. When we call the function, each variable is assigned a value based on the order in which it appears in the list – 4 goes to $first, 5 to $second. Then we simply added the two numbers together and returned the result. “Return” here simply means to send the result back. At the end of the script we print the number 9.

Let’s create something that’s more useful to our database application. How about something that gracefully handles errors? Try this:

01 <html>
02 <body>
03
04 <?php
05
06 function  do_error($error) {
07
08    echo  "Hmm, looks like there was a problem here...<br>";
09
10    echo "The reported error was $error.n<br>";
11
12    echo "Best you get hold of the site admin and let her know.";
13
14    die;
15 }
16
17 if (!$db = @mysql_connect("localhost","user", "password")) {
18
19    $db_error = "Could not connect to MySQL Server";
20
21    do_error($db_error);
22
23 }
24
25 ?>
26
27 </body>
28 </html>

Before running this, try shutting down MySQL or using a bogus username or password. You’ll get a nice, useful error message. Observant readers will notice the @ symbol in front of mysql_connect(). This suppresses error messages so that you get the information only from the function. You’ll also see we were able to pass a variable into the function, which was defined elsewhere.

Remember that I said functions use their own private variables? That was a little white lie. In fact, you can make variables outside of a function accessible to the function. You might create a function to query a database and display a set of results over several pages. You don’t want to have to pass the database connection identifier into the function every time. So in this situation, you can make connection code available as a global variable. For example:

01 <html>
02
03 <body>
04
05 <?php
06
07 function  db_query($sql) {
08
09    global $db;
10
11    $result = mysql_query($sql,$db);
12
13    return $result;
14
15 }
16
17 $sql = "SELECT * FROM mytable";
18
19 $result = db_query($sql);
20
21 ?>
22
23 </body>
24
25 </html>

This is a basic function, but the point is that you don’t need to send $db through when you call the function – you can make it available using the word global. You can define other variables as global in this statement, just separate the variable names by a comma.

Finally, you can look like a real pro by using optional function variables. Here, the key is to define the variable to some default in the function, then when you call the function without specifying a value for the variable, the default will be adopted. But if you do specify a value, it will take precedence.

Confused? For example, when you connect to a database, you nearly always connect to the same server and you’ll likely use the same username and password. But sometimes you’ll need to connect to a different database. Let’s take a look.

01 <html>
02 <body>
03 <?php
04
05 function  db_connect($host = "localhost", $user="username", $pass="graeme") {
06
07    $db = mysql_connect($host, $username, $password);
08
09    return $db;
10
11 }
12
13 $old_db = db_connect();
14
15 $new_host = "site.com";
16
17 $new_db = db_connect($new_host);
18
19 ?>
20
21 </body>
22
23 </html>

Isn’t that cool? The variables used inside the function were defined when the function was defined. The first time the function is called, the defaults are used. The second time, we connect to a new host, but with the same username and password. Great stuff!

Think about where you could use other functions in your code. You could use them for data checking, performing routine tasks, and so on. I use them a lot when processing text for display on a Web page. I can check, parse, and modify the text to add new lines and escape HTML characters in one fell swoop.

Now all that’s left to do is to impart some words of wisdom.

 

Closing Advice

When it comes to databasing, there’s a lot to learn. If you haven’t done it already, find a good book about database design and learn to put together a solid database – on any platform. It’s an invaluable skill and it will save you plenty of time and headache in the long run. Also, learn about MySQL. It’s a complex but interesting database with a wealth of useful documentation. Learn about table structure, data types, and SQL. You can actually achieve some pretty impressive stuff if you know enough SQL.

Finally, there’s PHP. The PHP Web site has nearly everything you need, from a comprehensive manual to mailing-list archives to code repositories. An excellent way to learn about PHP is to study the examples used in the manual and to check out the code archives. Many of the posted scripts consist of functions or classes that you can use for free in your own scripts without having to reinvent the wheel. Additionally, the mailing list is an excellent spot to check out if you get stuck. The developers themselves read the list and there are plenty of knowledgeable people there who can help you along the way.

Build a Website With Flash and MySQL – Lesson 1

There’s always been somewhat of a disconnect between designer-types and backend-types. And we’re not just talking about hairstyles, eyewear, and the contents of their bookshelves — designers and programmers approach problems in different ways, and their individual plans of attack don’t always jibe. Of course, you want your site to be sleek, fast, and bleeding-edge, but are you willing to compromise stability, scalability, and compatibility just so the users can ooh and aah at a few pretty pictures? Web design has always been a balancing act, and the ideologies of each camp often clash.

But Webmonkey’s here to say that it doesn’t have to be that way. It is possible to create a site that has a lightweight Flash frontend, a dreamy interface, and a scalable, secure, and dynamic backend.

Webmonkey Scott has found the balance between eye-catching design and backend database wizardry. Lucky for us, he’s agreed to share this knowledge in a two-day tutorial. He’s even put together a hands-on project that shows you how to build an easy-to-update blog using Flash. That’s right, a Flash-based blog — it sounds a little loony, but this blog pulls content from a MySQL database and feeds it into the dynamic Flash frontend using a few lines of PHP code.

In day one, Scott talks you through the construction of an open source MySQL database using both the phpMyAdmin tool and mysql on the command line. You’ll learn how to add blog entries to the database and then you’ll learn how to run queries in PHP. Topics such as basic database organization and the behavior of variables are also covered in this lesson.

Day two shows you how to design and build the blog’s Flash frontend. You’ll use common Flash ActionScript objects to pull the content from your MySQL database to the different areas of your blog’s user interface. You’ll also learn some common workarounds to keep all of your content flowing smoothly.

Whether you are designer or a programmer, it’s time to roll up those sleeves and get ready to see how the other half lives. Even if those of you who don’t feel a particular loyalty to either side of the fence will still discover that there’s plenty of hands-on knowledge to be gained.

We’re not promising that designers will sell their $500 pencil sharpeners, start marrying UNIX geeks, and honeymooning in Cancun before breeding programmer/designer children, but we will insist that they sit in a room alone together until they can build a useful website that everyone can agree on. Hey, stranger things have happened. Like Carrot Top.

Contents

  1. Building with Flash and MySQL
  2. PHP Basics
  3. Using MySQL
  4. Creating the Database
  5. Entering Data
  6. Add a Dash of PHP
  7. Strings, Sealing Wax, Fancy Stuff

 

Building with Flash and MySQL

From the beginning, the database has been the sole province of programming geeks. For designers, usually geeks themselves, but with backgrounds in color theory or animation rather than code architecture, the jargon surrounding databases is often confusing, overwhelming, and intimidating. Even so, the irony is that designers are sometimes the very people who can benefit the most from using a database.

Many designers can create eye-popping sites full of life, but it’s rare to find a designer who can sustain that initial vibrancy as the site ages. How often have you seen an amazing-looking site that was created, hyped, then abandoned?

Database integration can propel a website from a well-designed but infrequently updated bore to a dynamically generated, updated-on-the-fly extravaganza for the mind. Connecting your design to content driven by a database gives you or your clients a site that can be updated easily without ever needing to touch the design elements themselves. Also, this separation of content and container will make your job as a designer much easier. With the continuous flow of timely content from your easy-to-update database, your site will stay fresh and youthful. It works better than any of those creams from France, anyway.

As a good designer, you’re capable of translating loosely held, often ill-conceived ideas into the sites we all use and enjoy. What’s more you make them look good. But perhaps dealing with raw code is not your forte. Not to worry, it’s not as difficult as you might think.

To get the full benefits a database offers, we’re going to feed our content into a Flash movie rather than an HTML page. Why would you want to do that, you ask? As a seasoned Web surfer, you’re probably familiar with the so-called “blink” of HTML. The process goes a little something like this:You click a link, the link then sends a command (often via PHP, which we’ll use here) to a database to retrieve whatever information you’re after. Once the database has retrieved your information, it sends that info back to the page that requested it. Now, if that page is HTML, your browser must weed through the results and shape them accordingly. But, before it can do any of that, the browser must first empty out the old page and create that momentary blank browser window – the blink of HTML.

This doesn’t happen with Flash. Flash can load the data into the very same place from which it was requested without a visible refresh. No blink. No redrawing of elements that haven’t changed. In short, a smoother, less distracting transition for your user which leads to a better, more enjoyable viewing experience.

Everybody convinced? Then let’s get started.

 

PHP Basics

If you’ve worked with databases before, even if you’ve always fed the content into HTML pages, you’ll have a head start here. I’ll be assuming that you have MySQL and PHP installed and running on your server. If you’re going to be doing this through your Web hosting company, check to see if they already have the necessary software installed. If you find that you need to install either program, check out PHP and MySQL Tutorial, which will also be a helpful read for those of you who’ve never worked with PHP since it goes into a little more detail on the syntax. The homebase for MySQL has a full manual available for download, as does the official PHP site. Take advantage of these free resources. Many hardworking volunteers have created them for you, and answers to most nitty-gritty questions can be found on one of those sites.

If your website is hosted by someone other than yourself, you will have to ask your hosting company how to connect and interact with your database. Most good hosting companies will have you interact with your database via your Web browser and the phpMyAdmin program. The application will give you a nice graphical interface for creating tables and entering information. Also, phpMyAdmin shows you the actual MySQL syntax for your actions, which will let you get a better feel for how MySQL works. Really good hosting companies will let you connect directly via a secure shell or some other graphical program of your choice.

There are many other graphical ways to interact with MySQL, but for the purpose of this tutorial, I will be giving examples from a terminal interface using the mysql client program, which comes with the MySQL package.

Why use a terminal when GUIs of all shapes and sizes are available? Graphical interfaces may allow you to interact and manipulate data, but they often do so without letting you understand exactly what it is you’re doing. Typing the commands in a terminal session will help you not only learn the SQL syntax but understand how and why you’re doing what you’re doing. Knowledge of the SQL syntax will give you a better understanding of how to use PHP to manipulate SQL data. And what are you going to do when you land that dream project you spent months pitching only to learn you aren’t going to have access to a GUI? Over-reliance on GUIs is not the monkey way. Don’t let the command line intimidate you.

To take full advantage of the organization a database offers, it helps to sit down ahead of time with a pen and piece of paper (made from trees … or even better, recycled material … like in high school) and plan out your database. Databases are simply means of organizing information into tables. Tables are made up of columns and rows. Typically each vertical column is some category of information and each horizontal row is an individual instance of that information. Too abstract? Think of your monthly bank statement. If it’s anything like mine, it’s a simple table (full of zeros). Across the top are column definitions (check number, payee, amount, et cetera). Reading horizontally, each line contains values that are given meaning by the column they’re in. MySQL databases work very much the same way. We create tables, define columns, and enter rows of data. The main difference is that our information will be searchable by any criteria we define.

Unfortunately, those MySQL searches cannot be run directly from your browser. Thus, while we can enter the commands and search in our terminal (or GUI bad monkey, no banana), we can’t write some HTML code and duplicate those search results. But that’s OK, because there are other languages that canduplicate them. The two main languages used in a database search, or query, are PHP and Perl. For our purposes, which will be to deliver Web content to Flash, we’re going to use PHP. PHP was developed to specialize in database connections on the Web.

Chances are many of the sites you visit everyday are generated using PHP. You’ve been seeing the benefits without ever knowing what was going on behind the nicely formatted information you receive. How did PHP give you your information without you even knowing it was doing so? If you view the source of a PHP page you will only see HTML. Where the heck is the PHP and how does it do what it does? We’ll get to that part a little later. Right now let’s dive into MySQL and feed it some information.

 

Using MySQL

Databases are serious stuff my friends, and databases call for serious examples. When I think of seriousness and integrity on the Web, the first thing that comes to mind is the weblog. Ahem.

Anyway let’s suppose you’re one of the two people who don’t yet have a blog. Or even better, let’s suppose you want to create a weblog that’s a little different from the endlessly repeated templates you see everyday. Let’s suppose for a moment that you want to create your very own Flash-based weblog to tell the world about your ever-growing collection of florescent feather boas or your serialized, Charles Dickens-style Great American Novel (Great British Novel just doesn’t have the same ring, sorry). Whatever it is, you plan on telling the world something about it each and every day.

Well then, let’s think about this for a moment. Your user interface will be a Flash .swf file that will display content from your MySQL database. How do you want to organize your data? That’s really up to you. The simplest approach is the date-based model that most weblogs out there function on:one date, one title, one entry. Our simplest case calls for a table that contains the following information:

Flash_MySQL_chart1.gif

Before we run off to create this table, let’s first look at some potential problems with our mock-up (remember: measure twice, cut once). While this table meets our most basic needs, we might want to include a few other things. Let’s say for instance that down road, when we get more sophisticated with our site, we want to be able to search according to subject. Well, right this minute we aren’t going to bother with a subject column, but in the future we might want to add a whole separate table to contain metadata of this sort. To link a new table to this one we’d need an index column. So our mock-up should include an ID column which will cover us if we’d ever like to add to our database. We’ll also be using the ID column to simplify the process of finding the newest entry.

Flash_MySQL_chart2.gif

Alright, we’ve done all of our paperwork. Our database-to-be looks pretty robust, so let’s go ahead and create it.

 

Creating the Database

For this example, I’m going to assume you’re using the mysql client (note the all lowercase mysql, this denotes the client program that comes free with MySQL) in the command line. To fire up the mysql client, start up a new terminal window and login to your server using the format:/Path/To/mysql/ -h yourHostname -u yourUser -p. Enter your password when prompted. Now we’re going to issue the following commands (and don’t forget your semicolons):

1 mysql> CREATE DATABASE blog_db;

This command tells MySQL to create a new database named blog_db (you can give it whatever name you like, just adjust everything accordingly). Now switch to the new database.

1 mysql> USE blog_db;

You can verify that you’re actually using the blog_db by issuing a SELECT DATABASE() command which will tell basic information about our newly created blog_db database, namely, that it contains nothing. An empty database is a useless database, so let’s create a table. Your input will look like this:

01 CREATE TABLE blog_entries (
02
03
04
05  id INT NOT NULL AUTO_INCREMENT ,
06
07
08
09  date DATETIME NOT NULL ,
10
11
12
13  title VARCHAR (30 )NOT NULL ,
14
15
16
17  entry LONGTEXT NOT NULL ,
18
19
20
21  PRIMARY KEY (id)
22
23
24
25  );

OK what the hell is all that gibberish? Let’s walk through it one statement at a time.

The CREATE statement should be obvious:It creates a new table by the name of blog_entries. Next, we fill out our table. But we don’t want just a table, we want to define our four columns that we’re going to use to organize our data. So, “id” is the first column and will be an integer, defined by INT(). The NOT NULL statement simply means that each row must have a value for this column. As you can see, I’ve set the table up to require values for all the columns. If you need some rows of a table left blank, simply change the statement to NULL which will allow blanks, or “null values” in database speak. The AUTO_INCREMENT statement tells MySQL to assign a new value for each row and to increment that number each time we enter another row. In other words, the first entry will get a value of 1, the second 2, and so on. This way, we don’t have to keep track of what entry number we’re on; we let MySQL do it for us. This will allow us to always find the most recent entry by querying for the largest number. This information will come in handy when we start writing queries in PHP.

Next is DATETIME, a predefined format that will have MySQL stamp each entry with the current date and time. VARCHAR, on the next line, is short for variable character. This means that entries in the title column will be filled with characters of varying length. I chose to cut them off at 30 characters, but you can specify any number you’d like (up to 255). The last datatype we’re using is LONGTEXT. LONGTEXT allows up to 4,294,967,295 characters. That’s over one million words per entry — possibly overkill for our purposes, but I thought we’d play it safe. You could also use MEDIUMTEXT if you’re feeling less wordy than me. PRIMARY KEY is somewhat more complex, and I’m not going to explain it here, other than to say that if a column is AUTO_INCREMENTed it must be a primary key.

Whew! Alright, just to make sure that MySQL created things the way we expect, issue the command DESCRIBE blog_entries and you should see the following:

Flash_MySQL_chart3.gif

Notice the date format is year-month-day. Putting the year first takes some getting used to, but this is how MySQL handles dates — you’re just going to have to keep that in mind. We’re good monkeys, flexible and capable of adaptation.

 

Entering Data

Okay, we have our table all set up. Now we just need to enter some data. Entering information into MySQL can be very laborious when done by hand. For this reason, extensions were written to read from tab-delimited text files. However, since our table is pretty simple, and it’s unlikely that we’ll be entering more than one row at a time, I’m going to go ahead and enter things the old fashioned way. When your data gets more complex, you’ll want to investigate how to enter from external files. A good starting point would be the MySQL site.

01 INSERT INTO blog_entries VALUES
02
03
04
05 (
06
07
08
09 '',NOW(),'my first blog entry','your text here'
10
11
12
13 );

The main thing to notice here is that the first value is blank. The value for the ID column tells MySQL to go ahead and insert the auto increment number, in this case, the integer one. The NOW() function gives us the current date and time. If you wanted to forge an older or newer date and time, you could enter them by hand in the format discussed earlier (YYYY-MM-DD 00:00:00). Double check to see that everything looks the way it should by issuing your first real query:

1 SELECT * FROM blog_entries;

and you should see:

Flash_MySQL_chart4.gif

I went ahead and inserted a few more entries to give us more to play with when we get to the Flash end of our blog. See, that wasn’t too bad was it? Alright, maybe a little, but take a deep breath and move on to the PHP anyway.

 

Add a Dash of PHP

When a user arrives at our blog, we want the following two things to happen:First, we want the latest entry to be displayed. Second, we want to display a list of past entries, which will be links to our older postings. I’m going to make the archives show both entry and date so you can see how both methods would work. The information we need to retrieve is stored in our table named “blog_entries”, which is part of the database named “blog_db”. The Flash piece we’re going to build will need to query the database for that information, and to do so, we will utilize a PHP file.

If you’re familiar with Flash ActionScript, PHP should look familiar to you. Like ActionScript, PHP is a scripting language and therefore has a similar syntax. The one big difference from ActionScript is that PHP variables must always begin with a $. Just like in Actionscript, we’re going to write some functions and use them to interact with our blog_db database. The reason I spent so much time on the MySQL section is that the commands we used there to see the entries in our database are the same as the ones we’ll use in our PHP scripts. For our purposes, PHP is going to act as a middleman, sending queries from Flash to MySQL and then returning results back from MySQL to Flash.

So, diving right in. Fire up your favorite text editor, create a new document and save it with the extension .php. Now enter in the following code. (Your browser will probably wrap this code, so try copying the raw source)

01 <?php
02
03 mysql_pconnect ("yourHost", "yourUserName", "yourPassword");
04
05
06
07 mysql_select_db ("blog_db");
08
09 $qResult = mysql_query ("SELECT * FROM blog_entries ORDER BY id DESC");
10
11
12
13 $nRows = mysql_num_rows($qResult);
14
15 $rString ="&n=".$nRows;
16
17
18
19
20
21 for ($i=0; $i< $nRows; $i++){
22
23     $row = mysql_fetch_array($qResult);
24
25     $rString .="&id".$i."=".$row['id']."&"."&title".$i."=".$row['title']."&"."&date".$i."=".$row['date']."&"."&entry".$i."=".$row['entry']."&";
26
27 }
28
29 echo $rString."&";
30
31
32
33 ?>

Now let’s go through it line by line. The first few lines are PHP’s way of connecting to our database, just like what we did earlier in our terminal program. How does this work you ask? PHP, like ActionScript, has a bevy of built-in functions, and one of them is mysql_pconnect(). So then it’s just a matter of plugging in the right parameters for our database. The mysql_select_db() function is just like the USE function we typed earlier in our terminal. It tells MySQL which database we want to query. Then we have these lines:

1 $qResult = mysql_query ("SELECT * FROM blog_entries ORDER BY entry_id DESC");
2
3
4
5  $nNows = mysql_num_rows($qResult);
6
7
8
9  $rString = "&n=".$Nrows;

Here we’ve created three variables (remember variables in PHP must begin with a $). The variable $qResult is our actual query to the database (qResult is short for Query Result — get it?). The parameter is a string which tells MySQL to select all the information in our blog_entries table and return them in an ordered list. The ORDER BY command will order our result by the id column, starting with the largest number and reading down in descending order. This means that the rows of data returned start with the newest entry and read down to the oldest. Now you can see why we used the auto increment feature when we set up our table.

The variable $nRows uses the function mysql_num_rows() to store the number of rows in our database (nRows is short for number of rows). We need to know how many rows we have so that we can write a loop to cycle through and read each row. Then we will write some code to format each row into a URL encoded string, which we will then pass on to our Flash movie. $rString is the variable we will use to pass the data to Flash (rString being my shorthand for Returned String). Next comes the loop to read all the data in our table.

01 for ($i=0; $i< $nRows; $i++){
02
03
04
05     $row = mysql_fetch_array($qResult);
06
07
08
09     $rString .="&id".$i."=".$row['id']."&"."&title".$i."=".$row['title']."&".
10
11
12
13     "&date".$i."=".$row['date']."&"."&file".$i."=".$row['entry']."&";
14
15
16
17  }
18
19
20
21  echo $rString."&";

“Oh my god!” you scream, “What is that string of nonsense?” Good question.

The first thing we’ve done is set up a for loop. This loop says:for a variable $i, which is initially equal to zero, and for as long as $i remains less than our variable $nRows, increment the variable $i by one each time you perform the following loop. We do this so that PHP will loop through each row of our MySQL table and give each row its own unique number. This way when we pass the information to Flash, we can separate each entry and send it to the appropriate place.

Next, we’ve taken advantage of another built-in PHP function (don’t worry — it won’t sue). We’ve created the variable $row and assigned it the function mysql_fetch_array(). Our parameter is the variable $qResult which we defined earlier as our query. This function takes the long string of information that is our variable $qResult and breaks it into an array. The new array contains all our table columns and the rows of data. Using the $row array we can access each element by name. Let’s just pause for a moment and consider that there are brilliant programmers out there who made this possible with one simple line of code.

Done pausing? Great, let’s move on.

 

Strings, Sealing Wax, Fancy Stuff

OK now let’s concentrate on that really long and confusing line, the meat of our for loop. It turns out that Flash has to receive external information in the form of a URL-encoded string (actually, if you install Flash Remoting on your server, it can receive information as straight record sets, but Flash Remoting is beyond the scope of this article — visit the Macromedia site if you’re interested in learning more). The string our Flash movie will be looking for is a bunch of incremented variables squeezed between ampersands. If we include linebreaks to make it more readable it looks like this:

01 &id0=1&
02
03
04
05  &title0=myfirstEntry&
06
07
08
09  &date0=2003:10:10 03:30:02&
10
11
12
13  &entry0=some text&

The variable $rString in our PHP code contains all the necessary formatting syntax to ensure that Flash gets this string. The PHP code looks confusing because we’re concatenating (joining together) strings (everything in quotes) with elements in our $row array.

Let’s break it down some more. In PHP we write (I’ve put extra spaces between each element to make it easier to follow):

1 $rString.=  "&id".  $i.  "=".  $row['id'].  "&"  et cetera...

$rString is our PHP variable which will get passed to Flash. We’ve assigned it to a big long concatenated string of smaller strings and elements of the array $row. In PHP the . (dot) operator is used to concatenate elements of a string. “&id” starts our string off and gets joined with the variable $i. Now the variable $i is the incremented element in our loop, so it will be an integer. Because we set $i=0 in our loop, the first time through the loop it will be 0, the second time it will be 1, and so on. For this reason, each row in our MySQL table has a unique identifying number. We then join the $i variable with an equals sign. So far we have given Flash the following information:

1 &id1=

Now we assign this newly created “id” variable an element in our $row array ($row[id]). Essentially we’re writing id=id, pretty simple right? The first number in our MySQL column was 1, so the first time through our loop we return a 1. We then tack on another string so that this element of our big string (the one being passed to Flash) is enclosed in ampersands the way we want it to be. All Flash is going to see is:

1 &id1=1&

The rest of our variable $rString does the same formatting for the rest of our columns so that we can pass Flash our completed string which will look like this:

1 &id0=1&&title0=myfirstEntry&&date0=2003:10:10 03:30:02&&entry0=some text&

Now for the final and really easy bit of code:

1 echo $rString."&";
2
3
4
5  ?>

Since Flash is going to read the results of our PHP code, we have to make sure that we use the echo command, to tell PHP (when it gets done processing all the things we’ve told it to do) to display the completed string shown above. Echo is the same as print. In fact, you can use the command print if you so desire. The ?> simply closes our PHP code just like  /html¢ would close an HTML document.

To test and make sure everything is working, upload the .php file to the server and point your browser to its location. You should be greeted by a stark white page with unformatted black text that reads:

1 &id0=1&&title0=myfirstEntry&&date0=2003:10:10 03:30:02&&entry0=some text&

Okay, you’re done. With PHP that is. Time for a break. Crack your knuckles, lean back, smoke ‘em if you got ‘em, and relax. That was some terribly complicated stuff you just waded through.

A quick note about security:Did it make you nervous to type your password in plain text at the beginning of our PHP file? It should. Granted, PHP is a server-side technology and there is no easy way to view this file as we’re seeing it. But that doesn’t mean it can’t be done. A more secure method of sending this information is to use an include statement. What we do is take the mysql_pconnect statement and move it to a separate file. Name it connect.inc, and now move it to a location on your server where the general public can’t access it. Then change the mysql_connect statement above to read include (“connect.inc”). Also, don’t forget to tell PHP where the include file is. We need to edit the PHP initialization file to point to the directory in which you just placed the include file. Typically, the initialization file lives in the/usr/local/apache/php directory. If your server isn’t running Apache, refer to your documentation. Not only is this method more secure, it saves you from having to type the whole connect statement every time you write a new PHP document for this database.

Build a Website With Flash and MySQL – Lesson 2

In Build a Website With Flash and MySQL – Lesson 1, we successfully created a MySQL database, filled it with blog entries, and learned how to query it. Now we’re going to move on to the fun stuff:creating a Flash container to display our blog entries as we pull them out of the database. I’m going to help you build something along the lines of what you’ll encounter at my own Flash blog site, Luxagraf.

Fire up Flash and create a new document. The first thing we need is a nice big text field to display our entries. You could create a text field in ActionScript if you like, using the createTextField() method. I don’t know about you, but my stomach for code is still full from yesterday, so I’ve just drawn a text field using the cursor tool and given it an instance name of entries_txt. In the Properties Inspector, set the text to be dynamic, multi-line, and HTML formatted. For safety’s sake, select the character option and click “embed all characters.” Flash has some issues with dynamic textfields that don’t have embedded characters,

Sunlight Labs Offering $5K for Best Government Data Mashups

Artists, web developers and data visualization geniuses, here’s a chance to strut your stuff, serve your country and win some serious money in the process.

Sunlight Foundation, a non-profit organization that provides tools to make government data more transparent, has announced a new contest called Design for America. Billed as a “design and data visualization extravaganza,” Sunlight is encouraging the public to create and publish data visualizations that help make complex government data easier for people to digest and interact with.

There are several different categories open for submission, including: visualizations of Recovery.gov data that shows how the stimulus money is being spent, visualizations showing how a bill becomes a law, a redesign of a .gov website, and a redesign of any government form. Top prize in each category is a cool $5,000.

Creations can be in any form — a website, a game, a poster, a sculpture, whatever — though we suspect most of the entries will be either posters or interactive Flash graphics.

The contest is being run by Sunlight Labs, the skunkworks wing of the larger Sunlight Foundation. The Sunlight group spends most of its energy collecting government data, organizing it into publicly accessible databases, then creating tools that make it easier for ordinary people to access that data. The non-profit works with organizations like OpenCongress, MapLight, FollowTheMoney and USASpending.gov. Sunlight also maintains a list of APIs developers can use to access the data.

The Design for America contest encourages participants to sift through the vast datasets available from all of these organizations, as well as the datasets maintained by Sunlight Foundation and any raw government data that’s available. As the Sunlight Labs blog says, the goal of the contest is to “tell interesting stories” that go beyond what can be an overwhelming amount of unfiltered data.

Visualizations can be in any medium, not just the web, so if you’re a video or infographic specialist, you can still enter the contest. The main criteria for judging are the visual quality of the artwork and how well the underlying information is conveyed.

Open Data’s Access Problem, and How to Solve it

The recent Gov 2.0 summit in Washington D.C. saw several promising new announcements which will help government agencies share code and best practices for making public data available to developers.

The idea behind new projects like Challenge.gov, the FCC’s new developer tools and the Civic Commonsis that by giving developers access to data previously stored in dusty filing cabinets, they can create tools to give ordinary citizens greater access to that data.

Unfortunately, not everything open data project leads to good things. It is critical that if open data is made available on the web, it must be accompanied by some effort to ensure everyone can access it.

We’ve seen an explosion in creative hacks that use this newly available data to provide excellent online resources. Public data sites like EveryBlock, or the Sunlight Foundation’s Design for America contesthave highlighted some of the amazing ways open data can make our lives better. Whether it’s finding out crime stats, real estate values, health hazards and business license statuses in your neighborhood, or visualizing how the government is spending your tax dollars through innovative maps, open data and what you can do with it is the current hotness among web developers.

Most of the benefits are close to home — in the U.S., just about everyone has access to online government resources thanks to web-enabled computers in free public libraries.

But extend that argument to the rest of the world and the number of people that really have access to the data drops significantly. If you don’t have an easy way to get online, you can’t benefit from open data.

Michael Gurstein, Executive Director of the Center for Community Informatics Research, recently highlighted some of the problems with open data accessibility.

Gurstein points out a number of assumptions about open data that are often overlooked by those most enthusiastic about making such data publicly available.