DJS Consulting Tech Blog
Technical Information You Can Use (or not - but hey, at least it’s free!)

I found a script over on the Lunarpages Forums about using PHP to back up your site. I have taken it, modified it a little, beefed up the documentation a lot, and am now posting it here. You can download the source code for it, and it is also displayed below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102

<?php
/**
 * Generic Backup Script.
 * 
 * To configure this script for your purposes, just edit the parameters below.
 * Once you have the parameters set properly, when the script executes, it will
 * create an archive file, gzip it, and e-mail it to the address specified.  It
 * can be executed through cron with the command
 * 
 * php -q [name of script]
 * 
 * You are free to use this, modify it, copy it, etc.  However, neither DJS
 * Consulting nor Daniel J. Summers assume any responsibility for good or bad
 * things that happen when modifications of this script are run.
 * 
 * @author Daniel J. Summers <daniel@djs-consulting.com>
 */
 
// --- SCRIPT PARAMETERS ---
 
/*  -- File Name --
	This is the name of the file that you're backing up, and should contain no
	slashes.  For example, if you're backing up a database, this might look
	something like...
$sFilename = "backup-my_database_name-" . date("Y-m-d") . ".sql"; */
$sFilename = "backup-[whatever-it-is]-" . date("Y-m-d") . ".[extension]";
 
/*  -- E-mail Address --
	This is the e-mail address to which the message will be sent. */
$sEmailAddress = "[your e-mail address]";
 
/*  -- E-mail Subject --
	This is the subject that will be on the e-mail you receive. */
$sEmailSubject = "[something meaningful]";
 
/*  -- E-mail Message --
	This is the text of the message that will be sent. */
$sMessage = "Compressed database backup file $sFilename.gz attached.";
 
/*  -- Backup Command --
	This is the command that does the work.
 
	A note on the database commands - your setup likely requires a password
	for these commands, and they each allow you to pass a password on the
	command line.  However, this is very insecure, as anyone who runs "ps" can
	see your password!  For MySQL, you can create a ~/.my.cnf file - it is
	detailed at http://dev.mysql.com/doc/refman/4.1/en/password-security.html .
	For PostgreSQL, the file is ~/.pgpass, and it is detailed at
	http://www.postgresql.org/docs/8.0/interactive/libpq-pgpass.html .  Both of
	these files should be chmod-ded to 600, so that they can only be viewed by
	you, the creator.
 
	That being said, some common commands are...
 
- Backing Up a MySQL Database
$sBackupCommand = "mysqldump -u [user_name] [db_name] > $sFilename";
 
- Backing Up a PostgreSQL Database
$sBackupCommand = "pg_dump [db_name] -h localhost -U [user_name] -d -O > $sFilename";
 
- Backing Up a set of files (tar and gzip)
$sBackupCommand = "tar cvf $sFilename [directory]/*";
 
Whatever command you use, this script appends .gz to the filename after the command is executed.  */
$sBackupCommand = "[a backup command]";
 
// --- END OF SCRIPT PARAMETERS ---
//
// Edit below at your own risk.  :)
 
// Do the backup.
$sResult = passthru($sBackupCommand . "; gzip $sFilename");
$sFilename .= ".gz";
 
// Create the message.
$sMessage = "Compressed database backup file $sFilename attached.";
$sMimeBoundary = "<<<:" . md5(time());
$sData = chunk_split(base64_encode(implode("", file($sFilename))));
 
$sHeaders = "From: $sEmailAddress\r\n"
		. "MIME-Version: 1.0\r\n"
		. "Content-type: multipart/mixed;\r\n"
		. " boundary=\"$sMimeBoundary\"\r\n";
 
$sContent = "This is a multi-part message in MIME format.\r\n\r\n"
		. "--$sMimeBoundary\r\n"
		. "Content-Type: text/plain; charset=\"iso-8859-1\"\r\n"
		. "Content-Transfer-Encoding: 7bit\r\n\r\n"
		. $sMessage."\r\n"
		. "--$sMimeBoundary\r\n"
		. "Content-Disposition: attachment;\r\n"
		. "Content-Type: Application/Octet-Stream; name=\"$sFilename\"\r\n"
		. "Content-Transfer-Encoding: base64\r\n\r\n"
		. $sData."\r\n"
		. "--$sMimeBoundary\r\n";
 
// Send the message.
mail($sEmailAddress, $sEmailSubject, $sContent, $sHeaders);
 
// Delete the file - we don't need it any more.
unlink($sFilename);
?>

While working on the Not So Extreme Makeover: Community Edition site, I came up with an algorithm that simplifies anything else I’ve ever written to deal with this condition. I’ll set the scenario, explain the algorithm, share how I implemented it in PHP, and provide a modification if the scenario is a bit more complicated.

Scenario - You have two parent tables, and a child table with a many-to-one relationship with both parent tables, used to map entries in the two parent tables to each other. For this example, we’ll use these three tables…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE TABLE volunteer (
    vol_id  integer  NOT NULL,
    vol_last_name  varchar(50)  NOT NULL,
    ...etc...
  PRIMARY KEY (vol_id)
);
 
CREATE TABLE r_volunteer_area (
    rva_id  integer  NOT NULL,
    rva_description  varchar(255)  NOT NULL,
  PRIMARY KEY (rva_id)
);
 
CREATE TABLE volunteer_area (
    va_volunteer_id  integer  NOT NULL,
    va_area_id  integer  NOT NULL,
  PRIMARY KEY (va_volunteer_id, va_area_id),
  FOREIGN KEY (va_volunteer_id) REFERENCES volunteer (vol_id),
  FOREIGN KEY (va_area_id) REFERENCES r_volunteer_area (rva_id)
);

Algorithm - The three-step algorithm is as follows…

1. Create a comma-delimited string of IDs for the child table.
2. Delete the IDs from the child table that are not in the list.
3. Insert the IDs into the child table that are not there already.

Implementation - In PHP, if you have an array, it’s easy to come up with comma-delimited list. To get an array of values back in a post, define your fields with “[]” after the name…

1
2
3
4

<input type="checkbox" name="area[]" id="chkArea1" value="1" />
<label for="chkArea1">Do Something</label><br />
<input type="checkbox" name="area[]" id="chkArea7" value="7" />
<label for="chkArea7">Do Something Else</label>

Here’s the PHP code, using PHP Data Objects (PDO) as the database interface, behind a helper class that creates the statement, appends the parameters, and executes it. (The “quoting” escapes the statement to avoid potential SQL injection attacks - putting it in its own class would make the implementation here much cleaner.)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

/**
 * STEP 1
 *    Create a comma-delimited list of IDs.
 */
 
// Quote will return the string as '2,3,4' - since we're using this
// as an IN clause of integers, we'll strip the quotes off.
$sAreas = $pdo->quote(join(",", $_POST["area"]));
$sAreas = substr($sAreas, 1, strlen($sAreas) - 1);
 
// Quote the volunteer ID.
$iVol = $pdo->quote($_POST["vol"], PDO::PARAM_INT);
 
/**
 * STEP 2
 *    Delete the IDs that are no longer in the list.
 */
$dbService->executeCommand(
    "DELETE FROM volunteer_area
    WHERE   va_volunteer_id = ?
        AND va_area_id NOT IN ($sAreas)",
    array($iVol);
 
/**
 * STEP 3
 *    Insert the IDs that are not yet in the list.
 */
$dbService->executeCommand(
    "INSERT INTO volunteer_area
        SELECT $iVol, rva_id
        FROM r_volunteer_area
        WHERE   rva_id IN ($sAreas)
            AND rva_id NOT IN
            (SELECT va_area_id
            FROM volunteer_area
            WHERE va_volunteer_id = ?)",
    array($iVol));

Modification - Suppose that now you accepted comments along with each of the checkboxes, so a simple two-integer insert/delete is no longer sufficient. You would still only need to break step 3 into two steps.

1. Get a list of IDs to update.
2. For each ID in the posted list
   1. If the ID exists in the update list, update it.
   2. Otherwise, insert it.

The implementation would then be able to use this list to make the decision without hitting the database every time.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

// Assume this returns an associative array of IDs.
$aUpdates = $dbService->performSelect(
    "SELECT va_area_id
    FROM volunteer_area
    WHERE   va_volunteer_id = ?
        AND va_area_id IN ($sAreas)",
    array($iVol));
 
foreach($_POST["area"] as $iArea) {
    if (in_array($iArea, $aUpdates)) {
        // Update the table
        ...etc...
    }
    else {
        // Insert into the table
        ...etc...
    }
}

I think you’ll agree that this is much better than spinning through a loop, doing a count on each ID to see if it exists, then either doing an update or an insert based on the count. And, while the implementation here is PHP, it could easily be implemented in any language that supports arrays and database access.

Akismet is, by far, the most popular anti-spam plug-in for WordPress. (It comes bundled with the download, so that gets it market share. But, it’s also very, very good.) It comes with a counter that can be put into a WordPress theme. It’s attractive, but its light blue color may not integrate well into a given theme.

I went digging around in the source code, and found the line that actually pulls the count from the database. Using this parameter, I was able to integrate a spam count into the sidebar that has a look consistent with the rest of the site.

Here’s the code that’s in use on the theme on this site.

1
2
3
4
5
6
7
8

<li id="spamstats">
	<h2><?php _e('Akismet-Eaten Spam:'); ?></h2>
	<ul>
		<li><a href="http://akismet.com"><?php
			echo(number_format(get_option("akismet_spam_count"))); ?>
			and counting...</a></li>
	</ul>
</li>

Of course, line 5 is the important one - that’s how to get the number, formatted for whatever locale the server is set up for. (On my personal blog, the number is up over 1,400!)

In my searching, I have found another WordPress source code plugin, called wp-syntax. This one uses GeSHi, the Generic Syntax Highlighter. It features many languages, and is extensible to even more. (If I ever post a COBOL snippet, I’ll probably add COBOL support to it, and contribute it to the codebase.)

To use it, you simply put a pre tag, followed by “lang=[language]“. It will also do line numbering. Of course, with a single “language” parameter, the embedded language will not be highlighted as well. In this case, the previous plug-in works better; although the syntax highlighting has to be done manually, it can handle multiple languages.

Here is what the example from the “Category DropDowns in WordPress” post looks like with lang="php"…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

<?php
/**
* This creates a list of category links that can be used with a category dropdown
*/
$aCategories = get_all_category_ids();
$iMaxCat = 0;
foreach($aCategories as $iThisCat) {
    if ($iMaxCat < $iThisCat) {
        $iMaxCat = $iThisCat;
    }
}
$iMaxCat++;
?><div style="text-align:center;">
    <form name="categoryform" action="" style="text-align:center;">
    <script type="text/javascript">
        var aLink = new Array(<?php echo($iMaxCat); ?>);
<?php
foreach($aCategories as $iThisCat) {
    echo("aLink[$iThisCat] = \"" . get_category_link</span>($iThisCat) . "\";\n");
} ?>
        function goCat() {
            window.location =
                aLink[document.getElementById('cat')[document.getElementById('cat').selectedIndex].value];
        }
    </script>
    <?php wp_dropdown_categories('class=sidebardropdown&orderby=name&show_count=1&hierarchical=1'); ?>
    <br />
    <button class="sidebarbutton" type="button" style="margin-top:5px;" onclick="goCat();">View Category</button>
    </form>
</div>

And, here’s what it looks like with lang="javascript"…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

<?php
/**
* This creates a list of category links that can be used with a category dropdown
*/
$aCategories = get_all_category_ids();
$iMaxCat = 0;
foreach($aCategories as $iThisCat) {
    if ($iMaxCat < $iThisCat) {
        $iMaxCat = $iThisCat;
    }
}
$iMaxCat++;
?><div style="text-align:center;">
    <form name="categoryform" action="" style="text-align:center;">
    <script type="text/javascript">
        var aLink = new Array(<?php echo($iMaxCat); ?>);
<?php
foreach($aCategories as $iThisCat) {
    echo("aLink[$iThisCat] = \"" . get_category_link</span>($iThisCat) . "\";\n");
} ?>
        function goCat() {
            window.location =
                aLink[document.getElementById('cat')[document.getElementById('cat').selectedIndex].value];
        }
    </script>
    <?php wp_dropdown_categories('class=sidebardropdown&orderby=name&show_count=1&hierarchical=1'); ?>
    <br />
    <button class="sidebarbutton" type="button" style="margin-top:5px;" onclick="goCat();">View Category</button>
    </form>
</div>

This is another option, and is probably what I’ll use for single-language posts, or posts where the embedded language may not be crucial.

WordPress provides a template tag, wp_dropdown_categories, that inserts a drop-down list (the HTML <select> element) of categories, where the value of each item is the ID from the database. This works fine if you are not using rewrite rules (AKA “pretty links”) - you can construct a URL using the value (?cat=[number]). However, if you use any sort of rewrite rules, this does not work. I recently converted my personal site, which uses the Pool theme, to utilize a JavaScript array to assist with displaying category pages.

Here’s the code…

<?php
/**
* This creates a list of category links that can be used with a category dropdown
*/
$aCategories = get_all_category_ids();
$iMaxCat = 0;
foreach($aCategories as $iThisCat) {
    if ($iMaxCat < $iThisCat) {
        $iMaxCat = $iThisCat;
    }
}
$iMaxCat++;
?><div style="text-align:center;">
    <form name="categoryform" action="" style="text-align:center;">
    <script type="text/javascript">
        var aLink = new Array(<?php echo($iMaxCat); ?>);
<?php
foreach($aCategories as $iThisCat) {
    echo("aLink[$iThisCat] = "" . get_category_link($iThisCat) . "";\n");
} ?>        function goCat() {
            window.location =
                aLink[document.getElementById('cat')[document.getElementById('cat').selectedIndex].value];
        }
    </script>
    <?php wp_dropdown_categories('class=sidebardropdown&orderby=name&show_count=1&hierarchical=1'); ?>
    <br />
    <button class="sidebarbutton" type="button" style="margin-top:5px;" onclick="goCat();">View Category</button>
    </form>
</div>

This works for both “pretty” and standard links, as it uses the template tag get_category_link to specify the link.

Today I installed Fedora Core 2. This thing is slick! WBEL looked a lot like RH8, which I had seen before my renewed Linux learning began. FC2 has a graphical loader that hides a lot of the background stuff (unless an error occurs) - that’s cool. During the install, I skipped OpenOffice.org and MySQL, although I installed PHP with MySQL support. The reason for that is that I wanted to get the latest and greatest versions of those two products. We’ll see if this proves to be a good decision or not.

The wireless network card still wasn’t recognized (phooey). I did some more searching, armed with the knowledge that I have an adm8211 chipset. One of the first hits under Google’s Linux search for “adm8211″ pointed me to a project called NDISwrapper. This is a “wrapper” that uses the vendor’s Windows DLL file, and converts the hooks from Windows to Linux. Doing this, this driver can (in theory) support most any network card, especially those that aren’t in the Linux Hardware Compatibility List (HCL). I downloaded it, compiled it, and followed the directions to install my driver under it. I still wasn’t able to create a connection, but on a hunch, I restarted the computer. NDISwrapper is also a kernel module, and I know that often those are only read at startup. Once the computer was restarted, I was able to create a connection, and now my network card works! YEA!!! (And it was only one night’s worth of work - much better.)

Now that I have networking working under both operating systems, I plan to try to get four things working the same, whether I’m booted to WXP or Linux - E-mail (using Mozilla Thunderbird), PHP (using Apache on Linux, IIS on WXP), MySQL (using the exact same version on both), and a web server that uses the same html root directory (again, Apache on Linux, IIS on WXP). If I didn’t already have IIS up, running, and configured under WXP, I’d probably just do Apache on both, but this will be interesting - it should work, as I don’t have many creative permission rules.

Despite being ill early in the day (which caused me to miss Sunday School and church), the day on the computer went pretty well. I made a lot more progress on TMTS, although I ran into a problem - it appears that the PHP that came with WBEL may not have MySQL support enabled. Rather than get sidelined with this right now, I’m going to continue converting pages on the application, and work this issue as part of unit testing.

I also managed to catch up on comp.lang.cobol and comp.sys.unisys, two newsgroups in which I participate. I was able to play a file off a DVD (although I still can’t play the disc itself).

When I moved the mouse to try to get the screen to unblank this morning, nothing happened. It was locked up once again. I decided to only run one process, to see if I could isolate which one was causing me problems. I started with the F@H client. I started it before we left for breakfast, and when we got back, the computer was still running okay. I started using the computer actively, and found another problem - my profile for Firefox now thinks it’s still in use, because I was using it when the machine crashed.

Now, anyone who has ever used Linux will know that one browser is much less severe than, for example, IE becoming unusable in a Windows evironment. I switched to using Mozilla, and was doing some research on Linux crashes when the machine locked up again. This time, when I restarted, both Firefox and Mozilla thought they were still in use. I fired up the only other currently-installed browser, Konqueror, and surfed out to LinuxQuestions.org and posted a message asking how to tell these browsers that they’re not in use.

A few hours later, an answer appeared. For Firefox, the file is ~/.mozilla/firefox/default.lz7/lock, and for Mozilla, the file is ~/.mozilla/default/{something}.slt/lock. Both these are symbolic links to a process PID - deleting them freed up the default profiles so these browsers could be used again.

In the mean time, I have not restarted F@H, but I’ve had Evolution running in the background without incident. It seems that it may be the F@H client. That bugs me, because I was really looking forward to using this machine to help with the project. I may try to run the Windows version under wine, a Windows emulator for Linux.

Some folks have also expressed interest in the Tournament and Membership Tracking System (TMTS), which is a web application I coded to track membership and golf tournaments for a local golfing organization. They’re interested in the PHP version, for which I no longer have the source code (it was on a laptop that was stolen). So, much of my computing effort over the next few days will be trying to get this recreated. Today, I was able to get the database rebuilt, and the first few pages converted.