tablePkId | userFkId | colorFkId |
----------+----------+-----------|
        1 |       11 |        22 | --
----------+----------+-----------|
        2 |       12 |        25 | YES
----------+----------+-----------|
        3 |       13 |        25 | YES
----------+----------+-----------|
        4 |       14 |        25 | --
----------+----------+-----------|
        5 |       14 |        25 | --
----------+----------+-----------|
        6 |       13 |        23 | no
----------+----------+-----------|
        7 |       12 |        22 | YES
----------+----------+-----------|
        8 |       11 |        23 | --
----------+----------+-----------|
        9 |       11 |        24 | --
----------+----------+-----------|
       10 |       12 |        24 | no
----------+----------+-----------|
       11 |       13 |        25 | YES
----------+----------+-----------|
       12 |       14 |        21 | --
----------+----------+-----------|
       13 |       14 |        25 | --
----------+----------+-----------|
       14 |       13 |        21 | YES
----------+----------+-----------|
       15 |       13 |        22 | YES
----------+----------+-----------|
       16 |       13 |        23 | no
----------+----------+-----------|
       17 |       12 |        25 | YES
----------+----------+-----------|
       18 |       12 |        24 | no
----------+----------+-----------|
       19 |       12 |        21 | YES
----------+----------+-----------|
       20 |       11 |        21 | --
---------------------------------|
QUESTION: 
Need a script and/or query (PHP / MySql) that results in a list
(array) of all "tablePkId"'s
filtered by a dynamically provided array of userFkId's (1-n; for
example userFkId 12 and 13)
where the applicable users have the same colorPkId. 
If the userFkId array would be (12,13) the resulting array of
tablePkId's in the above example
should be (2,3,7,11,14,15,17,19). 

I am sure there is a simple solution for this... I just don't seem to
be able to find it.

Klausb-ga,
Thanks for your interesting question!

The problem can be solved with the following SQL query which returns
an array of (2, 3, 7, 11, 14, 15, 17, 19) given input of (12, 13).  To
generalize this answer substitute your input variables for 12 and 13. 
Also substitute testtable for the name of your table.


SELECT DISTINCT T1.tablePkId FROM testtable T1, testtable T2 WHERE
(T1.userFkId=12 OR T1.userFkId=13)  AND (T2.userFkId=12 OR
T2.userFkId=13) AND (T1.userFkId=T2.userFkId-1 OR
T2.userFkId=T1.userFkId-1) AND T1.colorFkId=T2.colorFkId GROUP BY
T1.tablePkId ORDER BY T1.tablePkId

This works by selecting two copies of the same table, allowing you to
compare the data in one query, and not requiring PHP.

If you need any help or a clarification, don't hesitate to ask!

Sincerely,
Andyt-ga

---

Request for Answer Clarification by klausb-ga on 17 Jun 2004 23:19 PDT

Hi Andyt:
I did have something similar already...
//--- 

SELECT DISTINCT (a.colorFkId), a.userFkId, b.userFkId
FROM testtable a, testtable b
WHERE a.userFkId = '12' 
AND b.userFkId = '13' 
AND a.colorFkId = b.colorFkId 
//---
(And then 
Perhaps I was not clear in my original question. The problem I have is
that I do not know the userFkId's up front -- that is, they are
dynamic. So yes, they could be 12 and 13. Or they could just be (6);
or (1,2,3,4,5,6,7,8,9,...); or any other randon dynamic input.
So I would need to know is how to make this work with "a dynamically
provided array of userFkId's".

Thanks for your excellent attempt to help me with this problem. 
Cheers,
Klaus

---

Request for Answer Clarification by klausb-ga on 18 Jun 2004 09:02 PDT

Hi again Andyt -- and Cynthias:
I was going to create the query you (Andyt) suggested dynamically from
the input array of userFkId's as suggested by Cynthias. But first I
was going to run it "statically" to see if I get the desired results.
Unfortunately on my real table I get 0 results with the suggested
(static) query.
But even if it worked, if I where to attempt to dynamically generate
the sql string, I would not know how to expand on statements like
these:

AND (T1.userFkId=T2.userFkId-1 OR T2.userFkId=T1.userFkId-1) 
AND T1.colorFkId=T2.colorFkId  

It seems straigh forward enough with only two userFkId inputs, but in
reality I will probably deal with more like 5 or 6. Or, for the
purpose of the code, any number with no specific upper boundry.

Cheers,
Klaus

---

Clarification of Answer by andyt-ga on 18 Jun 2004 14:09 PDT

Klausb-ga,
I wish you had given me a bit of time to respond to your
clarification, but I can understand if you're in a rush.  I work
during the day, so unfortunately did not have time to view your
questions until tonight, which was too late.

Indeed, I had misinterpreted your original question.  When you said
dynamically provided array of userFkId's (1-n; for example 12 and 13),
I assumed you meant that there would always be two numbers and one
number would be one less then the original number.

I would have been happy to modify the query to work with a variable
amount of numbers, but I'm glad you worked it out on your own.

Regards,
Andyt-ga

---

Request for Answer Clarification by klausb-ga on 20 Jun 2004 21:40 PDT

Hey andyt:
I meant no disrespect! I know that you would have come back and solved
the problem completely and entirely satisfactorily. But as you already
assumed (correctly), I was very much under time pressure. And like I
mentioned, in the end you helped in the best way possible: but putting
me on the right track.

Best regards and thanks again.

---

Clarification of Answer by andyt-ga on 23 Jun 2004 11:52 PDT

No disrepect taken! Thanks for using Google Answers.

Andyt-ga

I think main value of the answer was that it got me started to think
on the right tracks (and is therefore arguably the best possible
answer). Technically it was somewhat incomplete as I asked how to do
the whole thing. But, the end result it what counts for me, and I am
happy.

The simple thing is to array your userFkId input, then create a
variable that holds your where clause for all values inputed for
userFkId. Append that where string to your query. (where
userFkId=userFkId(1) OR ...)

Query one table, count the occurrences of the colorId into an array.
Query the results one more time with the color that repeats (or
repeats the most). Use the where clause above in parentheses () AND
colorFkId=mostrepeatedcolor.

Not really efficient, but I'm rather brute force instead of elegant coding.

I am not a Google Answers Researcher.

Re: Comment 18 Jun 2004 09:02 PDT
Don't use two tables. Use one table.
Assuming you have the ability to do it, 

$userfkarray are parts of $userfk variable from wherever you get it 
$whereclause="(";
for ($i=1;$i<=$numberOfuserFkIds;$i++) {
    $whereclause+="userFkId=" $userfkarray($i);
    if ($i<($numberOfuserFkIds) {
        $whereclaus+=" OR ";
    }
}
$whereclause+=")";
$query="SELECT tablePkId userFkId colorFkId FROM TABLE WHERE " $whereclause;
$result=mysql_query( $query ";") or die("Query Problems");
unset($colorarray);
$i=1;
while ($row = mysql_fetch_object($result) {
   $color=$row->colorFkId;
   $colorfound=0;
   for ($j=1;$j<=$i;$j++) {
      if ($colorarray[$j][1]=$color) {
         $colorfound=1;
         $colorarray[$j][2]++;
      }
    }
    if ($colorfound=0) {
        $i++;
        $colorarray[$i][1]=$color;
        $colorarray[$i][2]=1;
     }
}
$andclause="("
for($j=1;$j<=$i;$j+=) {
   if ($colorarray[$j][2]>1) {
       $andclause+="colorFkId=" $colorarray[$j][1] " AND ";
   }
}
$andlcause+="1=1)";

$query+=$andclause;
$result=mysql_query( $query ";") or die("Query Problems");

I'm by no means an expert. I almost definitely have errors,
misspellings, etc. Your results may vary.
I think the concept flows, though.

like I missed the ; after $andclause="("

I'm not paying attention. 
$query="SELECT tablePkId userFkId colorFkId FROM TABLE WHERE "
$whereclause; should be
$query="SELECT tablePkId, userFkId, colorFkId FROM TABLE WHERE " $whereclause;

$andclause="(" should be
$andclause=" AND (";

$andclause+="colorFkId=" $colorarray[$j][1] " AND "; should be
$andclause+="colorFkId=" $colorarray[$j][1] " OR ";

$andlcause+="1=1)"; should be
$andlcause+="1=0)";

(the result should be find any repeated colors.)

$andlcause+="1=1)"; should be
$andclause+="1=0)";

I'm sorry for the repeated errors. I'm fighting a nasty flu/cold thing
and well, it's not important to you because I'm sure you just wanted
it right the first time.

Whoohooo! Thanks to both of you! And hopefully you feel better soon cynthias! 
I used a combination of all of the various answers and came up with
something that works. I am not sure if this is elegant or not, but I
really don't care too much about that right now. Give me a few minutes
to write it up and I will post the whole thing
-K

Let me try again. I had so many errors, and probably have more, but I
think I fixed most of them...

I'm assuming you have some control over the format and syntax of the
userFkIds that will be used, and that you either collect them as an
array or split on commas. I also assume that you know how many
userFkIds are being chosen.

$whereclause="(";
for ($i=1;$i<=$numberOfuserFkIds;$i++) {
    $whereclause+="userFkId=" $userfkarray[$i];
    if ($i<$numberOfuserFkIds) {
        $whereclaus+=" OR ";
    }
}
$whereclause+=")";
$query="SELECT tablePkId, userFkId, colorFkId FROM TABLE WHERE " $whereclause;
$result=mysql_query( $query ";") or die("Query Problems");
unset($colorarray);
$i=0;
while ($row = mysql_fetch_object($result)) {
   $color=$row->colorFkId;
   $colorfound=0;
   for ($j=1;$j<=$i;$j++) {
      if ($colorarray[$j][1]==$color) {
         $colorfound=1;
         $colorarray[$j][2]++;
      }
    }
    if ($colorfound==0) {
        $i++;
        $colorarray[$i][1]=$color;
        $colorarray[$i][2]=1;
     }
}
$andclause=" AND ("
for($j=1;$j<=$i;$j+=) {
   if ($colorarray[$j][2]>1) {
       $andclause+="colorFkId=" $colorarray[$j][1] " OR ";
   }
}
$andclause+="1=0)";

$query+=$andclause;
$result=mysql_query( $query ";") or die("Query Problems");

PS: It's Crythias and I'm male :).
Thanks for the wellwishes.

So here is what I ended up with:

First I wrote a function that takes loops through a MySql record set
and returns a (unique) array.
I called the function "rs2array" and it takes three variables: 
1. the sql query, 
2. the field to put into the array, 
3. whether or not to make use unique_array()

Here is the code that I used then: 
# create an array with all the colorFkId's in the table
$aryCID = rs2array("SELECT colorFkId FROM deploys", 0, 1);
$sWHERE 	= "";
$sAND		= "";
foreach($aryUsers as $key => $val) {
	$thisArray = rs2array("SELECT colorFkId FROM myTable WHERE userFkId =
'$val'", 0, true);
	# use the PHP array_intersect() funtion to filter by only the intersecting values
	$aryCID = array_intersect($aryCID, $thisArray);
	# create the WHERE clause
	$sWHERE .= "userFkId = '$val' OR ";
}
# trim the WHERE clause
$sWHERE = trim($sWHERE, " OR ");
# make sure only unique colorFkId's are in the array
$aryCID = array_unique($aryCID);
foreach($aryCID as $key => $val) {
	# create the AND clause
	$sAND .= "colorFkId = '$val' OR ";
}
# trim the AND clause
$sAND = trim($sAND, " OR ");
# finally get my desired results
$sql = "SELECT * FROM myTable WHERE ($sWHERE) AND ($sAND)";

And Bob's You Uncle as they say. 
Feel free to comment on how this could be "prettier", but I am happy
it works now -- very happy :)

-K

That's prety great. Now I feel stupid about the +=. I've programmed a
lot but PHP only about a few months. However, concepts are concepts
and I like your method.

I am not a Google Answers Researcher, so I can't get paid. I'm glad I
helped, if any.

Hey crythias: 
you did help a lot! And maybe I can help you one day in return.
And, oh yea, err... sorry about that name thing :)

-K