From SELECT to intersect

[roygbiv]

I am using a form, MySQL and PHP to populate an otherwise empty table. Each entry in the table is a partial descrition of a certain number of related insects. I want to see what the intersection is of all the sets of the entries in this table, eventually reducing the possibilities to one (a positive identification of that insect). But I do not know how to go from "$query = SELECT ..." to, for example, "foreach ($array as $key => $value)".
Basically, I want to start with a "SELECT" and then use "array_intersect()", but I do not know how to get each $result into a separate array so I can intesect them. Advice?
Greetings,
Roy

[HalfaBee]

I am lost, but this is how to get the data into an array

while( $a[] = mysql_fetch_assoc( $res ) ) ;
array_pop( $a ); // pops off the FALSE off the end of the array;

[leatherback]

Hey Roy,

Your question is not clear. Possibly because you are trying to achieve something symple in a vary complex way. Could you please explain in more detail what you are doing, and why you cannot get the correct data from your database in one go?

[roygbiv]

Thanks for your replies. I'll give an example of what I'm trying to do.
I am trying to aid determination of insects with a database key. One selects pre-defined characteristics with radio buttons. If you select "yellow thorax," that gets entered into a table and it is related to the following insects: Ectemnius dives, Ectemnius borealis and Ectemnius guttatus. Another selection, "Brown spot on wings" is related to Ectemnius guttatus and Ectemnius sexinctus. The intersection of these two selections is only one insect: Ectemnius guttatus. This could, in a perfect world, be considered a positive identification.
I am able to retrieve information from MySQL. But MySQL has no "intersection" function that I know of, only UNION, and that's not quite what I want. I assume I need to populate arrays in PHP and use array_intersect() to find out what the intersection is of all the radio bottons one has selected. And print that.
This is a bit wordy, but I hope now it's clear.
Greetings,
Roy

[leatherback]

Why don't you just select for both traits?

select * from TABLE where (thorax_colour = 'yellow') and (wings='brown') and (pattern= 'spotted')

which would be the usual way of approaching this. I cannot look into your database, to see how the traits are stored, but I presume something like this should be feasable, without going into custom array-manipulation functions.

[Roger_Ramjet]

Roy, Codd's relational model and therefore relational databases are founded upon predicate logic and set theory; and the correct method of querying the contents is through the intersection of sets. So sql does not contain any any specific 'intersection' functions because sql is already an 'intersection function'.

[roygbiv]

Thanks for putting me back on what is probably the right track. But I'm still not there yet. I'll explain the structure of my database and tables, then show you a sample of an MySQL "SELECT" command. And my question is basically: what's wrong with the syntax or logic of my "SELECT" command?
Like I said before, it's a database to help identify certain insects. Their names are all listed in the table "names," along with a name_id. There are about 10 tables with titles consisting of names of body parts and the entries are various desriptions. I'll mention two of them to keep the example shorter: head and antenne. each description has a description_id. Certain descriptions belong to certain insects, so they are related by means of rel_name_head and rel_name_antenne tables. A user selects certain descriptions by means of a radio button form. The selections are entered into a new table called "results," with a unique key on the description. I am trying to determine the name of the insect by looking at which descriptions get entered into the table "results." I compare the descriptions in "results" with the descriptions held in the original tables "head" and "antenne" and then travel over the "rel_name_head" and "rel_name_antenne" tables to find the entries in "names" that are related to the choices. The intersection set of all of the entries in "results" is the name of the insect I'm looking for.
Is this logic acceptable? I knew of no better, expandable way to do this using a form.
Now for the syntax. I am using AND...AND...AND, and it's not working:

$query2 = "SELECT n.name FROM names n, resultats res, antenne ant, rel_name_antenne rel_n_ant, head he, rel_name_head rel_n_he WHERE res.description = ant.description AND ant.antenne_id = rel_n_ant.antenne_id AND n.name_id = rel_n_ant.name_id AND res.description = he.description AND he.head_id = rel_n_he.head_id AND n.name_id = rel_n_he.name_id";
$results2 = mysql_query($query2)or exit(mysql_error());
        echo "<h3>List possible identifications</h3>";
        echo '<ul>';
        while( $row2 = mysql_fetch_assoc( $results2 ) ) {
        foreach($row2 as $value2) {
          echo '<li>';
          echo $value2;
          echo '</li>';
          }
        }
        echo '</ul>';

But this isn't working. Do I need to use IF or some other conditional instead of AND...AND...AND?
Thanks,
Roy

[Roger_Ramjet]

Before I go any further I'm going to tell you to go anr read about JOINS and then to use them. Also please use the [ p h p ] ... [ / p h p ] tags (take the spaces out) to make your posted code more readable - you'll get more answesr then.

Using just a comma for a join you get a cartesian join = every row joined to every row, unneccessary load on the server. If you qualify the join in the where clause you confuse everyone as to what is a true where condition and what is just a join condition.

As to this problem: generic solution:

[edit] Whoops too quick on that - won't work - wrong solution, teach me to watch TV at the same time :o

Have a column for each body part in the insects table: if an insect has no antenne then store NULL in that column etc, otherwise you store the ant_id etc

table insects
insect_id
insect
head
antenne
thorax
abdomen
etc for all 10 characteristic

table antenne
ant_id
description

(or you could have 1 table with a body_part column, up to you)

<input type="radio" name="antenne" value="$ant_id" />

// then process the input into your query
foreach ($POST as $key=>$val) {
  if ($key != 'submit') {
     $ary[] = $key . "=" . $val ;
  }
}

$where = implode(" AND ", $ary);
$sql = "SELECT insect FROM insects WHERE " . $where;

[HalfaBee]

2 extra comments.

Any data used for querys should be "cleaned" before use.
$antenne= mysql_real_escape_string( $_POST['antenne'] );

and you need to put the string in 's.
c.char_id='$antenne'

[Roger_Ramjet]

No need to quote the ids as they are integers but protecting against sql injection is a good idea. Personally I use PDO prepared statements which handles that for you. Let me know if you want help using PDO.

[roygbiv]

Roger and others: thanks. A question or two to help me clear things up.
I am borrowing the descriptions from a binary determination table written by a colleague of mine. I'm stuck with his logic and text. Occasionally he uses two or three partial descriptions for one body part. And various insects share one, two or three of those descriptions. Is there room in your scheme for multiple descriptions for one body part?

Now I finally see where the actual descriptions would go and how you would join the tables (I too was watching TV over my shoulder).

Will your form still work one radio button-selection at a time and allow for corrections (if the result set becomes NULL, then chances are one has used a description which is not correct).
And yes, please let me know more about PDO.
Thanks,
Roy

[leatherback]

Hey Roy,

No. That would not work. You would have to go with Roger's second suggestion: Store the body part in a colum:
You would get a tablecharacteristics:

Characteristic:
Insect_nr => Referes to the insect ID in table insects
body_part_nr => Refers to the id of the bodypart in your table bodyparts
characteristic => The actual characteristic

Your query would have to look like:
select distinct insect_id, , count() as no_matches from insects
left join
...
where (
(
((body_part_nr = $bodypart1) && (characteristic = $characteristic1))
OR
((body_part_nr = $bodypart2) && (characteristic = $characteristic2))
)
AND no_matches = $number_of_descriptors)

where you would have the $number_of_descriptors the same as the number of descriptions passed into the query.

[Roger_Ramjet]

Well there is not with the corrected version: 1 column per part; but there was in the first version which had a joining table (I call them a matrix-table to myself). In fact, breaking many:many relations is normally done with a matrix like that.

If you want to tunnel down with user input 1 characteristic at a time then that makes things easier. I presume that a choice at one level elliminates some choices from the next level.

table insects
ins_id
insect

table heads
head_id
type

table ins_heads
ins_id
head_id

table antenne
ant_id
type

table ins_ants
ins_id
ant_id

etc

You don't use radio buttons for multiple choice so you're gonna have to assume the user is sensible with the input. So user choose 1 or more head types and then you want to present the possible antenne types for the next step.

foreach ($_POST as $key=>$val) {
   if ($key != 'submit') {
      $ary[] = $val;
   }
}
$list = implode(',' ,$ary());

// now get the possible antenne for the head choices
$sql1 = "SELECT a.* FROM antenne a INNER JOIN ins_ants ia ON(a.ant_id=ia.ant_id)
INNER JOIN insects i  ON(ai.ins_id=i.ins_id)
INNER JOIN ins_heads ih  ON(i.ins_id=ih.ins_id)
WHERE ih.head_id IN ($list)
GROUP BY a.ant_id, a.type";

Now I'm sure that you can refactor this rather than have 9 or 10 queries but get it working first.

[edit]
Watching CSI so it took ages to post :o . Yep, first instincts correct as leatherback sez. So instead of joining to all those tables you have to join to the chars table multiple times: makes the query structure much more generalised immediately, just substitute vars for body-part in the same query as you tunnel down.

[roygbiv]

OK, let me work on this. It could take a few weeks. If there's something I don't understand, you'll hear from me soon(er).
Thanks,
Roy

[roygbiv]

Hello,
I need help with a join. I've tried self joins, but I haven't got them to work.
I've decided not to follow previous advice (above) because it resembles binary identification tables that I am trying to get away from. So what I am left with is this: A short version of my database consists of

database
Insects

table names
name
name_id

table face
description
face_id

table antenne
description
antenne_id

table eyes
description
eyes_id

table rel_name_face
name_id
face_id

table rel_name_antenne
name_id
antenne_id

table rel_name_eyes
name_id
eyes_id

table results
description (unique key)

Table "results" is normally empty and only gets populated by choices entered by means of a radio-button form, one choice at a time. The choices consist of the entries found in the columns face.description, antenne.description and eyes.description. The purpose of the MySQL query is to compare results.description with face.description, antenne.description and eyes.description, note any matches and return any fields from names.name that are related to the selections found in results.description . There is a many-to-many relationship between face.description, antenne.description and face_description AND names.name .
With a SELECT...UNION I can get a list of all possible names.name based on results.description.

SELECT n.name FROM names n, results res INNER JOIN rel_name_antenne rel_n_ant ON(n.naam_id=rel_n_ant.naam_id) INNER JOIN antenne ant ON(rel_n_ant.antenne_id=ant.antenne_id) WHERE res.description = ant.description 
UNION SELECT name FROM names n, results res INNER JOIN rel_naam_face rel_n_face ON(n.naam_id=rel_n_face.naam_id) INNER JOIN face ON(rel_n_face.face_id=face.face_id) WHERE res.description = face.description
UNION SELECT name FROM names n, results res INNER JOIN rel_naam_eyes  ON(n.name_id=rel_n_eyes.name_id) INNER JOIN eyes ON(rel_n_eyes.eyes_id=eyes.eyes_id) WHERE res.description = eyes.description

But I want the ever decreasing intersection set of names.name based on all the fields in results.description . Ideally res.description(id=1) is related to names.name (id=1),(id=5) and (id=6); res.description (id=2) is related to names.name (id=1) and (id=3) and res.description (id=3) is related to res.description (id=1) and (id=4). The intersection set is res.description (id=1)
I've tried variations on SELECT...UNION, self joins and WHEN EXISTS(SELECT...) but nothing quite returns what I'm loking for. Any suggestions?
-Roy