#native_company# #native_desc#
#native_cta#

So you want to use a database in your site? Page 3

By Jess Castagnetto
on July 30, 2000

Example 1: a simple SQL query interface

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: $datestampn" ) ;  

    
fwrite ($fp,  "QUERY: select $qstringn" ) ;  

    
fwrite ($fpsprintf "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=;$i<$nrows ;$i++ )  {  

         if  ( (
$i ) == )  {  

         echo  ( 
"n<tr>" ) ;  

         }  else  {  

         echo  ( 
"n<tr bgcolor=$color>" ) ;  

         }  

        
$rowarr msql_fetch_row ($res ) ;  

         for  (
$j=;$j<$nfields ;$j++ )  {  

        
$val $rowarr[$j] ;  

         if  (
$val ==   ""  )  {  

            
$val stripslashes "&nbsp;" ) ;  

         }  

         echo  ( 
"<td>".chop ($val ). "</td>" ) ;  

         }  

         echo  ( 
"</tr>" ) ;  

     }  

     }  

?>  

</table>  

</body>  

</html>


That’s it! Now if we do a search like:
(mockup form, does nothing)
SELECT
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.

1
|
2
|
3
|
4
|
5
|
6
|
7