Example 1: a simple SQL query interface
June 6, 2000
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 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>
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.
Create the Database
So you want to use a database in your site?
Example 2: Parsing and querying
|
