[SOLVED] I need help with a search.

[DrTrans]

What im trying to do is this:

 

#1. when someone goes to my site and types in a box " John Smith " it looks into the database under " fname, and lname" and displays all records with that name, or similar to that name. i also want them to be able to type jOhn smith. and it show up so it not be case sensitve.

 

im sure it would be a sql statement. im just trying to get this working.

[GoneNowBye]

First of all break the input into chunks.

 

$data explode(" ",$string); will work

 

$data[0] now = 'john'

$data[1] now = 'smith'

 

Now.

 

Select * from database table where lname like '%$data[1]%' and fname like '%$data[0]%'

 

The % mean anything

 

so something%

 

means any record where the field starts with something.

 

good luck

[xtopolis]

Case sensitivity is based on the collation of your table.  The default collation, latin1, is case insensitive.

 

Alternatively, you can use a PHP function to set the input to lowercase before querying MySQL.  Be careful if you choose to follow Havok's suggestion as using dual wildcards, one on each end, may produce unexpected results.

[DrTrans]

Heres my temp code

 

if($submit) {

$searchc = explode(" ",$search);
$searchc[0]; 
    $searchc[1];
     $connect = mysql_connect("localhost","damgears_evil","damgears");
     mysql_select_db("damgears_evil");
     $query = mysql_query("SELECT * FROM titles WHERE fname LIKE '%$searchc[0]%'");

     while($row = mysql_fetch_assoc($query))
	{
		$dbid = $row['id'];
		$dbfname = $row['fname'];
		$dblname = $row['lname'];
		$dbkey = $row['key'];
		$dbskey = $row['skey'];
		$dbname = $row['sname'];
		$title = $row['title'];
		$dbstatus = $row['status'];
		$dbdate = $row['date'];
		$region = $row['region'];
		echo "$fname";
print "<center><b><a href=\"profile.php?fname=$dbfname&lname=$dblname\">$dbfname $dblname</a></b><br>";

	}


}

 

However...in the datbase there could be

 

John Smith <-- Same Person

John Smith <-- Same Person

John Sax

John Alverez <--

John Alverez <-- would be the same person

 

How do i get it to only state ( 1 ) of thenames and go on to the next one.

 

so it reads :

 

John Smith

John Sax

John Alverez

 

 

[DavidAM]

If the first and last name is all that you are interested in, the query would be:

SELECT DISTINCT fname, lname FROM titles WHERE ...

that will give you only rows where the combination is unique.

 

If you want to summarize some of the other data, you could do something like:

SELECT fname, lname, COUNT(title) as Articles FROM titles WHERE ... GROUP BY fname, lname

which will return one row for each unique combination of first and last name along with a count of the number of titles associated with the combination.

 

[Kaboom]

use

$_POST['search'] = strtolower($_POST['search']);

to have it goto lowercase

[GoneNowBye]

i used like '%$data[0].... so on

 

use or instead of and.