it's simple but yet complicated...php and mysql search..

[desiguru]

I fond some tutorial that can search one field of the table in a database,. But I would like to search the whole table.. In my table I have people's name by first name, last name, phone number etc... how can I display the whole thing as a result? For example if someone was to type only John... the results would show, John's first name, last name, phone number... etc....

[Lafinboy]

You'll need to use a bit of conditional testing to build the appropriate sql string based on the submitted search terms. A simplified example shown below assumes the user can chooseto search on either firstname, lastname or both.

[PHP]$fname = $_POST['firstname'];
$lname = $_POST['lastname'];
$where = array();

if (isset( $fname ) && $fname!= "") {
$where[] = "(firstname LIKE '%$fname%')";
}
if (isset( $lname ) && $lname!= "") {
$where[] = "(lastname LIKE '%$lname%')";
}

$sql = ( "SELECT firstname, lastname, phonenr"
. "\nFROM tablename"
. (count( $where ) ? "\nWHERE " . implode( ' AND ', $where ) : "")
. "\nORDER BY lastname, firstname" );[/PHP]

[desiguru]

I understand this concept but, I need only one search box.


I have attached some images, which will help you further to understand the concept...

Image 1 shows my tabel. Image 2 shows my table.... and the last image shows how I want to output...

And finally thanks very much for your help!!!!!!!!!

[Lafinboy]

What are the search parameters you want to handle? Does the single search box mean that users will only search on firstname, or possibly firstname and lastname, or possibly lastname only? Or all three?

[desiguru]

All of the three.

[pritaeas]

You can use

SELECT * FROM tablename WHERE CONCAT(firstname,lastname,phonenr) LIKE '%$search%'

[EFEXConsulting]

btw, you should not play to hard with Like '%%', especially if you will have pager there (limit) as well as ability to sort by some fields (order by).
if you need - i can a short storty about doing that on big "for gamers" site.
briefly, try just to find some good book on database design, and read what can be done, what should be done and how :)

best regards

[desiguru]

btw, you should not play to hard with Like '%%', especially if you will have pager there (limit) as well as ability to sort by some fields (order by). if you need - i can a short storty about doing that on big "for gamers" site. briefly, try just to find some good book on database design, and read what can be done, what should be done and how :)

best regards

I will try my best and if I have further questions, I will post them here. Thanks for your help :idea:

[Dean C]

Do not use LafinBoy's code, it opens up a security risk. You should always sanitize your incoming post data.

[mkhalid]

In mysql, you can use "FULL TEXT SEARCH" option. For this you have to add index on all of three fields First name, Last name and Phone.
After that you will use the query

SELECT rowid, (match (firstname, lastname, phone) against ('$keyword')) as score 
FROM contact 
where (match(firstname, lastname, phone) against ('$keywork'))

OR

SELECT rowid, (match (firstname, lastname, phone) against ('$keyword')) as score 
FROM contact 
where (match(firstname, lastname, phone) against ('$keywork') IN BOOLEAN MODE)

For detail about the full text search you can read http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html

[boo_lolly]

i have a question, what if you had many search fields to search a table. for instance, if you had a table with many columns (that you would search through) like first name, last name, birth date, how would you search for all three queries and return only results that matched the user's input data?

for instance, if they wanted to search for all the people in my_search_table who had the first name 'Glen' AND the last name 'Lambert' AND who have a birthdate of 02/05/82. how can this be done in the query? something like
[php]
$result = mysql_query("SELECT first_name, last_name, birth_date
FROM my_search_table
WHERE first_name LIKE '%". flname ."%' AND last_name LIKE '%". $lname ."%' AND
birth_month LIKE '%". $birth_month ."%' AND
birth_day LIKE '%". $birth_day ."%' AND
birth_year LIKE '%". $birth_year ."%'")
or die(mysql_error());
[/php]
how would you have that effect?

[hazel_0821]

I fond some tutorial that can search one field of the table in a database,. But I would like to search the whole table.. In my table I have people's name by first name, last name, phone number etc... how can I display the whole thing as a result? For example if someone was to type only John... the results would show, John's first name, last name, phone number... etc....

this is the code:

if you type john all the j will appear on the search table...
please ym me or email me at [EMAIL="[e-mail]"][e-mail][/EMAIL] for more questions


<?php
$con = mysql_connect("localhost","root");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("employees", $con);
$sql="INSERT INTO employee_data (emp_id,f_name,l_name,title,age,yos,salary,perks,email)
Values('$_POST[emp_id]','$_POST[f_name]','$_POST[l_name]','$_POST[title]','$_POST[age]','$_POST[yos]','$_POST[salary]','$_POST[perks]','$_POST[email]')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";
mysql_close($con);
?>

[desiguru]

Thanks everyone for your help. Everything is now working fine. I tried all of the codes. After playing around wit them they work perfect...