mysql query result into an array, used as criteria in next query 1

[ulteriormotif]

I have a series of queries that, according to a number of conditions submitted on a form, only ONE of the queries will execute. That's working fine.

I then put the result of that query into an array. It's only one field that I want, but there will be a variable number of records returned. I THINK that's working, but the output isn't as clean as I expected it to be.

That array I then need to use as criteria in the next query. THAT I am having trouble with.

Can you help please? Firstly, am I putting the data into the array correctly? Secondly, how do I then refer to that array in the following query.

Example follows:

<?
$gotblkyrs=array();
//INITIAL QUERY
$getblkyrsquery="SELECT bid AS blkyr FROM blockyear WHERE  (various criteria here)";
    $getblkyrsquery = mysql_query($getblkyrsquery) or die("Error: " . mysql_error());  
    if(mysql_num_rows($getblkyrsquery) == 0){ 
        echo("can't identify any appropriate blocks"); 
    } ELSE {
while ($row = mysql_fetch_array($getblkyrsquery)) {
//LOOP THROUGH RESULTS AND PUT INTO ARRAY - HAVE I GOT THIS RIGHT?
    array_push($gotblkyrs, $row);
echo $row['blkyr']."<br>";
}
}
?>

The blkyrs echo out in this case as:
1293
1325
1705
3812
3824
4649
4655
... which is exactly the data I expect to see.

When I check out the array with print_r($gotblkyrs);
I get:

Array ( [0] => Array ( [0] => 1293 [blkyr] => 1293 ) [1] => Array ( [0] => 1325 [blkyr] => 1325 ) [2] => Array ( [0] => 1705 [blkyr] => 1705 ) [3] => Array ( [0] => 3812 [blkyr] => 3812 ) [4] => Array ( [0] => 3824 [blkyr] => 3824 ) [5] => Array ( [0] => 4649 [blkyr] => 4649 ) [6] => Array ( [0] => 4655 [blkyr] => 4655 ) )

I don't know - is that how it should look? Why the duplication of the blkyr values?

The array then needs to be used as criteria in the next query - I need to find the records that match any of the blkyrs in the array:

<?
$getblkyrsquery2="SELECT blockyearid AS blkyr FROM blockyear WHERE blockyear.blockyearid '" . $gotblkyrs[blkyr] . "'";
    $getblkyrsquery2 = mysql_query($getblkyrsquery2) or die("Error: " . mysql_error());  
    if(mysql_num_rows($getblkyrsquery2) == 0){ 
        echo("IMPLODE TEST FAILED"); 
    } ELSE {
while ($row = mysql_fetch_array($getblkyrsquery2)) {
echo $row['blkyr']." - <br>";
}
}
?>

Which produces the following error:
Error: 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 '''' at line 1


I hope I've explained it clearly enough. Would really appreciate any help.

 

[jpadie]

use mysql_fetch_assoc, not array

<?
$gotblkyrs=array();
//INITIAL QUERY
$getblkyrsquery="SELECT bid AS blkyr FROM blockyear WHERE  (various criteria here)";
    $getblkyrsquery = mysql_query($getblkyrsquery) or die("Error: " . mysql_error());  
    if(mysql_num_rows($getblkyrsquery) == 0){
        echo("can't identify any appropriate blocks");
    } ELSE {
while ($row = mysql_fetch_assoc($getblkyrsquery)) {
//LOOP THROUGH RESULTS AND PUT INTO ARRAY - HAVE I GOT THIS RIGHT?
    array_push($gotblkyrs, $row);
echo $row['blkyr']."<br>";
}
}
?>

<?
$getblkyrsquery2="SELECT blockyearid AS blkyr FROM blockyear WHERE blockyear.blockyearid IN ('" . implode("','", $gotblkyrs). "')";
    $getblkyrsquery2 = mysql_query($getblkyrsquery2) or die("Error: " . mysql_error());  
    if(mysql_num_rows($getblkyrsquery2) == 0){
        echo("IMPLODE TEST FAILED");
    } ELSE {
while ($row = mysql_fetch_assoc($getblkyrsquery2)) {
echo $row['blkyr']." - <br>";
}
}
?>

 

[ulteriormotif]

Thanks pjadie!

That improved the look of the array and got rid of the duplicate values. I guess I was effectively pulling an array into an array? Array result using mysql_fetch_assoc is now:
Array ( [0] => Array ( [blkyr] => 1264 ) [1] => Array ( [blkyr] => 1293 ) [2] => Array ( [blkyr] => 1325 ) [3] => Array ( [blkyr] => 1341 ) [4] => Array ( [blkyr] => 1705 ) [5] => Array ( [blkyr] => 3812 ) [6] => Array ( [blkyr] => 3824 ) [7] => Array ( [blkyr] => 4649 ) [8] => Array ( [blkyr] => 4655 ) )

Much tidier.

I still couldn't get the implode to work as I had it, but I found a small function for imploding a single column out of a multidimensional array, and that's working:

    function get_name($array) { return $array['blkyr']; }
    $blkyrslist = implode(', ', array_map('get_name', $gotblkyrs));

//TEST IMPLODE LIST

echo "<P>TEST IMPLODE LIST<p>";
$getblkyrsquery2="SELECT blockyearid AS blkyr, year FROM blockyear WHERE blockyear.blockyearid IN ($blkyrslist)";
    $getblkyrsquery2 = mysql_query($getblkyrsquery2) or die("Error: " . mysql_error());  
    if(mysql_num_rows($getblkyrsquery2) == 0){ 
        echo("IMPLODE TEST FAILED"); 
    } ELSE {
while ($row = mysql_fetch_assoc($getblkyrsquery2)) {
echo $row['blkyr']." - ".$row['year']."<br>";
}
}

Is it correct though? Efficient? This is going to be a hua of a big report once done, so I need to keep it as lean as I can (easier said than done when you're learning on the fly).

Any comments appreciated

 

[jpadie]

change this line as shown

array_push($gotblkyrs, $row[red]['blkyr'][/red]);