Page 1 of 1
LIKE problem
Posted: Tue Sep 04, 2007 7:30 pm
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'";
Posted: Tue Sep 04, 2007 7:42 pm
by Zoxive
Posted: Tue Sep 04, 2007 8:01 pm
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.
Posted: Wed Sep 05, 2007 7:45 am
by playgames
like '%{$var}%'
u may use fulltext . match(...) aginst(......)
Posted: Wed Sep 05, 2007 7:48 am
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'";
Posted: Wed Sep 05, 2007 7:51 am
by RobertGonzalez
WHERE 'term' IN ('terma', 'termb', 'termc')
Posted: Wed Sep 05, 2007 9:34 am
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.
Posted: Wed Sep 05, 2007 10:07 am
by psurrena
meaning?
Posted: Wed Sep 05, 2007 10:15 am
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
);
Posted: Wed Sep 05, 2007 12:01 pm
by psurrena
Yes, that is the solution I ended up using. Is it the most efficient or just the easiest to understand right now?
Posted: Wed Sep 05, 2007 12:17 pm
by RobertGonzalez
Typically that is the most efficient, most normalized manner of maintaining and selecting data.
Posted: Wed Sep 05, 2007 12:21 pm
by psurrena
Thanks for all your help everyone!