LIKE problem

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
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

LIKE problem

Post by psurrena »

I've been having a problem with LIKE and want to know if I'm using it correctly.

Say I have a database with 100 projects all with unique numeric id's. I also have a table of employees with a field for the id's of the projects they worked on. The id's are stored exactly as "7 13 15". Maybe three employee's worked on project id 7, how do I return their names and not the names of the people who worked on 17, 77 and 37?

In the below query, I get no return if an employee has multiple projects and when I use %, I get everything containing a 7.

Any ideas?

Code: Select all

$pid=7;
$query="SELECT project_id, people_fname, people_lname, people_mem, people_id FROM people WHERE project_id LIKE '$pid'";
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Post by Zoxive »

Code: Select all

LIKE '%$var%'
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post by psurrena »

That's just what I don't want. That gives me 7, 17, 77 etc.

Here's an example:

employee 1 -> projects ="12 7 89 3"
employee 2 -> projects = "7 58 1"
employee 3 -> project = "17 85 77"

I only want people who worked on "7" which would be employee 1 and 2.
User avatar
playgames
Forum Newbie
Posts: 22
Joined: Tue Sep 04, 2007 4:28 am

Post by playgames »

like '%{$var}%'

u may use fulltext . match(...) aginst(......)
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post by psurrena »

I ended up creating a new table with a row for the employee ID and the project ID. Works well, don't know if it could be written better...

Code: Select all

$query="SELECT R.people_id, R.project_id, P.people_fname, P.people_lname, P.people_mem FROM people_project R, people P WHERE R.people_id=P.people_id AND R.project_id='$pid'";
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

WHERE 'term' IN ('terma', 'termb', 'termc')
User avatar
xpgeek
Forum Contributor
Posts: 146
Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:

Post by xpgeek »

psurrena wrote:That's just what I don't want. That gives me 7, 17, 77 etc.

Here's an example:

employee 1 -> projects ="12 7 89 3"
employee 2 -> projects = "7 58 1"
employee 3 -> project = "17 85 77"

I only want people who worked on "7" which would be employee 1 and 2.
Normalize your table structure.
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post by psurrena »

meaning?
User avatar
xpgeek
Forum Contributor
Posts: 146
Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:

Post by xpgeek »

psurrena wrote:meaning?
Instead of one table which contain relations user to projects - you need three tables.
First table of users.
Second table of projects.
Third table it is table with relations from users to projects, i.e.:

Code: Select all

#users
create table users(
user_id int,
name varchar(50)
);

#projects
create table projects(
project_id int,
name varchar(50)
);

#users_to_projects
craete table users_to_projects(
user_id,
project_id
);
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post by psurrena »

Yes, that is the solution I ended up using. Is it the most efficient or just the easiest to understand right now?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Typically that is the most efficient, most normalized manner of maintaining and selecting data.
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post by psurrena »

Thanks for all your help everyone!
Post Reply