Google Answers Logo
View Question
 
Q: PHP / MySql query and/or script ( Answered 3 out of 5 stars,   11 Comments )
Question  
Subject: PHP / MySql query and/or script
Category: Computers > Programming
Asked by: klausb-ga
List Price: $30.00
Posted: 17 Jun 2004 16:59 PDT
Expires: 17 Jul 2004 16:59 PDT
Question ID: 362714
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.
Answer  
Subject: Re: PHP / MySql query and/or script
Answered By: andyt-ga on 17 Jun 2004 22:07 PDT
Rated:3 out of 5 stars
 
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
klausb-ga rated this answer:3 out of 5 stars
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.

Comments  
Subject: Re: PHP / MySql query and/or script
From: crythias-ga on 18 Jun 2004 07:17 PDT
 
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.
Subject: Re: PHP / MySql query and/or script
From: crythias-ga on 18 Jun 2004 10:18 PDT
 
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.
Subject: Re: PHP / MySql query and/or script
From: crythias-ga on 18 Jun 2004 10:19 PDT
 
like I missed the ; after $andclause="("
Subject: Re: PHP / MySql query and/or script
From: crythias-ga on 18 Jun 2004 10:30 PDT
 
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.)
Subject: Re: PHP / MySql query and/or script
From: crythias-ga on 18 Jun 2004 10:40 PDT
 
$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.
Subject: Re: PHP / MySql query and/or script
From: klausb-ga on 18 Jun 2004 11:03 PDT
 
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
Subject: Re: PHP / MySql query and/or script
From: crythias-ga on 18 Jun 2004 11:12 PDT
 
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");
Subject: Re: PHP / MySql query and/or script
From: crythias-ga on 18 Jun 2004 11:14 PDT
 
PS: It's Crythias and I'm male :).
Thanks for the wellwishes.
Subject: Re: PHP / MySql query and/or script
From: klausb-ga on 18 Jun 2004 11:17 PDT
 
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
Subject: Re: PHP / MySql query and/or script
From: crythias-ga on 18 Jun 2004 11:26 PDT
 
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.
Subject: Re: PHP / MySql query and/or script
From: klausb-ga on 18 Jun 2004 12:31 PDT
 
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

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy