Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
psurrena
Forum Contributor
Posts: 355 Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY
Post
by psurrena » Tue Sep 04, 2007 7:30 pm
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'";
Zoxive
Forum Regular
Posts: 974 Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan
Post
by Zoxive » Tue Sep 04, 2007 7:42 pm
psurrena
Forum Contributor
Posts: 355 Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY
Post
by psurrena » Tue Sep 04, 2007 8:01 pm
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.
playgames
Forum Newbie
Posts: 22 Joined: Tue Sep 04, 2007 4:28 am
Post
by playgames » Wed Sep 05, 2007 7:45 am
like '%{$var}%'
u may use fulltext . match(...) aginst(......)
psurrena
Forum Contributor
Posts: 355 Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY
Post
by psurrena » Wed Sep 05, 2007 7:48 am
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'";
RobertGonzalez
Site Administrator
Posts: 14293 Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA
Post
by RobertGonzalez » Wed Sep 05, 2007 7:51 am
WHERE 'term' IN ('terma', 'termb', 'termc')
xpgeek
Forum Contributor
Posts: 146 Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:
Post
by xpgeek » Wed Sep 05, 2007 9:34 am
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.
psurrena
Forum Contributor
Posts: 355 Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY
Post
by psurrena » Wed Sep 05, 2007 10:07 am
meaning?
xpgeek
Forum Contributor
Posts: 146 Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:
Post
by xpgeek » Wed Sep 05, 2007 10:15 am
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
);
psurrena
Forum Contributor
Posts: 355 Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY
Post
by psurrena » Wed Sep 05, 2007 12:01 pm
Yes, that is the solution I ended up using. Is it the most efficient or just the easiest to understand right now?
RobertGonzalez
Site Administrator
Posts: 14293 Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA
Post
by RobertGonzalez » Wed Sep 05, 2007 12:17 pm
Typically that is the most efficient, most normalized manner of maintaining and selecting data.
psurrena
Forum Contributor
Posts: 355 Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY
Post
by psurrena » Wed Sep 05, 2007 12:21 pm
Thanks for all your help everyone!