Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Aug 2006
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Modyfying a PHP search script to search multiple rows in a MYSQL database

    Hello.....i have a php script here that i am trying to modify so it will be able to do a keyword search of multiple rows in a database....but i'm stuck

    when i search using specific values like "agency name" and "senior offices", the script works fine.....i am just lost as to how i can search all the rows in the database (like a keyword search)

    this is the search.php

    Code:
    <?php
    include 'includes/config.php';
    ?>
    <form name="form1" style="margin:0px" method="post" action="<?php $s = 'searchresults.php'; echo "$path$s"; ?>">
    <select name="2">
          <option value="STATEPROV" selected>Keywords</option>
          <option value="TITLE" >Agency Name</option>
          <option value="OVERVIEW">All Services</option>
          <option value="HOURS">Senior Officers</option>
         </select>
     <input type="submit" name="Search" value="Go">
    </form>
    this is my searchresults.php

    Code:
    <?php
    /////////////////////////////////////////////////////////////////////////////////
    include 'includes/config.php';
    $a = $_POST['1'];
    $b = $_POST['2'];
    /////////////////////////////////////////////////////////////////////////////////
    ?>
    
                  <strong>Search Results</strong>  
            <?php
    if (empty($a)) {$message = "Your search contained no keywords"; include 'status.php';}else{
    $keywords = explode(" ", $a);
    foreach ($keywords as $value) {
    
    $SQL = "Select * from LISTINGS WHERE $b LIKE '%$value%' LIMIT 50 ";
    $result = mysql_query( $SQL );
    while( $row = mysql_fetch_array( $result ) ) {
    $id = $row["ID"];
    $title = $row["TITLE"];
    $directory = $row["DIRECTORY"];
    $phone = $row["PHONE"];
    $website = $row["WEBSITE"];
    $email = $row["EMAIL"];
    $address = $row["ADDRESS"];
    $statepro = $row["STATEPROV"];
    $overview = $row["OVERVIEW"];
    $announcements = $row["ANNOUNCEMENTS"];
    $hours = $row["HOURS"];
    $cit = $row["CITY"];
    $zip = $row["ZIP"];
    if ($hold == $id) {} else {
    $hold = $id;
    ?>
            <table cellspacing="0" cellpadding="2" border="0" bordercolor="#FFFFFF" bgcolor="#CCCCCC" width="100%">
              <tr bgcolor="#F9F9F9"> 
                <td width="33%" bgcolor="#FFFFFF"> <font color="#FFFFFF"><font color="#000000"> <a style="font-weight:bold" href="item.php?id=<?php echo $id; ?>&dir=<?php echo $directory; ?>"> 
                  <?php echo $title; ?> </a> </font></font><br>
                  <?php echo $address; ?>              <?php $qrst = none; ?>
                  <br>
                  <br></td>
              </tr>
            </table>
            <?php } ?>
            <?php } ?>
            <?php }
    ?>
            <?php }
    ?>
            <?php if (empty($qrst)) {?>
            <table cellspacing="0" cellpadding="2" border="0" bordercolor="#FFFFFF" bgcolor="#CCCCCC" width="100%">
              <tr bgcolor="#F9F9F9"> 
                <td width="33%"> <font color="#FFFFFF"><font color="#000000"> Sorry, 
                  we couldn't find anything matching that criteria.</font></font></td>
              </tr>
            </table>
            <?php } ?>
            <br>
            <br>
            <br>
            <br> </td>
         
        </tr>
      </table>

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You should really look into fulltext searching. MySQL pretty much handles all the grunt work for you.

  • #3
    New to the CF scene
    Join Date
    Aug 2006
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    yeh true...butthis code does the job nicely....i'm just wondering if i can modyfy it to search all fields instead of one particular field

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    look into fulltext searching then you can easily search across multiple columns. you would not be changing anything in your script except the single database query. failing that chain a list of ORs together in the query.

    Code:
    select 
    foo,
    bar,
    qux
    from yourtable
    where foo like '%searchterm%'
    OR
    bar like '%searchterm%'
    OR
    qux like '%searchterm%'
    note that the use of % before your searchterm precludes your query from using an index on any of those columns and thus your search time will be SIGNIFICANTLY slower than using FULLTEXT which is exactly why fulltext was suggested.

  • #5
    New to the CF scene
    Join Date
    Aug 2006
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks i will mess around with this and let you guys know how it goes

  • #6
    New to the CF scene
    Join Date
    Aug 2006
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    got it working....thanks


  •  

    Posting Permissions

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