simple MYSQL search string help.?

Question

My table contains last name, first name and phone numbers. I want to create a page in which if someone types phone number it will display the first and last name associated with it.

Please Not: Fist Name, Last Name and Phone Number are different fields.

Image: http://img246.imageshack.us/my.php?image=databseak6.png

Thank You,
Desi

Answer 1

I assunme that your Table name is TBL_PHONEBOOK
It is a must you have a unique ID to your Database.
Add a one more fied (Auto Number) such as PERSON_ID.. I will tell you why later....
This is the query string

SELECT PERSON_ID, FIRST_NAME, LAST_NAME, PHONE_NO
FROM TBL_PHONEBOOK
WHERE PHONE_NO=@phno

Fill your Data Set with this Query String...
In your ASP or C# code, get the value for phno(variable)
SQL Query string will search the Table and will return the Row which contains data. And for this ... you need to have a unique ID for each record. thats why I ask you to add the PERSON_ID field which is a Key and a unique ID.

Answer 2

First, create an Index on the Phone Number field, then use a search string similar to:

SELECT First, Last FROM Tablename WHERE Phone='xxx-xxxx'

Replace First and Last with the actual field names in your database. Replace Tablename with the name of your table in the database.

You can replace 'xxx-xxxx' with a varible ('$Varname').

You dont have to create the index, but it will make the search run faster.

Answer 3

I don't know how MySQL would differ from MS-SQL, which I /am/ familiar with.

At runtime, pass this query to the database:

select first + ' ' + last from [table] where phone = @pPhNbr

--where @pPhNbr is a properly formatted phone number entered by the user.

Answer 4

//Split post by words and build the query $words_query = ""; $words = split(' ', $_POST["search"]); for ($i = 0; $i < count($words); $i++) { if ($words_query == "") $words_query .= "WHERE field LIKE '%" . $words[$i] . "%'"; else $words_query .= " OR field LIKE '%" . $words[$i] . "%'"; } //Check here that words_query is valid if ($words_query != "") { $result = mysql_query("SELECT * FROM table " . mysql_real_escape_string($words_query)); while($r=mysql_fetch_array($result)) { //display results code } } Be VERY carefuly about how you let your user interact with SQL through your site. The function mysql_real_escape_string() will remove any characters that they could use to maliciously affect your website and its data. As a quick example with your original code, if someone set 'search' as "%'; DELETE * FROM table; --" all your data would have been cleared.

Answer 5

here you can find some tutorials or scripts you can modify

http://www.hotscripts.com