SitePoint Sponsor

User Tag List

Results 1 to 20 of 20

Thread: search problem

  1. #1
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question search problem

    how do one make a search from database
    like if i search for a name sneha then the search shud give me all possible combinations of sneha
    like snehalata or snehas or snez shud also be displayed

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    How is "snez" a match for "sneha"?

    Code:
    SELECT * FROM table WHERE name LIKE 'sneha%'

  3. #3
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    n if i add the full name say
    sneha agarwal then?

  4. #4
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    searching for more then one parameter in the same database

  5. #5
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question sql retrieving

    i am using the following to search the database
    the prob is if i give three parameters to be searched say sneha,female,india
    then it is displaying all the users whose name is sneha,all the users whose gender is female,all the users whose country is india
    i want only those results which all three
    plz help
    PHP Code:
     <?php
     $var 
    $_GET['search'] ;
      
    $trimmed trim($var);
       
    //$var1 = $_GET['field'] ;
       
    $s $_GET['s'];
      
    //$trimmed1 = trim($var1); //trim whitespace from the stored variable
    $username=$_SESSION['username'];
    $limit=2

    // check for an empty string and display a message.
    if ($trimmed == "")
      {
    $resultmsg "<p>give a search parameter! </p>" ;  
      }

    // check for a search parameter
    if (!isset($var))
      {
    $resultmsg "<p>give a search parameter! </p>" ;
      }


    $trimmed str_replace(","" "$trimmed);
    //separate key-phrases into keywords
    $trimmed_array explode(" ",$trimmed);

    foreach (
    $trimmed_array as $trimm){
    // EDIT HERE and specify your table and field names for the SQL query
    $query "SELECT * FROM profile WHERE first_name LIKE \"%$trimm%\" OR last_name LIKE \"%$trimm%\" OR city LIKE \"%$trimm%\" OR state LIKE \"%$trimm%\" OR country LIKE \"%$trimm%\" OR gender LIKE \"%$trimm%\" ORDER BY userid " ;
    // Execute the query to get number of rows that contain search kewords
    $numresults=mysql_query ($query);
    //$numresults=(array_intersect(mysql_query ($query), $numresults));
    //echo $numresults;
    $row_num_links_main =mysql_num_rows ($numresults);


      if (empty(
    $s)) {
      
    $s=0;
      }
      else
      
    $s=$s;

    // get results
      
    $query .= " Limit $s,$limit";
      
    $result mysql_query($query) or die("Couldn't execute query");
      
    $rowmysql_fetch_array ($numresults);

    // display what the person searched for

    //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
    if($_SESSION['username'] != $row['username'])
    $adid_array[] = $row['userid'];
    }while( 
    $rowmysql_fetch_array($numresults));
    //end foreach


    if($row_num_links_main == && $row_set_num == 0){
    $resultmsg "<p>You searched for:" $trimmed ."</p><p>No results were found!</p>" ;

    }
    if( isset (
    $resultmsg)){
    echo 
    $resultmsg;
    exit();
    }else{
    $x =$s ;
    echo 
    "<p>Your results för :  &quot; $var &quot;</p></div>";
    }
    //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++;
    }
    ?>

    <form action="search.php" method="get" name="search">
    <div align="center">
    <input name="search" type="text"  size="45">
    <input type="submit" value="Search">
    </div>
    </form>

    <?php
    ................

  6. #6
    SitePoint Enthusiast MetalHippy's Avatar
    Join Date
    Nov 2008
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    Instead of using OR in your sql select statement use AND.

    Using AND will only return records that match all of the criteria.

    Hope that helps

    Mike
    One Life - Live It

  7. #7
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    no if i use and i ll have to know which field i m checking also
    here i don no

  8. #8
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sneha24 View Post
    no if i use and i ll have to know which field i m checking also
    here i don no
    You need to dynamically build your SQL statement based on which parameters are set. Append a string to your SQL query for each variable that is defined.

  9. #9
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    i din understand what u said
    could u plz explain me or make the reqd changes in the coding given

  10. #10
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is unrelated to your problem but I would recommend using fulltext boolean search instead of LIKE... much faster and flexible.

  11. #11
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    can anyone plz help me

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,252
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by sneha24 View Post
    can anyone plz help me
    what is your question?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i am using the following to search the database
    the prob is if i give three parameters to be searched say sneha,female,india
    then it is displaying all the users whose name is sneha,all the users whose gender is female,all the users whose country is india
    i want only those results which have all three
    plz help
    PHP Code:
    PHP Code:
     <?php
     $var 
    $_GET['search'] ;
      
    $trimmed trim($var);
       
    //$var1 = $_GET['field'] ;
       
    $s $_GET['s'];
      
    //$trimmed1 = trim($var1); //trim whitespace from the stored variable
    $username=$_SESSION['username'];
    $limit=2

    // check for an empty string and display a message.
    if ($trimmed == "")
      {
    $resultmsg "<p>give a search parameter! </p>" ;  
      }

    // check for a search parameter
    if (!isset($var))
      {
    $resultmsg "<p>give a search parameter! </p>" ;
      }


    $trimmed str_replace(","" "$trimmed);
    //separate key-phrases into keywords
    $trimmed_array explode(" ",$trimmed);

    foreach (
    $trimmed_array as $trimm){
    // EDIT HERE and specify your table and field names for the SQL query
    $query "SELECT * FROM profile WHERE first_name LIKE \"%$trimm%\" OR last_name LIKE \"%$trimm%\" OR city LIKE \"%$trimm%\" OR state LIKE \"%$trimm%\" OR country LIKE \"%$trimm%\" OR gender LIKE \"%$trimm%\" ORDER BY userid " ;
    // Execute the query to get number of rows that contain search kewords
    $numresults=mysql_query ($query);
    //$numresults=(array_intersect(mysql_query ($query), $numresults));
    //echo $numresults;
    $row_num_links_main =mysql_num_rows ($numresults);


      if (empty(
    $s)) {
      
    $s=0;
      }
      else
      
    $s=$s;

    // get results
      
    $query .= " Limit $s,$limit";
      
    $result mysql_query($query) or die("Couldn't execute query");
      
    $rowmysql_fetch_array ($numresults);

    // display what the person searched for

    //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
    if($_SESSION['username'] != $row['username'])
    $adid_array[] = $row['userid'];
    }while( 
    $rowmysql_fetch_array($numresults));
    //end foreach


    if($row_num_links_main == && $row_set_num == 0){
    $resultmsg "<p>You searched for:" $trimmed ."</p><p>No results were found!</p>" ;

    }
    if( isset (
    $resultmsg)){
    echo 
    $resultmsg;
    exit();
    }else{
    $x =$s ;
    echo 
    "<p>Your results för :  &quot; $var &quot;</p></div>";
    }
    //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++;
    }
    ?>

    <form action="search.php" method="get" name="search">
    <div align="center">
    <input name="search" type="text"  size="45">
    <input type="submit" value="Search">
    </div>
    </form>

    <?php
    ................

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,252
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, i fail to see how that further explanation is any different from your first post, which led to the subsequent questions about what you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I'm taking a stab in the dark and going to assume you're intending to build an SQL query dependent on the POST vars sent to the script?

    If so, maybe this will help a little??

    PHP Code:
    <?php
    $sSQL 
    'SELECT name, age FROM users WHERE 1 = 1';

    if(isset(
    $_POST['name']) && ! empty($_POST['name']))
    {
        
    $sSQL sprintf(
            
    "%s AND name LIKE '%%%s%%'",
            
    $sSQL,
            
    mysql_real_escape_string($_POST['name'])
        );
    }

    if(isset(
    $_POST['gender']) && ! empty($_POST['gender']))
    {
        
    $sSQL sprintf(
            
    "%s AND gender = '%s'",
            
    $sSQL,
            
    mysql_real_escape_string($_POST['gender'])
        );
    }
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  16. #16
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the prob is if i give three parameters to be searched say sneha,female,india
    i want only those results which all three
    The answers are here
    Instead of using OR in your sql select statement use AND.

    Using AND will only return records that match all of the criteria.
    and here

    You need to dynamically build your SQL statement based on which parameters are set. Append a string to your SQL query for each variable that is defined.
    Along with the above you will need to somehow specify which field(s) to search.

  17. #17
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    the problem is regarding the results of search option
    a user may enter anything in the text and i m supposed to search one table but all the fields from my database
    and display the results which matches all the options given

  18. #18
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    nothing still works
    i tried making these changes that using array_intersect butnow no output is being displayed
    PHP Code:
    <?php
    $tab 
    "forum";
      
    $nav "";
    require_once(
    "../content.php");
    mysql_connect("localhost","root","rootwdp"); 

    mysql_select_db("spob") or die("Unable to select database"); 
      
    // Get the search variable from URL
    $unique=0;
      
    $var $_GET['search'] ;
      
    $trimmed trim($var);
       
    //$var1 = $_GET['field'] ;
       
    $s $_GET['s'];
      
    //$trimmed1 = trim($var1); //trim whitespace from the stored variable
    $username=$_SESSION['username'];
    $limit=2

    // check for an empty string and display a message.
    if ($trimmed == "")
      {
    $resultmsg "<p>give a search parameter! </p>" ;  
      }

    // check for a search parameter
    if (!isset($var))
      {
    $resultmsg "<p>give a search parameter! </p>" ;
      }


    $trimmed str_replace(","" "$trimmed);
    //separate key-phrases into keywords
    $trimmed_array explode(" ",$trimmed);

    foreach (
    $trimmed_array as $trimm){
    // EDIT HERE and specify your table and field names for the SQL query
    $query "SELECT * FROM profile WHERE first_name LIKE \"%$trimm%\" OR last_name LIKE \"%$trimm%\" OR city LIKE \"%$trimm%\" OR state LIKE \"%$trimm%\" OR country LIKE \"%$trimm%\" OR gender LIKE \"%$trimm%\" ORDER BY userid " ;
    // Execute the query to get number of rows that contain search kewords
    $numresults=mysql_query ($query);
    //$numresults=(array_intersect(mysql_query ($query), $numresults));
    //echo $numresults;
    $row_num_links_main =mysql_num_rows ($numresults);


      if (empty(
    $s)) {
      
    $s=0;
      }
      else
      
    $s=$s;

    // get results
      
    $query .= " Limit $s,$limit";
      
    $result mysql_query($query) or die("Couldn't execute query");
      
    $rowmysql_fetch_array ($numresults);

    // display what the person searched for

    //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
    if($_SESSION['username'] != $row['username'])
    $adid_array[$unique] = $row['userid'];
    echo 
    $adid_array[$unique];
    }while( 
    $rowmysql_fetch_array($numresults));
    echo 
    "<br />";
    if(
    unique>=1){
    $adid_array[0]= array_intersect($adid_array[$unique-1], $adid_array[$unique]);
    echo 
    "<br />";
    echo 
    $adid_array[$unique];}
    $unique++;
    //end foreach


    if($row_num_links_main == && $row_set_num == 0){
    $resultmsg "<p>You searched for:" $trimmed ."</p><p>No results were found!</p>" ;

    }
    if( isset (
    $resultmsg)){
    echo 
    $resultmsg;
    exit();
    }else{
    $x =$s ;
    echo 
    "<p>Your results för :  &quot; $var &quot;</p></div>";
    }
    //delete duplicate record id's from the array. To do this we will use array_unique function
    //$tmparr = array_unique($adid_array[0]);
    $i=0;
    foreach (
    $adid_array[0] as $v) {
    echo 
    $i;
    $newarr[$i] = $v;
    $i++;
    }
    ?>

    <form action="search.php" method="get" name="search">
    <div align="center">
    <input name="search" type="text"  size="45">
    <input type="submit" value="Search">
    </div>
    </form>

    <?php

    // now you can display the results returned

      
    for($count=0;$count<$limit&&$x<$i;$count++){
     
    // EDIT HERE and specify your table and field names for the SQL query
    $query_value "SELECT * FROM profile WHERE userid = $newarr[$x]";
    $num_value=mysql_query ($query_value);
    $row_linkcatmysql_fetch_array ($num_value);
    $row_num_linksmysql_num_rows ($num_value);

    $linkimages =( $row_linkcat'photo_filename' ] );
    //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.
    $titlehigh preg_replace "'($var)'s" "<b>\\1</b>" $row_linkcat'first_name' ] );
    $linkhigh preg_replace "'($var)'s" "<b>\\1</b>" $row_linkcat'last_name' ] );
    $linkdesc preg_replace "'($var)'s" "<b>\\1</b>" $row_linkcat'city' ] );
    $linkdesc1 preg_replace "'($var)'s" "<b>\\1</b>" $row_linkcat'state' ] );
    $linkdesc2 preg_replace "'($var)'s" "<b>\\1</b>" $row_linkcat'country' ] );
    $linkdesc3 preg_replace "'($var)'s" "<b>\\1</b>" $row_linkcat'gender' ] );
    foreach(
    $trimmed_array as $trimm){
        if(
    $trimm!= 'b' ){
    //IF you added more fields to search make sure to add them below as well.
            
    $titlehigh preg_replace"'($trimm)'s" "<b>\\1</b>" $titlehigh);
            
    $linkhigh preg_replace"'($trimm)'s" "<b>\\1</b>" $linkhigh);
            
    $linkdesc preg_replace"'($trimm)'s" "<b>\\1</b>" $linkdesc);
            
    $linkdesc1 preg_replace"'($trimm)'s" "<b>\\1</b>" $linkdesc1);
            
    $linkdesc2 preg_replace"'($trimm)'s" "<b>\\1</b>" $linkdesc2);
            
    $linkdesc3 preg_replace"'($trimm)'s" "<b>\\1</b>" $linkdesc3);

        }
    //end if
    }//end foreach
    //end highlight
    ?>


    <table align="center" width="500"  cellspacing="0" cellpadding="0">
    <tr>
    <td rowspan="3" width="10"><img src="http://localhost/spob/profile/photo/<?php echo $linkimages;?>" width="100" height="75"></td>
        <td width="394" height="46"><a href="view_profile.php?userid=<?php echo $row_linkcat['userid'];?>"><?php echo $titlehigh;?> 
          - <?php echo $linkhigh;?></a></div></td>
    </tr>
    <tr>
        <td height="18"><font size="-1" color="#999999"><?php echo $linkdesc3;?></font></td>
      </tr>
    <tr>
    <td> <font size="-1" color="#999999"><?php echo $linkdesc;?> , <?php echo $linkdesc1;?> , <?php echo $linkdesc2;?>      </font></td>
    </tr>


    </table>
    <br> <?php 
    $x
    ++;
    }

    $currPage = (($s/$limit) + 1);

    //break before paging
      
    echo "<br />";

      
    // next we need to do the links to other results
      
    if ($s>=1) { // bypass PREV link if s is 0
      
    $prevs=($s-$limit);
      print 
    "&nbsp;<a href=\"$PHP_SELF?s=$prevs&search=$var\">&lt;&lt; 
      Prev " 
    .$limit"</a>&nbsp&nbsp;";
      }

    // calculate number of pages needing links
      
    $pages=intval($i/$limit);

    // $pages now contains int of pages needed unless there is a remainder from division

      
    if ($i%$limit) {
      
    // has remainder so add one page
      
    $pages++;
      }

    // check to see if last page
      
    if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

      
    // not last page so give NEXT link
      
    $news=$s+$limit;

      echo 
    "&nbsp;<a href=\"$PHP_SELF?s=$news&search=$var\">Next " .$limit" &gt;&gt;</a>";
      }

    $a $s + ($limit) ;
      if (
    $a $i) { $a $i; }
      
    $b $s ;
      echo 
    "<p>Showing results $b to $a of $i</p>";
      
    ?>

  19. #19
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Your code is far too complicated, break it down and simplify it. Get each component working as expected and move on to the next.

    Maybe you're trying to run before you can walk so to speak.

    Concentrate on building the SQL string dynamically first, test this outside PHP until you're happy the resultset yields the desired data.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  20. #20
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question


    i had worked it out for one particular keyword
    and it was working absolutely fine
    all i want is the code for serching where it is independent of keywords


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
  •