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

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....

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

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]

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

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!!!!!!!!!

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

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?

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

All of the three.

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

You can use

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

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

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

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

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

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

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

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

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/...xt-search.html