[SOLVED] IN($array[x]) problem

[simon551]

Hi, I'm trying to use an array in a criteria but not having any success.

 

<?php

$query = "SELECT contracts.contractID as x FROM contracts";
$rsContracts = mysql_query($query, $conn_org) or die(mysql_error());

do {
array_push ($contractArray, $row_rsContracts['x']);
   }
while 
($row_rsContracts = mysql_fetch_assoc($rsContracts));

//check the array
foreach ($contractArray as  $check) { echo $check.'<br />';}
//all good (10 results)

//now use the array to query the same table just b/c we already know there should be something

$query= ("SELECT * FROM contracts WHERE contracts.contractId IN($contractArray[x])");

echo $query.'<br />';
//query looks like this: SELECT * FROM contracts WHERE contracts.contractId IN()

//execute query
$contracts=mysql_query($query, $conn_org)or die(mysql_error());
//error: You have an error in your SQL syntax; check the manual that corresponds to your 
//error: MySQL server version for the right syntax to use near ')' at line 1

//the rest of this is obviously not working, but this is how I'd check it.
do 

{echo 'q:'.$row['contractID'].'<br />';}

while ($row=mysql_fetch_assoc($contracts));

?>

[GingerRobot]

I think what you were probabably need is:

 

$query= ("SELECT * FROM contracts WHERE contracts.contractId IN(".implode(',',$contractArray).")");

 

The in clause of the mysql statement expects a comma delimited list of possible values, not an array. So you use the implode() function to create that list.

 

Might not be what you were after however, you weren't overly specific about your lack of success.

[simon551]

the IN part of the query was the problem. thanks. I'm trying that...

 

Now it's giving me a new error. I think there may be a problem with my array function.

 

the array has a blank space at the beginning so my new query looks like this:

 

SELECT * FROM contracts WHERE contracts.contractId IN(,37,38,35,11,16,21,49,29,17,22)

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '37,38,35,11,16,21,49,29,17,22

[GingerRobot]

I think thats because you used a do...while loop. The first time that runs, you've not used the mysql_fetch_assoc function. Therefore, $row_rsContracts['x'] wont exist the first time, hence the blank in your array.

 

Try an ordinary while loop:

while ($row_rsContracts = mysql_fetch_assoc($rsContracts))
{
array_push ($contractArray, $row_rsContracts['x']);
}

 

[simon551]

did the trick! thank you very much Mr. GingerRobot! :-]

[darkfreaks]

please mark as solved