3 Different Types of Variables
June 6, 2000
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>
<?
/* 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 ( " \;" ) ;
}
echo ( "<TD>".chop ($val ). "</TD>" ) ;
}
echo ( "</TR>" ) ;
}
}
?>
</TABLE>
</BODY>
</HTML>
Example 2: Parsing and querying
So you want to use a database in your site?
Using the Form to Search
|
