search problem

[sneha24]

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

[Dan Grossman]

How is "snez" a match for "sneha"?

SELECT * FROM table WHERE name LIKE 'sneha%'

[sneha24]

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

[sneha24]

searching for more then one parameter in the same database

[sneha24]

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
 $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");
  $row= mysql_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( $row= mysql_fetch_array($numresults));
} //end foreach


if($row_num_links_main == 0 && $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
................

[MetalHippy]

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

[sneha24]

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

[SituationSoap]

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.

[sneha24]

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

[risoknop]

This is unrelated to your problem but I would recommend using fulltext boolean search instead of LIKE... much faster and flexible.

[sneha24]

can anyone plz help me

[r937]

can anyone plz help me

what is your question?

[sneha24]

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
 $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");
  $row= mysql_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( $row= mysql_fetch_array($numresults));
} //end foreach


if($row_num_links_main == 0 && $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
................

[r937]

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

[AnthonySterling]

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
$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'])
    );
}
?>

[spiderling]

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.

[sneha24]

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

[sneha24]

nothing still works
i tried making these changes that using array_intersect butnow no output is being displayed

<?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");
  $row= mysql_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( $row= mysql_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 == 0 && $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_linkcat= mysql_fetch_array ($num_value);
$row_num_links= mysql_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 + 1 ;
  echo "<p>Showing results $b to $a of $i</p>";
  
?>

[AnthonySterling]

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.

[sneha24]

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