Chances are that at some point in the life of a web site you will need to be creating content on request. Fortunately (yeah, right...), I had to tackle this early in the development of our Metalloprotein Database site at TSRI.

There are several ways of going about this. You can purchase an expensive all-in-one packaged solution (with the kitchen sink and all the chrome), or if you are like me (e.g. someone with not much dough to spend around, and a taste for tinkering) you will just roll your own. After all kitchen sinks are overrated and chrome is passe. You will have to invest some time reading documentation and debugging, but that is the fun part of it: getting it to work.

What you need

You will need to have a correctly installed web server with PHP support, and a database. I use Apache with mod_php, and MiniSQL as a back-end database, but the same will apply if you were using any other server, with PHP running as a stand-alone CGI and other back-end database engine.

In the examples below, we will use the database "documents" (non-normalized), in which we will store, for example, the articles published in this site. This database will contain 2 tables:

Table: article
Fields: id, title, author, published, length, updated, notes
Table: body
Fields: id, line_num, contents

In mSQL you will create the database and tables using the code:




% msqladmin create documents

% msql documents < schema.sql

in our case "schema.sql" will contain:




create table article (

  id        char(10) NOT NULL,    # unique ID 

  title        char(200) NOT NULL,    # title of the article

  author    char(200),        # one or more, comma separated list

  published    int NOT NULL,        # when was the article published

  length    int NOT NULL,        # how many lines (each of 120 char length)

  updated    int,            # when was it updated

  notes        text(80),        # notes for internal use

)\p\g



create unique index article_idx on article ( id )\p\g



create table body (

  id        char(10) NOT NULL,    # same as the article ID

  line_num    int,            # line number

  contents    char(120)        # a line of text from the article

)\p\g



create index body_idx on article ( id )\p\g

Of course the date fields could have been created using the DATE type, but using an INT type makes it easy for comparison using dates in the form: yyyymmdd, e.g. 19990415 for April 15, 1999. After setting up the database we populate it, and start having fun!

Example 1: a simple SQL query interface

The first thing we will do is to create a simple form, and a script to handle SELECT statements and show the results in tabular form. We will also save the query string for debugging purposes.

sql_form.html




<html>

<title> SQL form</title> 

<body bgcolor="white">

Enter you SELECT query statement below:

<form action="do_sql.php3" method="POST">

<b>SELECT</b>  

<input type="text" name="sqlstring" size="60"> 

<input type="submit" name="submit" VALUE="Submit query">

</form>

</body>

</html>

do_sql.php3




<html>  

<head>  

    <title>Results from query</title>  

</head>  

<body bgcolor="white">  

<h1 align="center">Query Results</h1>  

<?  

    /* This script will just receive an SQL string 

     * and do a "SELECT" query. No syntaxis validation is 

     * made. Also, only SELECTs are supported to avoid someone 

     * compromising the integrity of the database contents. 

     * --- Jesus M. Castagnetto 

     */ 



    $qstring = stripslashes ($sqlstring ) ;  

     echo  ( "Saving your query for debugging purposes<BR>\n" ) ;  

     echo  ( "Your query was: <B>\"select $qstring\"</B><BR>\n" ) ;  



    $link = msql_pconnect ( ) ;  

    $res = msql ( "documents",  "select ".$qstring, $link ) ;   

     if  ($res )  {  

    $nrows = msql_num_rows ($res ) ;  

    $nfields = msql_num_fields ($res ) ;  

    printf ( "and it found: <B>%d rows</B>\n",$nrows ) ;  

     }  else  { 

     echo  ( "<BR>Your query did not find any matches. Try again<BR>\n" ) ;  

     } 

     

     /* save info into a file */   

    $datestamp = date ( "Y-m-d H:i:s",time ( ) ) ;  

    $fp = fopen ( "sql_form.log",  "a+" ) ;  

    fwrite ($fp,  "DATE: $datestamp\n" ) ;  

    fwrite ($fp,  "QUERY: select $qstring\n" ) ;  

    fwrite ($fp, sprintf ( "RESULT: %d rows\n\n",$nrows ) ) ;  

    fclose ($fp ) ;  

     

?>  

<table>  

<?  

     if  ($res )  {  

     echo ( "\n<tr bgcolor=\"#E0FFFF\">" ) ;  

     for  ($i=0; $i < $nfields; $i++)  {  

        $fname = msql_fieldname ($res,$i ) ;  

         echo  ( "<th>$fname</th>" ) ;  

     }  

     echo ( "</tr>" ) ;  

    $color =   "#D3D3D3" ;  

     for  ($i=0 ;$i<$nrows ;$i++ )  {  

         if  ( ($i % 2 ) == 0 )  {  

         echo  ( "\n<tr>" ) ;  

         }  else  {  

         echo  ( "\n<tr bgcolor=$color>" ) ;  

         }  

        $rowarr = msql_fetch_row ($res ) ;  

         for  ($j=0 ;$j<$nfields ;$j++ )  {  

        $val = $rowarr[$j] ;  

         if  ($val ==   ""  )  {  

            $val = stripslashes ( "&nbsp\;" ) ;  

         }  

         echo  ( "<td>".chop ($val ). "</td>" ) ;  

         }  

         echo  ( "</tr>" ) ;  

     }  

     }  

?>  

</table>  

</body>  

</html>

That's it! Now if we do a search like:
(mockup form, does nothing)

We will get the following output:

Query Results

Saving your query for debugging purposes
Your query was: "select title,published from article where author like '%perdue%'"
and it found: 3 rows

title	published
Building Dynamic Pages With Search Engines in Mind	19990117
Logging With PHP	19990130
Sending Mail With PHP3	19990221

This simple interface can be quite powerful, depending on how you set your SQL queries. For example, if you decide to obtain the body of the articles written by Rasmus Lerdorf, then you would use the following query:




SELECT article.title,article.author,body.contents from article,body

where article.author clike '%rasmus%' and article.id=body.id order by

body.line_num

Or we can be even fancier and select only the lines of the article from a particular author containing one or more keywords of interest:




SELECT article.title,article.author,body.line_num,body.contents 

from article,body

where aticle.author='Mark Musone' and article.contents clike '%pop%' and

article.id=body.id order by body.line_num

Other queries can be also done, but this should suffice to whet your appetite.

In the next part of this article we will tackle the parsing of variables, and construction of an SQL query from them.

Here I will show a more complex example, which should make life easier for the user (who says the developer's life should be easy too :).

Example 2: Parsing and querying

In the first part of this article we made a straight, no syntax checking, SQL form interface to the database.

Now, I know that some people do not like to write raw SQL, so we will make a more "user-friendly" HTML form:

query_form.html




<html>

<title>Query form</title>

<body bgcolor="white">

<form action="do_query.php3" method="POST">



Find all documents in which:<br />



the title contains any of these keywords: (*)

    <input type="text" name="title" size="40">i<br />

&nbsp;<br />

was written by any of the following: (check as many as needed)<br />

<input type="checkbox" name="author[]" value="Mark Musone"> Mark Musone

<input type="checkbox" name="author[]" value="Mattias Nilsson"> Mattias Nilsson

<input type="checkbox" name="author[]" value="Rasmus Lerdorf"> Rasmus Lerdorf

<input type="checkbox" name="author[]" value="Tim Perdue"> Tim Perdue

<br />&nbsp;<br />

was published on or after the year:

<select name="pubyear">

<option value="1999" SELECTED>1999</option>

<option value="2000">2000</option>

<option value="2001">2001</option>

</select>

<br />

<input type="submit" name="submit" value="Submit query">

</form>

(*) <I>Note</I>: You can enter a comma separated list of keywords, e.g.

"mail,imap,logging" will search for full or partial matches to:

<tt>mail OR imap OR logging</tt> in the title of the article.

</body>

</html>

To make things interesting, the form will allow the input of 3 different types of variables:

a list of optional keywords for the title (using comma as a separator),
a series of checkboxes allowing for multiple selections (treated as an array),
and a drop down allowing selection of a single option.

Allowing for the input of a list, makes it easy to search for multiple keywords, e.g. if I want to search for articles with "mail" or "imap" in the title, I will write: "mail,imap".

To handle the form above, we are going to reuse the code from the script "do_sql.php3" (isn't recycling good), adding code to parse the title, author, and publication date:

do_query.php3






<html>

<head>

    <title>Results from query</title>

</head>

<body bgcolor="white">

<h1 align="center">Query Results</h1>

<?php

    /* Parsing functions

     * The list parsing function could be defined in terms of an array

     * parsing function, but I decided to do different implementations

     * to show how defaulted parameters can be used as flags

     * --- Jesus M. Castagnetto

     */



    /* parseList:

     * $fieldcond = the SQL condition for the input items

     * $slist = the string containing the list of input items

     * $sep = the list separator, defaults to a single comma

     * $q1 and $q2 are the quote string to be pre/appended to the list item

     */

     function parseList ($fieldcond,$slist,$sep= ",",$q1= "'",$q2= "'" )  {

    $tarr = explode ($sep,$slist ) ;

    $out = $fieldcond . $q1 . $tarr[0] . $q2 ;

     if  (count ($tarr ) > 1 )  {

         for  ($i=1 ; $i<count ($tarr ) ; $i++ )  {

         if  ($tarr[$i] !=  "" )  {

            $out .=  " or " . $fieldcond . $q1 . $tarr[$i] . $q2 ;

         }

         }

     }

    return  "( " . $out .  " )" ;

     }



     /* parseArray:

     * $field = the field for the input items

     * $alist = the array containing the input items

     * $comp = the comparison to be used for the SQL string

     * $quoted = whether the items need to be single quoted

     */

     function parseArray ($field,$alist,$comp= "=",$quoted=1 )  {

     if  ($quoted )  {

        $q1 = $q2 =  "'" ;

        $comp = strtolower ($comp ) ;

         if  ($comp ==  "like" || $comp ==  "clike" )  {

        $q1 =  "'%" ; $q2 =  "%'" ;

         }

     }  else  {

        $q1 = $q2 =  "" ;

     }

    $out = $field.  " " . $comp .  " " . $q1 . $alist[0] . $q2 ;

     if  (count ($alist ) > 1 )  {

         for  ($i=1 ; $i<count ($alist ) ; $i++ )  {

         if  ($alist[$i] !=  "" )  {

            $out .=  " or " . $field.  " " . $comp .  " " . $q1 . $alist[$i] . $q2 ;

         }

         }

     }

    return  "( " . $out .  " )" ;

     }



    /* title is a list */

     if  ($title )  {

    $q_title = parseList ( "title clike ",strtolower ($title ), ",", "'%", "%'" ) ;

     }



     /* author is an array */

     if  ($author )  {

    $q_author = parseArray ( "author",$author ) ;

     }



     /* publication year - lower limit */

    $q_pubyear =  ($pubyear ?  "published >= ".$pubyear. "0101" :  "" ) ;



     /* build the query string */



    $qstring =  " title,author,published,length FROM article WHERE " ;

    $qstring .= $title ? $q_title :  "" ;

    $qstring .=  ($title && $author ) ?  " AND " :  "" ;

    $qstring .= $author ? $q_author :  "" ;

    $qstring .=  ( ($title && $pubyear ) ||  ($author && $pubyear ) ) ?  " AND " :  "" ;

    $qstring .= $pubyear ? $q_pubyear :  "" ;

    $qstring .=  " ORDER BY author,published" ;



     /* build message string */

    $mstring =  "You searched on articles " ;

    $mstring .= $title ?  " with the words ".$title. " in the title; " :  "" ;

    $mstring .= $author ?  " written by ".implode ($author, " OR " ) :  "" ;

    $mstring .= $pubyear ?  " published on or after ".$pubyear. "." :  "" ;





     /* Show query string */

     echo  ( "Saving your query for debugging purposes<BR>\n" ) ;

     echo  ( "<B>$mstring</B><BR>\n" ) ;



     /* Uncomment the following line if you want to show the SQL string */



     // echo ("The parsed SQL string was: $qstring<BR>\n" );



    $link = msql_pconnect ( ) ;

    $res = msql ( "documents",  "select ".$qstring, $link ) ;

     if  ($res )  {

    $nrows = msql_num_rows ($res ) ;

    $nfields = msql_num_fields ($res ) ;

    printf ( "and it found: <B>%d rows</B>\n",$nrows ) ;

     }  else  {

     echo  ( "<BR>Your query did not find any matches. Try again<BR>\n" ) ;

     }

     /* save info into a file */

    $datestamp = date ( "Y-m-d H:i:s",time ( ) ) ;

    $fp = fopen ( "query_form.log",  "a+" ) ;

    fwrite ($fp,   "DATE: $datestamp\n" ) ;

    fwrite ($fp,   "QUERY: select $qstring\n" ) ;

    fwrite ($fp, sprintf ( "RESULT: %d rows\n\n",$nrows ) ) ;

    fclose ($fp ) ;



?>

<table border="0">

<?php

     if  ($res )  {

     echo ( "\n<TR BGCOLOR=\"#E0FFFF\">" ) ;

     for  ($i=0 ;$i<$nfields ;$i++ )  {

        $fname = msql_fieldname ($res,$i ) ;

         echo  ( "<TH>$fname</TH>" ) ;

     }

     echo ( "</TR>" ) ;

    $color =   "#D3D3D3" ;

     for  ($i=0 ;$i<$nrows ;$i++ )  {

         if  ( ($i % 2 ) == 0 )  {

         echo  ( "\n<TR>" ) ;

         }  else  {

         echo  ( "\n<TR BGCOLOR=$color>" ) ;

         }

        $rowarr = msql_fetch_row ($res ) ;

         for  ($j=0 ;$j<$nfields ;$j++ )  {

        $val = $rowarr[$j] ;

         if  ($val ==   ""  )  {

            $val = stripslashes ( "&nbsp\;" ) ;

         }

         echo  ( "<TD>".chop ($val ). "</TD>" ) ;

         }

         echo  ( "</TR>" ) ;

     }

     }

?>

</table>

</body>

</html>

In the script above we have two general parsing functions that return a valid SQL comparison statement, these can be extended to cases in which we want to allow the use of operators such as: AND, OR, NOT, etc.

In this script, you saw the use of default values for some of the parsing functions parameters; this allows the invokation of the function with a variable number of said parameters. You will only need to specify the defaulted ones if you need to change them.

We will use the form to search for the keywords "image,reading,creating", in the title of articles written by Matias or Mark or Rasmus, with a publication date of 1999 or later. This will generate the following result:

Query Results

Saving your query for debugging purposes
You searched on articles with the words image,reading,creating in the title; written by Mark Musone OR Mattias Nilsson OR Rasmus Lerdorf published on or after 1999.
and it found: 3 rows

title	author	published	length
IMAP Mail Reading With PHP3	Mark Musone	19990207	200
Creating your own logfile	Mattias Nilsson	19990302	200
Image Creation With PHP	Rasmus Lerdorf	19990124	200

The form and the handling script can be modified to allow searching on more variables, or to search for keywords in the title and/or the body of the article, or even to retrieve a keyword matched in the body in a context (for example, get the 2 lines above and below the matching line).

Using a form like this gives control on the information the user can access, and reduces the number of possible SQL queries. If you type straight SQL, you can always make a query that can take (for practical purposes) and infinite amount of time to finish, something you may not want to happen in your site.

Where to go from here

If you went through the examples and experimented a bit, you will start having more and better ideas on how to use this newfound flexiblity in your site design. You should be able to make more sophisticated interfaces, allowing the use of more complex logical expressions, or even create the entire query interface based on reading the structure of the database, on-the-fly. No limit to what you can do, just remember to have fun and don't get discouraged.

Paraphrasing an old saying: "Inspiration is 10% of the work, debugging the remaining 90%" (or something like that).

You should check the examples sources available from the "Sample Code" link in this site. In particular I am impressed by the work done to make the PHP base library, which now includes the OOHForms module (highly recommended).

You can get the (IMHO) best web server available from the Apache Project site, and obtain good SQL databases such as: mSQL, MySQL, and PostgreSQL from their respective sites. And of course, keep the PHP manual handy at all times.

You are now on your own pal!
Good luck.
=== Jesús.