List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 12 1999 1:37pm
Subject:Re: [mySQL] beginners search engine layout
View as plain text  
On Sat, 1999-09-11 19:18:50 -0700, Del Dhanoa wrote:
> I have heard that it is possible to code a PHP/mySQL search engine
> in one file (call it index.php3).  I am wondering if someone can
> poit out a basic file structure for me.

There are two phases (as you've also written):
  1. The Search Form
  2. Searching and presenting the result

The most basic form to implement this, is to actually write two pages:
  1. A very normal HTML page:

    /* Section 1 -- The Search Form */
    <FORM ACTION="search.php3" METHOD="POST">
    <INPUT TYPE="text" NAME="phrase" SIZE="20" MAXLENGTH="30">
    <INPUT TYPE="submit" VALUE="Search!">
    </FORM>

    The important part here is ACTION="search.php3" pointing
    to the search script:

 2. The search script "search.php3":

    Because the name of the text input field of the HTML FORM
    has been "phrase", we now have a PHP variable $phrase holding
    the search phrase.

    Now there are two steps:
    2a) Doing the search as an SQL query
    2b) Presenting the results of the query

    2a) You have a database "search" with a table
        "table(title,url,description,keywords)".

        So the query will be:
          SELECT ... FROM search.table WHERE ...

        Or, if "search" already is the current database:
          SELECT ... FROM table WHERE ...

        - What fields of the table do you want to be searched?

        Your example assumes, only the keywords field.
           * WHERE keywords = 'words of phrase'
               This is not what you want, because this way the
               keywords fields must have exactly this value.
        You probably want to test if the search phrase is a
        substring of the value of the keywords field:
           * WHERE keywords LIKE '%words of phrase%'
        or * WHERE INSTR(keywords,'words of phrase') > 0

        - What fields of the table do you want to be returned as
          a result for matching rows?

        Most probably all of them.  That is:
           * SELECT title, url, description, keywords

        - So the complete query looks like this:

        SELECT title, url, description, keywords
        FROM   search.table
        WHERE  keywords LIKE '%words of phrase%'

        - Now, how do you write this in PHP?

        Instead of 'words of phrase' we need the value of $phrase.
        So the first try is:
          $query = "SELECT title, url, description, keywords
                    FROM   search.table
                    WHERE  keywords LIKE '%$phrase%'";

        But what happens, if for example the phrase is "Meyer's"?
        The resulting string would be:
           SELECT ... WHERE keywords LIKE '%Meyer's%'
        Ups, this is no correct SQL anymore because of the three
        apostrophes!  Manually we'd have to write this as
           SELECT ... WHERE keywords LIKE '%Meyer\'s%'
        So the lesson learned is, that for unknown user input we
        have to quote "dangerous" characters.
        This is, what the PHP function AddSlashes does for us!
          $query = "SELECT title, url, description, keywords
                    FROM   search.table
                    WHERE  keywords LIKE '%"
                   . AddSlashes($phrase) . "%'";
        
        - How to send this query to MySQL?
         
          Open a connection to MySQL, then maybe choose a default
          database, then do the query.  Always check result codes!

          // connect using the defaults for host, user, password:
          $cid = mysql_connect()
             or die("Can't connect to MySQL!");
          // select 'search' as default database.
          mysql_select_db('search', $cid)
             or die("Can't select database 'search'!");
          // do the query.
          $rid = mysql_query($query, $cid)
             or die("Invalid query $query!");

          Note:  Instead of mysql_select_db, mysql_query you
          also can use mysql_db_query, but then do it with the
          needed arguments!  See the manual.

    2b) Presenting the results of the query

        The set of result rows may be empty, if there simply is no
        match for the searched phrase, so we have to test for the
        number of result rows:

        if ( 0 == mysql_num_rows($rid) ) {
          echo "Nothing found, please revise your search.<BR>\n";
        }
        else {
          // fetch results row by row and display them.
          while( $row = mysql_fetch_row($rid) ) {
            $title       = $row['title'];
            $url         = $row['url'];
            $description = $row['description'];
            echo HtmlSpecialChars("$title, $url, $description")
                 . "<BR>\n";
          }
        }

        Note: HtmlSpecialChars or HtmlEntities has to be used,
          if the field values might include the less sign or the
          ampersand, because if output unchanged, these characters
          are misinterpreted as HTML tags or entities.

        And finally:
        mysql_close($cid);



Okay, how to intergrate the two files (html page with form and PHP
search page) into a single PHP script?

  You need a way to distinguish between the two steps 1. and 2.!
  A suitable way is to check, whether $phrase is empty or not ...

  if( empty($phrase) ) {
    // do the HTML FORM with ACTION now pointing to this script itself
    // again
    ...
  }
  else {
    // do the search
    ...
  }

That's it.

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
[mySQL] beginners search engine layoutd e l   d h a n o a12 Sep
  • Re: [mySQL] beginners search engine layoutMartin Ramsch12 Sep
    • Re: [mySQL] beginners search engine layoutMartin Ramsch12 Sep