PHP/MySQL shortcut

PHP makes it easy to write information to a MySQL database and to read data from it. This, in turn, makes it easy to create dynamic, database-driven web pages. All the content unique to a page is in the database. Your page is just a template with placeholders (variables) for the specific items.

When you read data from the database (using a SELECT statement), you get an array of values for one or more rows in a database table. These arrays are indexed by numbers, keys(field names) or both. I prefer the key type of index. That way when I get the result, the values can be assigned to variables with the same names as the keys.

If you had these field names: city, state, zip ... you'd get these variables: $city, $state, $zip with the values from those fields assigned to them. This simplifies taking in data from forms and getting it back out - IF you rememeber to use the same field names in the table as you did in the form.

You can get the data from one row and assign it to variables one at a time like this:

$query = "SELECT * FROM table WHERE id='$id'";
$result = mysql_query($query) or DIE("bad query");
$row = mysql_fetch_array($result);
$city = $row[city];
$state = $row[state];
$zip = $row[zip];

If the query will return more than one row, your code would look more like this:

$query = "SELECT * FROM table";
$result = mysql_query($query) or DIE("bad query");
while($row = mysql_fetch_array($result)) {
  $city = $row[city];
  $state = $row[state];
  $zip = $row[zip];
  # Do something with these variables now; they'll have
  # different values on the next pass through the loop!
}


Now if you had ten fields in the table, the code above would have more lines like:

$variable = $row[variable];

If you added a field to your table, you'd have to add another line to your code to get its value and assign it to a variable. Likewise, if you changed the name of a field, you'd have to modify your code. Not only that, but the code has to be written specifically for every SELECT query. It's generally not going to be re-usable.

Here's my solution. It uses a foreach-loop to create the variables and assign values to them. (This is for extracting data from one row.) :

$query = "SELECT * FROM table WHERE id='$id'";
$result = mysql_query($query) or DIE("bad query");
$row = mysql_fetch_array($result);
  foreach($row as $key=>$value) {
    $$key = $value;
  }

For more than one row, use this:

$query = "SELECT * FROM table";
$result = mysql_query($query) or DIE("bad query");
while($row = mysql_fetch_array($result)) {
  foreach($row as $key=>$value) {
      $$key = $value;
    }
  # Do something with these variables now; they'll have
  # different values on the next pass through the loop!
}

On each pass through the foreach-loop, a key and a value are pulled from the $result array. The key name is pulled out as $key (for example, $key = city). The key isn't a variable yet, so we put another $ in front to make it a variable (as before but now $$key = $city). Assign the value $value (for example, $value might be "New York") to $city and it's ready to be used anywhere you need the city becuae now $city = "New York".

Now that's progress! If you change the number of fields or the name(s) of any field(s) - no problem. The code will handle the change without any rewriting. Sure, you still have to write new code or modify existing code to use the new or changed variable - but that's another story.

To make this code even more re-usable, replace the table name in the SELECT statement with a variable such as $table:

$query = "SELECT * FROM $table";

Now you just assign the actual table name before the MySQL code is called, like this:

$table = "addresses";

This one tiny chunk of code can now read any number of tables with any number of fields inside a single script without any modification at all. Tip: make and keep a text file with re-usable snippets like this, along with a short description of what each one does. Then just copy and paste them, as needed, into new scripts you write.

Happy Coding!

More formatting tips

I just answered an email from a friend who had a problem getting the formatting with printf() to work properly. Using printf() and sprintf() can be a little tricky at times so I've made a page with 3 examples of using printf() to print out some formatted text. I'll give you the URL shortly. For now, let's look at John's problem:

(From the email I received)

"I've written a simple little program that outputs
the following:

Free space on drive C: 103.67 GB
Free space on drive C: 106154.02 MB
Free space on drive C: 108701716 KB
Free space on drive C: 111310557184 bytes

Now what I would like for it to do is look like this:
Free space on drive C: 103.67 GB
                       106154.02 MB
                       108701716 KB
                       111310557184 bytes

... instead of getting the above I get this:

Free space on drive C: 103.67
106154.02 MB
108701716 KB
111310557184 bytes

How, then, can I easily achieve the effect of printing
blank space when it must precede anything else on a 
line of PHP output?"

The answer, while simple, isn't obvious at all. Here's what happens: The printf() statements he used were correct but the browser ignored the extra spaces. Why? Because HTML only shows one space where it encounters several together - unlesss you specify that you want things another way.

The <PRE> ... </PRE> does that for you. By default, you'll get small text in a monospace font. If you want to change the appearance (size, etc.) you can put a <FONT> ... </FONT> in between the <PRE> ... </PRE> tags.

Like this: <PRE><FONT size=4> ... </FONT></PRE>

Be careful not to use a proportional font. If you do, the characters will have different widths and the spaces won't be as wide as you expect. You'll see how bad this can look when you go to my example page.

I took the example from php.net and made a page that shows the code and the output in three different variations. Two of them look OK; one looks awful.

OK, I've kept you in suspense long enough. Here's the URL:
www.learntousephpintwohours.com/monkey_printf.php

Happy Coding!

Formatting output

PHP was designed specifically for the World Wide Web and its screen output is formatted as HTML. If there's an exception to that, I haven't seen it yet. But HTML doesn't always appear on the screen the way you want it to. Well, not without a little help, anyway.

Let's say you wanted to put a specific number of blank spaces in your output. (Don't forget that the width of a space isn't always the same unless you specify a monospace font...). The problem with most HTML code is that it converts a string of spaces to a single space unless you tell it otherwise.

There's a tag you can use for "preformatted output" that will override the tendency of HTML to eat the extra spaces. Here's an example where I'm showing a script and I want the indenting:

<PRE>
function runForever() {
  if(6 == 9) {
    print("Warning! Infinite loop");
  }
}</PRE>

Everything between the <PRE> and </PRE> tags has the same spacing and line feeds as it would have coming out of a word processor or typewriter. To be able to show you the <PRE> and </PRE> tags themselves, I had to enter them using HTML entities so they wouldn't be interpreted as tags!

Other ways to format text on the screen include using printf() and sprintf() just like in the C language. You can make numbers and text appear in a field of a certain width, choose a character to pad out the unused spaces and make your numbers appear as integers(binary, decimal, hex or octal), doubles (floating point values such as 3.14159) or doubles in scientific notation.

Text can be right- or left-justified and you can pass ASCII codes for characters and get the characters on the screen. Full documentation and a lot of good examples can be found here:

http://us3.php.net/manual/en/function.sprintf.php

Happy Coding!

Beware of infinite loops

Loops are a great way to get a repetitve task done in a PHP script. There are several kinds of loops: for-loop, foreach-loops, while-loops and do .. while-loops. The all have one thing in common - a stop condition.

A stop condition is an expression that, when it evaluates to true (or any non-negative number), will stop the loop from executing.

Here's a typical for-loop:

for($c=0; $c<10; $c++) {
  # do something here
}

This loop will run ten times while $c goes from 0 to 9. Now what would happen if you wrote a stop condition that was never reached? You guessed it. The loop would run forever. Here's an example:

for($c=0; $c<0; $c++) {
  # do something here
}

Here, $c gets bigger with each pass through the loop but the stop condition is that $c must be less than zero. That's never going to happen and the loop never quits. Why? Because $c starts at 0 and gets increased by 1 on each pass through the loop.

What about a while-loop? They look like this:

$c = 0;
while($c < 10) {
  # do something
  $c++;
}

This loop will stop when $c gets to 10. Now suppose you made this subtle typo:

$c = 0;
while($c < 10) {
  # do something
  $C++;
}

$c never changes! $C keeps getting bigger but this loop doesn't check for that.  So the loop never stops.

If a script seems to be "stuck" and not doing anything - or just not doing what it should be doing - force it to stop, then look for infinite loops. If you see one you're not sure is messed up, try putting an echo() line in the loop body so it prints something to the screen each time the loop executes. You'll see right away if you've found the problem because your screen will fill up and keep growing.

Happy coding!

PHP and the PSP?

Shazaam! Recently built Playstation Portables (PSPs) have a web
browser and wireless access built right in. But what's that got
to do with PHP? Plenty, as you'll soon see.

To take advantage of this phenomemon, you'll need web pages
optimized for the PSP's 480-pixel-wide screen. Next, you'll need
to detect when a visitor found your site with his/her PSP. That's
where the PHP bit comes in.

This code: (Replace the [ and ] with angle-brackets.)

[?php
if(eregi("PlayStation Portable", $_SERVER['HTTP_USER_AGENT']) ) {
header("Location: http://www.yoursite.com/psp/index.html");
die();
}
?]


will detect the PSP browser and redirect the visitor to a different
page that looks good on a PSP. All links on that page should go to
other pages that were also made PSP-friendly.

The code needs to be placed at the top of your normal index page
or any other page where the visitor might land on your site.

Happy Coding!