Searching Multiple fields in record

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pbsperry
Forum Newbie
Posts: 6
Joined: Thu Aug 15, 2002 1:27 pm

Searching Multiple fields in record

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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."%' ";
pbsperry
Forum Newbie
Posts: 6
Joined: Thu Aug 15, 2002 1:27 pm

Worked, sort of...

Post 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 :?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
Post Reply