To make things interesting, the form will allow the input of 3 different types
of variables:
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”.
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_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: $datestampn" ) ;
fwrite ($fp, "QUERY: select $qstringn" ) ;
fwrite ($fp, sprintf ( "RESULT: %d rowsnn",$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 ( " " ) ;
}
echo ( "<TD>".chop ($val ). "</TD>" ) ;
}
echo ( "</TR>" ) ;
}
}
?>
</table>
</body>
</html>
