Example 1: a simple SQL query interface
SELECT statements and show the results in tabular form. We will also
save the query string for debugging purposes.
<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>
<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: $datestampn" ) ;
fwrite ($fp, "QUERY: select $qstringn" ) ;
fwrite ($fp, sprintf ( "RESULT: %d rowsnn",$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 ( " " ) ;
}
echo ( "<td>".chop ($val ). "</td>" ) ;
}
echo ( "</tr>" ) ;
}
}
?>
</table>
</body>
</html>
(mockup form, does nothing)
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 |
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
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
the parsing of variables, and construction of an SQL query from them.
