dcsimg
www.webdeveloper.com
Results 1 to 3 of 3

Thread: PHP MySQL database search script

  1. #1
    Join Date
    Feb 2008
    Posts
    20

    PHP MySQL database search script

    I have a scrip from designpalace.com which is pretty much what i need but have to change the SQL query which is where i'm finding difficulties could anyone help me in fixing this...here is my code

    <?php
    //This is a working script
    //Make sure to go through it and edit database table filelds that you are seraching
    //This script assumes you are searching 3 fields
    $hostname_logon = "localhost" ;
    $database_logon = "databaseName" ;
    $username_logon = "databaseUser" ;
    $password_logon = "databasePass" ;
    //open database connection
    $connections = mysql_connect($hostname_logon, $username_logon, $password_logon) or die ( "Unabale to connect to the database" );
    //select database
    mysql_select_db($database_logon) or die ( "Unable to select database!" );

    //specify how many results to display per page
    $limit = 10;

    // Get the search variable from URL
    $var = @$_GET['q'] ;
    //trim whitespace from the stored variable
    $trimmed = trim($var);
    //separate key-phrases into keywords
    $trimmed_array = explode(" ",$trimmed);

    // check for an empty string and display a message.
    if ($trimmed == "") {
    $resultmsg = "<p>Search Error</p><p>Please enter a search...</p>" ;
    }

    // check for a search parameter
    if (!isset($var)){
    $resultmsg = "<p>Search Error</p><p>We don't seem to have a search parameter! </p>" ;
    }
    // Build SQL Query for each keyword entered
    foreach ($trimmed_array as $trimm){

    // EDIT HERE and specify your table and field names for the SQL query
    $query = "select * from prod_search where ".$trimm." like '%".$trimm."%'";
    //$query = " SELECT * FROM `prod_search` " ;
    // $query = "SELECT * FROM prod_search WHERE prt_no LIKE \"%$trimm%\" OR descrp LIKE \"%$trimm%\" OR img LIKE \"%$trimm%\" ORDER BY prt_no DESC" ;
    // WHERE prt_no LIKE \"%$trimm%\" OR descrp LIKE \"%$trimm%\" OR img LIKE \"%$trimm%\" ORDER BY prt_no DESC --- Execute the query to get number of rows that contain search kewords
    $numresults=mysql_query ($query);
    $row_num_links_main =mysql_num_rows ($numresults);

    // next determine if 's' has been passed to script, if not use 0.
    // 's' is a variable that gets set as we navigate the search result pages.
    if (empty($s)) {
    $s=0;
    }

    // now let's get results.
    $query .= " LIMIT $s,$limit" ;
    $numresults = mysql_query ($query) or die ( "Couldn't execute query" );
    $row= mysql_fetch_array ($numresults);

    //store record id of every item that contains the keyword in the array we need to do this to avoid display of duplicate search result.
    do{
    //EDIT HERE and specify your field name that is primary key
    $adid_array[] = $row[ 'prt_no' ];
    }while( $row= mysql_fetch_array($numresults));
    } //end foreach

    if($row_num_links_main == 0 && $row_set_num == 0){
    $resultmsg = "<p>Search results for:" . $trimmed ."</p><p>Sorry, your search returned zero results</p>" ;
    }
    //delete duplicate record id's from the array. To do this we will use array_unique function
    $tmparr = array_unique($adid_array);
    $i=0;
    foreach ($tmparr as $v) {
    $newarr[$i] = $v;
    $i++;
    }

    // now you can display the results returned. But first we will display the search form on the top of the page
    ?>

    <form action="search2.php" method="get" name="search">
    <div align="center">
    <input name="q" type="text" value=" <?php echo $q; ?> " size="15">
    <input name="search" type="submit" value="Search">
    </div>
    </form>

    <?php
    // display what the person searched for.
    if( isset ($resultmsg)){
    echo $resultmsg;
    exit();
    }else{
    echo "Search results for: " . $var;
    }

    foreach($newarr as $value){

    // WHERE prt_no LIKE \"%$trimm%\" OR descrp LIKE \"%$trimm%\" OR img LIKE \"%$trimm%\" ORDER BY prt_no DESC ---- EDIT HERE and specify your table and field names for the SQL query
    $query = "select * from prod_search where ".$trimm." like '%".$trimm."%'";
    //$query_value = " SELECT * FROM `prod_search` ";
    //$query_value = "SELECT * FROM prod_search WHERE prt_no LIKE \"%$trimm%\" OR descrp LIKE \"%$trimm%\" OR img LIKE \"%$trimm%\" ORDER BY prt_no DESC";
    //$query_value = "SELECT prt_no FROM prod_search WHERE descrp LIKE \"%$trimm%\" OR img LIKE \"%$trimm%\" ORDER BY prt_no DESC" ;
    $num_value=mysql_query ($query_value);
    $row_linkcat= mysql_fetch_array ($num_value);
    $row_num_links= mysql_num_rows ($num_value);

    //now let's make the keywods bold. To do that we will use preg_replace function.
    //EDIT parts of the lines below that have fields names like $row_linkcat[ 'field1' ]
    //This script assumes you are searching only 3 fields. If you are searching more fileds make sure that add appropriate line.
    $image = preg_replace ( "'($var)'si" , "<b>\\1</b>" , $row_linkcat[ 'img' ] );
    $partno = preg_replace ( "'($var)'si" , "<b>\\1</b>" , $row_linkcat[ 'prt_no' ] );
    $description = preg_replace ( "'($var)'si" , "<b>\\1</b>" , $row_linkcat[ 'descrp' ] );
    $price = preg_replace ( "'($var)'si" , "<b>\\1</b>" , $row_linkcat[ 'price' ] );
    $storelink = preg_replace ( "'($var)'si" , "<b>\\1</b>" , $row_linkcat[ 'str_lnk' ] );
    $speclink = preg_replace ( "'($var)'si" , "<b>\\1</b>" , $row_linkcat[ 'spc_lnk' ] );
    $manuallink = preg_replace ( "'($var)'si" , "<b>\\1</b>" , $row_linkcat[ 'man_lnk' ] );

    foreach($trimmed_array as $trimm){
    if($trimm != 'b' ){
    //IF you added more fields to search make sure to add them below as well.
    $image = preg_replace( "'($trimm)'si" , "<b>\\1</b>" , $image);
    $partno = preg_replace ( "'($trimm)'si" , "<b>\\1</b>" , $partno);
    $description = preg_replace ( "'($trimm)'si" , "<b>\\1</b>" , $description);
    $price = preg_replace ( "'($trimm)'si" , "<b>\\1</b>" , $price);
    $storelink = preg_replace ( "'($trimm)'si" , "<b>\\1</b>" , $storelink);
    $speclink = preg_replace ( "'($trimm)'si" , "<b>\\1</b>" , $speclink);
    $manuallink = preg_replace ( "'($trimm)'si" , "<b>\\1</b>" , $manuallink);

    // $linkhigh = preg_replace( "'($trimm)'si" , "<b>\\1</b>" , $linkhigh);
    // $linkdesc = preg_replace( "'($trimm)'si" , "<b>\\1</b>" , $linkdesc);
    }
    //end highlight

    ?>
    <p>
    <?php echo $image; ?><br>
    <?php echo $partno; ?><br>
    <?php echo $description; ?><br />
    <?php echo $price; ?><br>
    <?php echo $storelink; ?><br>
    <?php echo $speclink; ?><br />
    <?php echo $manuallink; ?>
    </p>

    <?php
    } //end foreach $trimmed_array
    if($row_num_links_main > $limit){
    // next we need to do the links to other search result pages
    if ($s>=1) { // do not display previous link if 's' is '0'
    $prevs=($s-$limit);
    echo "<div align='left'><a href='$PHP_SELF?s=$prevs&q=$var&catid=$catid'>Previous " .$limit. "</a></div>";
    }
    // check to see if last page
    $slimit =$s+$limit;
    if (!($slimit >= $row_num_links_main) && $row_num_links_main!=1) {
    // not last page so display next link
    $n=$s+$limit;
    echo "<div align='right'><a href='$PHP_SELF?s=$n&q=$var&catid=$catid'>Next " .$limit. "</a></div>";
    }
    }
    } //end foreach $newarr
    ?>

    I have pretty much messed it up not finding the right query....the output which i require from the database which has 7 colums and no primary key(suggest if i require or not)...

    Results to be displayed:
    --------------------------------------------------------------------------
    | img | part no: | PDF link | PDF link |
    | | Descrption: | | |
    | | Price: | | |
    | | link to online store | | |
    --------------------------------------------------------------------------

    each row will be having the same details on the keyword search....from the database

    some body help me in searching and showing the results page formated in this way...
    my fields in database are:
    - img(image location link on the database)
    - part no (XX-XX-XX)
    - description (200 carchar blah blah)
    - Price ($2345.00)
    - online store link (href link on the database)
    - specs PDF link (link to the specs location on the database)
    - manual PDF link (link to the manual location on the database)

    ...request anybody to help me achieving this...!!!

    Thanks
    Sanju

  2. #2
    Join Date
    May 2007
    Posts
    63
    Looks like you have incorrectly set the search query, check this line

    $query = "select * from prod_search where ".$trimm." like '%".$trimm."%'";

    If I am searching for a Wii for example then your query reads

    select * from prod_search where Wii like %Wii%

  3. #3
    Join Date
    Feb 2008
    Posts
    20
    the-ferret,

    what should be the correct query for the sql to run.....it gives error on line 64 i.e. $row_num_links_main =mysql_num_rows ($numresults);

    help me in fixing this!!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles