My web site is hosted at pair.com, which uses a free Unix operating system (FreeBSD), a free database (MySQL), and two free languages for server-side scripting: PHP4 and Perl.
Of the two scripting languages, I'll cover PHP4, which is more like Microsoft's Active Server Pages (ASP). (There's even a program, asp2php, that can help you convert old ASP pages to PHP. I haven't tried it, though.)
If your computer runs Microsoft Windows, you may wish to download Cygwin, which is a free and complete Unix environment that runs under Windows. This is an excellent way to practice Unix tools without the hassle of having to install Linux or BSD.
wget http://prdownloads.sourceforge.net/webcalendar/WebCalendar-0.9.34.tar.gz
tar -xzvf WebCalendar-0.9.34.tar.gz
cd WebCalendar-0.9.34 more INSTALL
vi includes/config.inc
mv WebCalendar-0.9.34 public_html/kegel/democal
Warning: Call-time pass-by-reference has been deprecated...I then went back to the WebCalendar support forum, found the thread that mentioned the problem and a fix. The fix was a replacement file, delawarewing.org/calendar/fix/php-dbi.inc.txt. I downloaded the file to my pair account with
wget http://delawarewing.org/calendar/fix/php-dbi.inc.txtfound the original file it was to replace, and compared them with the Unix command
diff php-dbi.inc{,.txt}
That looked awful until I realized the new file had MS-DOS line breaks.
So I told diff to ignore whitespace:
diff -b php-dbi.inc{,.txt}
That looked much better; only a few lines were changed. Rather
than make the edits by hand, I turned
that into a patch with the command
diff -bau php-dbi.inc{,.txt} > fix.patch
and applied the patch with the command
patch -p0 < fix.patch(I am such a geek). Visiting the calendar on the web then worked!
Total elapsed time from start of this log, including looking for a demo app: 3 hours. I could probably do it again in fifteen minutes now that I know what to do.
After a bit of searching, we settled on Phorm as the package to use for the back-end that accepted the survey form POSTs, stored them in a MySQL database, and maintained a live count of results. It wasn't too hard to set up the first time following Phorm's documentation, but it wasn't trivial, so the second time around I took the following notes in hopes it helps other newbies get up and running.
To make Apache easier to start, I created a batch file /startapache.bat containing
SET CYGWIN_ROOT=\cygwin SET PATH=.;%CYGWIN_ROOT%\bin;%PATH% cd \cygwin\usr\sbin sh apachectl start &made it executable (under XP, you really have to!) with
chmod +x /startapache.batand created a shortcut on the desktop to c:\cygwin\startapache.bat.
Then to test database access via PHP from her home server, I created a small script 'test.php' in her document root directory, with the newly-created database's hostname, username, and password:
<?php
mysql_connect("xxxx.pair.com", "xxxx_r", "xyZZy321") or
die("Could not connect: " . mysql_error());
printf ("MySQL server version: %s\n", mysql_get_server_info());
?>
and viewed it in her web browser via the url http://127.0.0.1/test.php
(having already started Apache).
After resetting the password at pair.com (I must have written it down
wrong the first time), that worked fine.
$ wget ftp://ftp.holotech.net/phorm/phorm.tar.gz $ tar -xzvf phorm.tar.gz $ cd distribution $ more INSTALL* $ more SUPPORT*Then try reading the doc via the local Apache (why not?) at http://127.0.0.1/distribution/quickstart.rtf (requires a word processor) and http://127.0.0.1/distribution/docs/. Keep coming back to the doc; try cruising through all the topic in the left navbar, and the appendices. It's confusing, but everything you need is in there.
Geek moment: you can search the source code and doc with the Unix commands find and grep. For instance, to see every place it mentions MySQL, try$ find . -type f | xargs grep -i mysql | more
<?php $PHORM_LOG = "phorm.log"; $PHORM_LOGVAR = "Name PHORM_FROM Comment"; // If using PHORM_* variables in PHORM_LOGVAR, must "declare" them // else they will be silently blanked out; see docs/configs.html $PHORM_FROM = ph_DECLARE; ?>(Be sure not to have anything before or after the php section, or you'll get a PHP error about "headers already started" later.)
<INPUT TYPE=HIDDEN NAME="PHORM_CONFIG" VALUE="myconfig.php">
First, design the SQL table to hold the form results. (Pair.com's MySQL tutorial may be helpful.) You'll need to know the field names of the form, and how big they can be, in order to figure out how to create the columns of the table. The x-comment.html example form has three fields. Two (Name and PHORM_FROM) are short, and can fit in, say, 32 chars; the third (Comment) is large, so make it as big as possible (255). You are free to make column names different from form field names, if you like. For example, I'll use "name" instead of "Name". The MySQL command to create a table with columns with those names and sizes is:
create table survey ( name varchar(32), email varchar(32), comment varchar(255) );Then log in to your pair.com shell account via ssh, and create the table you've designed. Here's an example session, showing how to connect to the server via ssh from the cygwin commandline, and create the table. (The $, myname@zirx%, and mysql> below are the prompts Cygwin, your Pair.com server's shell, and MySQL with prompt you with -- don't type them. The arguments to mysql are your database server, username, password, and database name; you'll need to fill them in with your own.)
$ ssh myhost.com myname@zirx% mysql -hxxxx.pair.com -uxxxx -pxxxxx xxxx_survey mysql> create table survey ( name varchar(32), email varchar(32), comment varchar(255) ); mysql> quitYou can then check that the table is created ok with the MySQL command
mysql> select * from survey;That should say "Empty set" (since there's no data yet).
Now set up Phorm to log to this table (see Phorm's docs/dblog.html.) To do this, edit myconfig.php, and add the lines inside the php section:
$PHORM_MYVARS = "name=Name, email=PHORM_FROM, comment=Comment"; // If using PHORM_* variables in PHORM_MYVARS, must "declare" them // else they will be silently blanked out; see docs/configs.html $PHORM_FROM = ph_DECLARE; $PHORM_MYTABLE = "survey"; $DBase = "myaccount_survey"; $DHost = "xxxx.pair.com"; $DUser = "xxxx_w"; $DPass = "xyZZy987";These define the form fields to log, the database table to log to, and how to connect to it. (You'll need to fill the last four in with the values for your database. Since pair has different usernames for different levels of access, be sure to use the 'write' access username here, i.e. the one ending in _w, and the associated password.)
Once you've done that, every time you click submit, a row should be added to that table in the database. You can verify this by logging in to your server via SSH, and using the MySQL command
mysql> select * from survey;It should show one row for every time you click, and the rows should contain the data that was typed into the form.
<?php
// Connect To Database
// * mysql_connect takes the servername, user, and password
// * as arguments. mysql_selectdb takes the database name.
// * Together, they open a connection to your database.
mysql_connect("xxxx.pair.com","xxxx_r","xyZZy321");
mysql_selectdb("myaccount_survey");
// Execute Query
// * mysql_query takes as its argument the query you are
// * executing on the database. It should be assigned to
// * a variable -- the variable is used by other functions
// * to retrieve the results.
$QUERY = mysql_query("SELECT COUNT(*) from survey");
// How man rows in results?
// * mysql_num_rows takes the variable the query was
// * assigned to (referred to hereafter as the query
// * identifier) and returns the number of rows the query
// * resulted in.
$NUMROWS = mysql_result($QUERY, 0);
echo "Number of responses: $NUMROWS<br>";
?>
#!/bin/sh
FILE=$1
# create field list for phorm
(
echo -n '$PHORM_MYVARS = "'
grep -i name=._ $FILE | sed 's/.*"_/_/;s/".*//' | uniq | \
sed 's/_\(.*\)/_\1=_\1, /' | tr '\012' ' '
echo '";'
) | sed 's/, *"/"/' > myvars.txt
# create table create statement for mysql
(
echo 'create table survey ('
grep -i name=._ $FILE | sed 's/.*"_/_/;s/".*//' | uniq | \
sed 's/_\(.*\)/_\1 CHAR(40), /'
echo ');'
) > fields.sql
To use it, run it with the filename of your form as the argument, e.g.
sh fields.sh ../survey.htmlIt saves the PHORM_MYVARS statement in myvars.txt; copy that into your myconfig.php file. It saves the SQL statement to create the table into a file fields.sql; you can copy and paste from that when creating your table.
This script assumes that each question's field name in the form is an underscore followed by the question number -- with (for multiple checkbox questions only) a letter to make the name unique. (Phorm's design forces you to have all unique field names.)
It helps to be handy with grep and vi when plugging the questions into this report script. If you try to do it by hand, it takes a lot longer.
$questions = array(
"1. Gender",
"2. Age Range",
"3. Regarding potential weekend events:",
"4. I live in:",
"5. The location of meetings will affect my decision to attend meetings and get-togethers: ",
"6. My household income range is:",
"7. Other comments: ");
$fields = array(
"_1",
"_2",
"_3a",
"_3b",
"_3c",
"_3d",
"_4",
"_5",
"_6",
"_7");
$q = 1;
foreach ($fields as $field) {
# if this answer matches the next question, print the new question
if (preg_match("/_$q/", $field)) {
echo "<p>Question ";
echo $questions[$q-1];
echo "<br>";
$q++;
}
$QUERY = mysql_query("SELECT $field, COUNT(*) from survey group by $field");
$NUMROWS = mysql_num_rows($QUERY);
$I = 0;
while ($I < $NUMROWS) {
// Get Results
// * mysql_result returns the value of a specific field
// * in a specific row. It takes three arguments: the
// * first is the query identifier, the second is the row
// * number, and the third is the field name. In this
// * example, a while loop is used to process all
// * rows.
$FIELD1 = mysql_result($QUERY,$I,0);
$FIELD2 = mysql_result($QUERY,$I,1);
if (strcmp($FIELD1, "")) {
echo "$FIELD2 : $FIELD1<br>\n";
}
$I++;
}
}
Scared yet? Good. Here are a few steps that might help you avoid becoming security road kill.
$ grep -i name=._ survey.html | sed 's/.*"_/"_/;s/" .*/",/' | uniq > fields.txt $ grep '>[0-9]*\.' survey.html | sed 's/.*>\([0-9]\)/\1/' > questions.txt(Looks cryptic, but once you're familiar with Unix text munging tools like grep and uniq, you can handle huge tasks without breaking a sweat.)
If you get an SQL error saying something like "unknown field name", you may have forgotten to update myconfig.php with the contents of myvars.txt.
I have yet to try any of these myself, so caveat emptor.