SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot pnathan's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Search mySQL using PHP

    Hello, here is what I want to do:

    I Have a mySQL database table with 5 field's of data in it.

    I want to search 2 of the fields and then return the results in html format (using PHP).

    The database is a knowledgebase for users of my software. I want to allow the user search the database and then click on the search on the search results, that will be displayed using PHP.

    I have looked at a number of search options but none of them are working out for me. Can anybody recommend a search script or any other way that I can resolve my problem.

    Thanks
    I have two tickets to the Crows, sweet.

  2. #2
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quick example: I'll assume you have a database of, say, documents...each of which have a body of text, and a title. Try this script:

    PHP Code:
    <?php
      
    if ($submit) {
        
    $result mysql_query("SELECT title, date FROM documents WHERE title LIKE \"%$query%\" OR body LIKE \"%$query%\"");
        
          if (!
    mysql_num_rows($result)) {
            print 
    "No documents found.";
            exit();
          }

        
    ?><table><?php

          
    while ($row mysql_fetch_array($result)) {
            
    extract($row);
            
    ?><tr>
                <td><?= $title ?></td>
                <td><?= $date ?></td>
              </tr><?php
          
    }

        
    ?></table><?php   

      
    } else {
        
    ?><form action="<?= $PHP_SELF ?>" method="post">
          Search Terms: <input type="text" name="query">
          <br><input type="submit" name="submit">
          </form><?php
      
    }
    ?>

  3. #3
    SitePoint Addict rwar's Avatar
    Join Date
    Sep 2001
    Location
    PF / RS / BR
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nothing else?
    php? mysql? apache? That's it.

  4. #4
    SitePoint Zealot pnathan's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    TWTCommish

    Thanks for that, but I could not get it working. Do you have any other suggestions?

    Is there a resource site that explains how to search a mySQL database using PHP and return the search results in formatted HTML
    I have two tickets to the Crows, sweet.

  5. #5
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure what you had trouble with...my example was almost completely functional, save for some minor query modifications, basically. What specifically is giving you a hard time?

  6. #6
    SitePoint Zealot pnathan's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here is the code I am using

    PHP Code:
    <?php
    include("config.php"); /// connect to database

      
    if ($submit) {
        
    $result mysql_query("SELECT problem, solution FROM cases WHERE problem LIKE \"%$query%\" OR body LIKE \"%$query%\"");
       
          if (!
    mysql_num_rows($result)) {
            print 
    "No documents found.";
            exit();
          }

        
    ?><table><?php
          
    while ($row mysql_fetch_array($result)) {
            
    extract($row);
            
    ?><tr>
                <td><?= $problem ?></td>
                <td><?= $solution ?></td>
              </tr><?php
          
    }
        
    ?></table><?php   
      
    } else {
        
    ?><form action="<?= $PHP_SELF ?>" method="post">
          Search Terms: <input type="text" name="query">
          <br><input type="submit" name="submit">
          </form><?php
      
    }
    ?>

    </div>
    And when I do a search, this is the error I get:

    Warning: Supplied argument is not a valid MySQL result resource in /home/capeclr/capeclear-www/php/search.php on line 7
    Any ideas?
    I have two tickets to the Crows, sweet.

  7. #7
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $result mysql_query("SELECT problem, solution FROM cases WHERE problem LIKE \"%$query%\" OR body LIKE \"%$query%\""); 
    use single quotes around $query
    also, why are you using % around the $query?
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  8. #8
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The "%" is a wild-card...which means it will return any rows that have the value of $query anywhere in the middle. Anyway, that code should work, but yes, try single quotes around the variables in the query...if that doesn't work, then the problem much be with one of the field/table names. I notice you still have "body" near the end...is that a mistake?

  9. #9
    SitePoint Zealot zook's Avatar
    Join Date
    May 2001
    Location
    Houston, tx
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i beleive this line is where the error is coming from:

    PHP Code:
    $result mysql_query("SELECT problem, solution FROM cases WHERE problem LIKE \"%$query%\" OR body LIKE \"%$query%\""); 
    at the end you have "OR body LIKE.." "body" should be "solution". example:

    PHP Code:
    $result mysql_query("SELECT problem, solution FROM cases WHERE problem LIKE \"%$query%\" OR solution LIKE \"%$query%\""); 

  10. #10
    SitePoint Zealot pnathan's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all your help guys/girls, I got it working

    For anybody's interest, here is the code I used:



    PHP Code:
    <? 
    $hostname 
    "localhost";    
    $username "name";       
    $password "pass";        
    $dbName "database";       
    $usertable "table";      

    MYSQL_CONNECT($hostname$username$password) OR DIE("Unable to connect to database"); 
    @
    mysql_select_db"$dbName") or die( "Unable to select database"); 
    ?> 
    <? 
    $query 
    mysql_query("SELECT * FROM $usertable WHERE solution LIKE '%$search%' LIMIT 0, 30 "); 
    ?> 
    <table wisth=420 border=1>
    <?php
    while ($row  =  mysql_fetch_array($query)) {
       
    extract($row);
    ?> 
    <tr>
       <td width=20><?= $ID ?></td>
       <td width=400><?= $problem ?></td>
    </tr>
    <?php
    }
    ?>
    </table>
    <?php   
    //below this is the function for no record!! 
    if (!$variable1)  

    print (
    "$XX"); 

    //end 
    ?>
    I using a html form for the search and declaring the "action" of the form as "search.php"

    Thanks again
    I have two tickets to the Crows, sweet.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •