Page 1 of 1

Searching Multiple fields in record

Posted: Thu Apr 03, 2003 5:28 pm
by pbsperry
Help -

I'm looking for some code that will search multiple fields in one record. For example, I've got a database of people where there is a skill1, skill2, and skill3. Many people have the same skill, but not in the same field (some have the skill of "framing," for example, in the skill1 field, and others have it in the skill2 field, and so on). What I need is a script that queries the database and then displays the person and the skill that was searched for next to each person. What's happening is that it's returning the people correctly, but only displays the skill that's in the skill1 field. See the link below for an example and also for the code:

example of what's going on:
http://www.zeitco.com/skill.php

code is here:
http://www.zeitco.com/skillcode.htm

Thanks for the help

Posted: Thu Apr 03, 2003 6:40 pm
by volka
$Query1="select * from people where skill1 like '%".$searchterm."%'";
$Query2="select * from people where skill2 like '%".$searchterm."%'";
$Query3="select * from people where skill3 like '%".$searchterm."%'";
you can have multiple conditions in your where clause, e.g.

Code: Select all

$Query1="SELECT * FROM people WHERE skill1 LIKE '%".$searchterm."%' OR skill2 LIKE '%".$searchterm."%' OR skill3 LIKE '%".$searchterm."%' ";

Worked, sort of...

Posted: Fri Apr 04, 2003 9:25 am
by pbsperry
Thanks a bunch, that drastically slimmed down my code. However, it's still displaying only those skills found in "skill1". Any ideas on how to get it to display the skill that was searched for?

I've tried a bunch of different "if" statements to see if I can get it to display the skill that was searched for regardless of where it found it, but can't seem to get the right one. Thoughts?

BTW - tks for the heads up on localhost :?

Posted: Fri Apr 04, 2003 10:30 am
by volka
mysql could return the field that matched the criteria, but for simplicity let's perform a second search in php.
http://www.php.net/manual/en/function.stristr.php
The result of your query is a row (or more of them) that contains the values of skill1, skill2 and skill3. One of them matched.
Let's say you fetched the record via

Code: Select all

while($row = mysql_fetch_array($result))
{
...
}
now you can find the field with

Code: Select all

while($row = mysql_fetch_array($result))
{
...
// "skill" cell of the table
if (stristr($row['skill1'], $searchterm))
	echo $row['skill1'];
elseif(stristr($row['skill2'], $searchterm))
	echo $row['skill2'];
else
	echo $row['skill3'];
...
}
or something like that