Page 1 of 1
I Need MySql Statement for this
Posted: Fri Mar 27, 2009 8:40 pm
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.
Re: I Need MySql Statement for this
Posted: Fri Mar 27, 2009 10:02 pm
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.
Re: I Need MySql Statement for this
Posted: Sat Mar 28, 2009 1:06 am
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.
Re: I Need MySql Statement for this
Posted: Sat Mar 28, 2009 1:14 am
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.
Re: I Need MySql Statement for this
Posted: Sat Mar 28, 2009 5:17 am
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.
Re: I Need MySql Statement for this
Posted: Sat Mar 28, 2009 1:52 pm
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
Re: I Need MySql Statement for this
Posted: Sat Mar 28, 2009 7:29 pm
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.