Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2008
    Posts
    8

    Unanswered: PHP Problem with Database Search Engine

    hello guys.. i have some problems regarding my search engine script

    http://img337.imageshack.us/img337/69/psvo5.th.jpg

    as you can see..there are several categories for the user to choose his/her search location.. i am able to implement it with this code..

    PHP Code:
    <?php require_once('Connections/Database.php'); ?>
    <?php
    $category 
    $_REQUEST['category'];
    $maxRows_rs 20;
    $pageNum_rs 0;
    if (isset(
    $_GET['pageNum_rs'])) {
      
    $pageNum_rs $_GET['pageNum_rs'];
    }
    $startRow_rs $pageNum_rs $maxRows_rs;

    $colname_rs "-1";
    if (isset(
    $_GET['title'])) {
      
    $colname_rs = (get_magic_quotes_gpc()) ? $_GET['title'] : addslashes($_GET['title']);
    }




    mysql_select_db($database_Database$Database);
    $query_rs sprintf("SELECT * FROM $category WHERE GPN LIKE '%%%s%%' OR Value LIKE '%%%s%%' OR Manufacturer LIKE '%%%s%%' OR Description LIKE '%%%s%%' OR Part_Number LIKE'%%%s%%'"$colname_rs,$colname_rs,$colname_rs,$colname_rs,$colname_rs);
    $query_limit_rs sprintf("%s LIMIT %d, %d"$query_rs$startRow_rs$maxRows_rs);
    $rs mysql_query($query_limit_rs$Database) or die(mysql_error());
    $row_rs mysql_fetch_assoc($rs);


    if (isset(
    $_GET['totalRows_rs'])) {
      
    $totalRows_rs $_GET['totalRows_rs'];
    } else {
      
    $all_rs mysql_query($query_rs);
      
    $totalRows_rs mysql_num_rows($all_rs);
    }
    $totalPages_rs ceil($totalRows_rs/$maxRows_rs)-1;


    ?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Search Results</title>
    <style type="text/css">
    <!--
    font {
        font-size: x-small;
    }
    .style2 {
        font-size: 12px;
        font-weight: bold;
    }
    .style3 {font-size: 12px}
    .style4 {font-size: 16px;
    }
    -->
    </style>
    </head>

    <body>
    <table width="800" class="style4">Search results for <?php echo $_GET['title']?> in <?php echo $category;?><tr><br />Your search returned <?php echo $totalRows_rs ?> results</tr></table>
    <span class="style4"></span><br />
    <br /><br />
    <table width="800" border="1" align="center" cellpadding="5" cellspacing="5">
      <tr bgcolor="#009999">
        <td><div align="center" class="style2">GPN</div></td>
        <td><div align="center" class="style2">Value</div></td>
        <td><div align="center" class="style2">Part Number</div></td>
        <td><div align="center" class="style2">Manufacturer</div></td>
        <td><div align="center" class="style2">Description</div></td>
        <td><div align="center" class="style2">Rating 1</div></td>
        <td><div align="center" class="style2">Rating 2</div></td>
        <td><div align="center" class="style2">Part Type</div></td>
        <td><div align="center" class="style2">Size</div></td>
        <td><div align="center" class="style2">Schematic Part</div></td>
        <td><div align="center" class="style2">ORCAD Footprint</div></td>
        <td><div align="center" class="style2">Footprint</div></td>
        <td><div align="center" class="style2">Component Height</div></td>
        <td><div align="center" class="style2">CATIA Mechanical Drawing</div></td>
        <td><div align="center" class="style2">Pro Engineer Mechanical Drawing</div></td>
        <td><div align="center" class="style2">Supplier</div></td>
        <td><div align="center" class="style2">Distributor Part Number</div></td>
        <td><div align="center" class="style2">Distributor</div></td>
        <td><div align="center" class="style2">Price</div></td>
        <td><div align="center" class="style2">Availability</div></td>
        <td><div align="center" class="style2">Alternate Source</div></td>
        <td><div align="center" class="style2">Alt Part Number</div></td>
        <td><div align="center" class="style2">Alt Price</div></td>
        <td><div align="center" class="style2">Pspice</div></td>
        <td><div align="center" class="style2">Datasheet</div></td>
        <td><div align="center" class="style2">ActivepartsID</div></td>
      </tr>
      <?php do { ?>
        <tr>
          <td><span class="style3"><?php echo $row_rs['GPN']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Value']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Part_Number']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Manufacturer']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Description']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Rating_1']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Rating_2']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Part_Type']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Size']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Schematic_Part']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['ORCAD Footprint']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Footprint']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Component_Height']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['CATIA_Mechanical_Drawing']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Pro_Engineer_Mechanical_Drawing']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Supplier']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Distributor_Part_Number']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Distributor']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Price']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Availability']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Alternate_Source']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Alt_Part_Number']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Alt_Price']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Pspice']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['Datasheet']; ?></span></td>
          <td><span class="style3"><?php echo $row_rs['ActivepartsID']; ?></span></td>
        </tr>
        <?php } while ($row_rs mysql_fetch_assoc($rs)); ?>
    </table>

    <form id="form1" name="form1" method="post" action="index.php">
      <label>
      <input type="submit" name="Submit" value="Search Again" />
      </label>
    </form>
    </body>
    </html>
    <?php
    mysql_free_result
    ($rs);
    ?>
    Here are my two problems/question:

    1. With the existing code above, how will i display a new page if the user does not put any keyword on the text box? As of the moment,the page will display all the contents of the table when a blank test is returned.

    2. I also included an "All" category in which all tables in the database will be searched.. i am having difficulty in implementing this..

    I hope you could help me on my prob.. thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well the way I handle this sort of thing is to have an <all> option in each of the list boxes and then build the parameters for the where clause individully.

    if the <all> option is selected by the user then I don't use that parameter, if an option is used then that paramter is used. Usually the value of the <all> option in my code is either -1 (for numeric values) and ! (for string/text values)
    on first entry into the script the selected value of the list box is set to <all>
    when building the SQL I insert the select statement then build a where clause as required. if there are always where terms I do those first and then add on any dynamic parameters and follow up with the sort order.

    eg
    Code:
    $strSQL = "select column1, column2, column3 from mytable join......blah blah blah";
    'ok so thats the select done, so now we need to build the where clause
    'note from now on anything added to the SQL or WHERE clause has a leading space
    $strWhereClause=" Where"; 
    if ($listParam1 != -1 )
    { $strWhereClause.= " column1 = $listParam1";} 'add the parameter IF its not set to all
    if ($listParam2 != -1)
    { if strlen($strWhereClause) > 7)  'check to see if the length of the whereclause is more than 7 in which case we have a previous parameter so insert an AND
      { $strWhereClause = " AND ";
      }
      $strWhereClause.= " column2 = $listParam2"; 'add the parameter
    }
    if ($listParam3 != "!") 
    { if strlen($strWhereClause) > 7)
      { $strWhereClause = " AND ";
      }
      $strWhereClause.= " column3 LIKE \"%$listParam3%\"";
    }
    if strlen($strWhereClause) > 7) 'check to see if the where clause is ore than 7 characters long if so add it to the SQL
    { $strSQL .= $strWhereClause
    }
    $strSQL .= " ORDER BY Column3"; 'add any sort order as required

    HTH
    Last edited by healdem; 05-21-08 at 03:22.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2008
    Posts
    8
    ummm..i don't quite get what you mean bro.. actually i'm new in php/mysql and i just used the assistance of dreamweaver in generating the codes..

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in which case Id step through the code and make an effort to understand what is going on with the script bro.....
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    healdem, your code will be simpler and less prone to error if you begin the WHERE clause with WHERE 1=1

    that way, any requests which consist of only "all" options will return all rows, and you won't have to remove the dangling WHERE keyword you started with in line 4 of your example

    plus, you don't have to decide on each parameter whether it's the first one or a subsequent one -- they are all subsequent to 1=1, so they can all simply start with AND
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    the WHERE 1=1 trick is one of the best programming tips I have picked up since leaving school - it is so simple, so elegant, and most of all - so easy!
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by r937
    healdem, your code will be simpler and less prone to error if you begin the WHERE clause with WHERE 1=1

    that way, any requests which consist of only "all" options will return all rows, and you won't have to remove the dangling WHERE keyword you started with in line 4 of your example

    plus, you don't have to decide on each parameter whether it's the first one or a subsequent one -- they are all subsequent to 1=1, so they can all simply start with AND
    nice one
    Ive always thought building the strings was a kludgey way of doing it.....
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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