★ wanayoo — archive 1999 http://phpbuilder.com/columns/jesus19990308-1.php3Nouvelle recherche | Portail wanayoo

    



Search:

Keywords:

  

So you want to use a database in your site? (Part 1)

Jesús Castagnetto

Chances are that at some point in the life of a web site you will need to be creating content on request. Fortunately (yeah, right...), I had to tackle this early in the development of our Metalloprotein Database site at TSRI.

There are several ways of going about this. You can purchase an expensive all-in-one packaged solution (with the kitchen sink and all the chrome), or if you are like me (e.g. someone with not much dough to spend around, and a taste for tinkering) you will just roll your own. After all kitchen sinks are overrated and chrome is passe. You will have to invest some time reading documentation and debugging, but that is the fun part of it: getting it to work.

What you need

You will need to have a correctly installed web server with PHP support, and a database. I use Apache with mod_php, and MiniSQL as a back-end database, but the same will apply if you were using any other server, with PHP running as a stand-alone CGI and other back-end database engine.

In the examples below, we will use the database "documents" (non-normalized), in which we will store, for example, the articles published in this site. This database will contain 2 tables:

  • Table: article
    Fields: id, title, author, published, length, updated, notes
  • Table: body
    Fields: id, line_num, contents

In mSQL you will create the database and tables using the code:

% msqladmin create documents
% msql documents < schema.sql

in our case "schema.sql" will contain:

create table article (
  id        char(10) NOT NULL,    # unique ID 
  title        char(200) NOT NULL,    # title of the article
  author    char(200),        # one or more, comma separated list
  published    int NOT NULL,        # when was the article published
  length    int NOT NULL,        # how many lines (each of 120 char length)
  updated    int,            # when was it updated
  notes        text(80),        # notes for internal use
)\p\g

create unique index article_idx on article ( id )\p\g

create table body (
  id        char(10) NOT NULL,    # same as the article ID
  line_num    int,            # line number
  contents    char(120)        # a line of text from the article
)\p\g

create index body_idx on article ( id )\p\g

Of course the date fields could have been created using the DATE type, but using an INT type makes it easy for comparison using dates in the form: yyyymmdd, e.g. 19990415 for April 15, 1999. After setting up the database we populate it, and start having fun!

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="/old?u=http%3A%2F%2Fphpbuilder.com%2Fcolumns%2Fdo_sql.php3&y=1999" 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 (  "&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
titlepublished
Building Dynamic Pages With Search Engines in Mind19990117
Logging With PHP19990130
Sending Mail With PHP319990221

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.

Next Page



 

Sample Code | Columns | Mail Archive | Support | Get Started! | Links | Contribute!

Contact (non support questions)

By viewing these pages you agree to the Legal Terms of Service.

 

 

 

  Geocrawler.com | GoToCity.com | DirectriCity.com | PHPBuilder.com | The Des Moines City.net