I Need MySql Statement for this

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
it2051229
Forum Contributor
Posts: 312
Joined: Tue Dec 25, 2007 8:34 pm

I Need MySql Statement for this

Post by it2051229 »

I have three tables in the mysql database. Let's say:

Table: Human -> Fields: human_id (primary key), name
Table: Attribute -> Fields: attribute_id (primary key), name
Table: Human_Attribute -> Fields: human_id (foreign key to human table), attribute_id (foreign key to attribute table)

Cardinality: Human Table to Human_Attribute Table (1 to Many)
Cardinality: Attribute Table to Human_Attribute Table (1 to Many)

My Objective is to search a Human given the attributes. So i created an interface where the user selects the attributes of the human and then given those attributes, I have to find all humans that has those selected attributes.

Can anyone help me with the SQL statement for this? I know that basic queries won't work for this.
tech603
Forum Commoner
Posts: 84
Joined: Thu Mar 19, 2009 12:27 am

Re: I Need MySql Statement for this

Post by tech603 »

Table: Human -> Fields: human_id (primary key), name
Table: Attribute -> Fields: attribute_id (primary key), name
Table: Human_Attribute -> Fields: human_id (foreign key to human table), attribute_id (foreign key to attribute table)

Lets say attribute that is selected by the user has an id of 3

select human_id, name from humanTable where Human_Attribute.attribute_id = 3 AND human_id = Human_Attribute.human_id

This should grab all the human names from the database where the attribute id and human id match in the Human_Attribute table, or you could try doing this also with table joins.

hope that helps.
User avatar
it2051229
Forum Contributor
Posts: 312
Joined: Tue Dec 25, 2007 8:34 pm

Re: I Need MySql Statement for this

Post by it2051229 »

i've done that but there's a problem. You see a Human can have one or more attributes. What you have shown as an example is just a single attribute. So on my search interface, the user enters multiple attributes and then searches the human table that has those attributes. The search should be exact where only those human that has those exact attributes that has provided by the user should be queried.
tech603
Forum Commoner
Posts: 84
Joined: Thu Mar 19, 2009 12:27 am

Re: I Need MySql Statement for this

Post by tech603 »

You should be able to accomplish this with a foreach loop. When the users selects the attributes just push that data into an array and then loop over that array to do your queries.

foreach($attribute as $entry)

{
mysql statement .... where attribute = $entry

echo username to browser.
}

Hope that helps.
User avatar
it2051229
Forum Contributor
Posts: 312
Joined: Tue Dec 25, 2007 8:34 pm

Re: I Need MySql Statement for this

Post by it2051229 »

the problem there again is all of those human which has those each attribute will be queried.

Here's what I want to happen, let's say I have a human named john with an attribute of head = 1, arms = 2, and legs = 2. John is stored in the human table and the head, arms, and legs are stored on the attributes table (note: head, arms, and legs are not table fields but values of the field called "attribute_name").

Now on the interface, I provided three textfields where to put the value of head, arms, and legs. So let's just say that the user entered 1 for head, 2 for arms, and 2 for legs... Now the queried result of course is John Because john has those specific attribute.
tech603
Forum Commoner
Posts: 84
Joined: Thu Mar 19, 2009 12:27 am

Re: I Need MySql Statement for this

Post by tech603 »

I have to find all humans that has those selected attributes.
I'm sorry i was going by what you had said in your first post. You were looking for all humans for that given attribute, if you want to specify human an there attributes then instead of searching per attribute you would search per person.

Code: Select all

Select attribute_id, human_id, Human_Table.name, Attribute_Table.name From Human_Attribute 
Left Outer Join Human_Table On human_id = Human_Table.human_id
Left Outer Join Attribute_Table On attribute_id = Attribute_Table.attribute_id
Where human_id = $userrequestedvaluehere
 
something like that
hope that helps
User avatar
it2051229
Forum Contributor
Posts: 312
Joined: Tue Dec 25, 2007 8:34 pm

Re: I Need MySql Statement for this

Post by it2051229 »

... human_id = $userrequestedvaluehere
the user requested values are attribute ID and not human ID and there are multiple attribute IDs... anyways If I were to search each human.. then I have to query all the human and then one by one checking if they have that attribute... not to mention that the human database is large and looping to each and everyone of them is inefficient.
Post Reply