PHPBuilder.com - So you want to use a database in your site? (Part 2)

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:

<HTML>
<TITLE>Query form</TITLE>
<BODY BGCOLOR="white">
<FORM ACTION="/old?u=http%3A%2F%2Fphpbuilder.com%2Fcolumns%2Fdo_query.php3&y=1999" METHOD="POST">

Find all documents in which:<BR>

the title contains any of these keywords: (*)
    <INPUT TYPE="text" NAME="title" SIZE=40>i<BR>
AND<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>
AND<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:

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:

<HTML>
<HEAD>
    <TITLE>Results from query</TITLE>
</HEAD>
<BODY BGCOLOR="white">
<H1 ALIGN="center">Query Results</H1>
<?
     /* 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>
<?
     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.