Query help

claire

can someone help me get the MySQL query correct. I'm having trouble with the WHERE statement in my second query.


//performing search 1, where I gather data in an array
while(.....)
$andel[]=$row['Andel'];

}

//making sure all duplicates are taken out of the array

$andel = array_unique($andel);

//search db2 for 'portkod' using $andel array from previous search.

initdb($db2);

$result = mysql_query("SELECT Portkod FROM Uppgifter WHERE  Andel='$andel[$i]'");

Sxooter

Originally posted by claire

SNIP

$result = mysql_query("SELECT Portkod FROM Uppgifter WHERE  Andel='$andel[$i]'");

[/B]

the problem is that PHP can't be sure just how to interpret the $andel[$i] part of your query. Just put it outside the quotes so it isn't ambiguous like so:

$result = mysql_query("SELECT Portkod FROM Uppgifter WHERE  Andel='".$andel[$i]."'");

jeremuck

what does $i equal?

tomhath

How about using andel as the key and "join"ing the keys of the array and using IN?

//performing search 1, where I gather data in an array
while(.....)
$andel[$row['Andel']]=1;
}

initdb($db2);

$result = mysql_query("SELECT Portkod FROM Uppgifter WHERE  Andel IN ('" .  join ("','", array_keys($andel) ) . "')" );

claire

Originally posted by Sxooter

the problem is that PHP can't be sure just how to interpret the $andel[$i] part of your query. Just put it outside the quotes so it isn't ambiguous like so:
$result = mysql_query("SELECT Portkod FROM Uppgifter WHERE  Andel='".$andel[$i]."'");
[/B]

I tried this notation but the query still doesn't work.
I want to query the second db on the same 'Andel' numbers that are found in the first search AND in the same order that are given by this search.
First query statement:

$svar = mysql_query("SELECT Andel,  substring_index(Namn,' ',1) AS surname FROM Uppgifter WHERE Andel>=24 AND Andel <=48 GROUP BY Andel, surname ORDER BY surname ");

In the first search I'm getting duplicates of 'Andel' and it is important that I get rid of those before I do the second search. That's why I thought making it an array and using array_unique().

I also tried using SELECT DISTINCT Andel, surname... and simply do the second query within the while loop of the first query. But i'm still getting duplicates of $andel totally messing up the 'Portkod' query.

So I'm back at trying to do a query using an array in the WHERE statement

tomhath

What I posted will do what you want (using $andel as the array Key instead of the Value). If the first query returns two rows with the same value for $andel it will just set the same array entry to 1 again, not create a duplicate.

claire

tomhath, you're right! I do get unique values, no duplicates. But another interesting (!) thing happened. Somehow I lost the sorting connection between the two columns. See

http://www.brfskalen11.org/claire/testlista2.php

The names are sorted correctly but not reflecting the correct code after the name. The code (portkod) is simply displayed numerically.

Then take a look at

http://www.brfskalen11.org/claire/testlista.php

where the connection is kept inbetween the columns. The problem here is the duplicated rows. The script used here is as follows.
Maybe it is simple getting the duplicates out, using this script?

initdb($medlemmardb);
$svar = mysql_query("SELECT DISTINCT Andel,  substring_index(Namn,' ',1) AS surname FROM Uppgifter WHERE Andel>=24 AND Andel <=48 GROUP BY Andel, surname ORDER BY surname ");
	while ($row = mysql_fetch_assoc ($svar)) {
		$people[$row['Andel']][] = $row['surname'];
		$andel=$row['Andel'];

	initdb($andelardb);
	$resultat = mysql_query("SELECT  Portkod FROM Uppgifter WHERE  Andel=$andel ");
	while ($rowc = mysql_fetch_assoc($resultat)) {
		$portkod[] = $rowc['Portkod'];
	}
}

tomhath

How about doing it this way:

        while ($rowc = mysql_fetch_assoc($resultat)) {
            $portkod[$andel] = $rowc['Portkod'];
        }

Then you loop through the keys from $people (which are andel values). I though that's where you were a couple of days ago?

claire

Originally posted by tomhath

I though that's where you were a couple of days ago?

Yeah, I've probably wandered about in a big wide circle!!! Most of the time feeling completely lost!

But - I'm not back where I started, I'm back with a working solution! Believe me, I was about to give up, but all knowledgeable people out there in the big wide community saved me!

I've recieved several different suggestions over the past few days. Now I'll go back to my cave and study them. Hopefully I'll come out a bit wiser and more comPHPetent!

Once again THANKS!