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!