[SOLVED] More array madness

[CincoPistolero]

I am trying to do comparisons for checkboxes from two arrays. I have a master table($row2) that spits out a bunch of checkboxes and their names, say(1,2,3,4,5,6,7,8,9,10) as IDs. I have another table($row3) that has only some of the matching IDS, say (1,4,9). Now I want to display all the the checkboxes(1-10) but only check boxes(1,4,9). Below is what I've tried, It only checks the first result in $row3(1) and not (4,9).

 

thanks for any help

$query3="SELECT optPricesID FROM woUpg WHERE awoID='$awoID'";
$result3=mysql_query($query3) or die ("Error in query: $query3. " . mysql_error());
$row3= mysql_fetch_array($result3);
extract($row3);

$max2=2; 
                $query2="SELECT op.optPricesID, o.name, op.basePrice FROM optPrices op, options o WHERE op.optionID = o.optionID AND op.drumTypeID=1"; 
                $result2=@mysql_query($query2); 
                $num2=mysql_num_rows($result2); 

                 
                if(empty($num2)){ 
                    $final2="\t<tr><td><center><span class='optTitles'>Nothing to Display</span></center></td></tr>\n"; 
                }else{ 
                    while($row2=mysql_fetch_array($result2,MYSQL_NUM)){ 
                        
					if($row2[0]==$row3[0]) {
					 $array2[]="<input type=checkbox name='snareOpt[]' checked value=".$row2[2]."\">".$row2[1];
					}else {
					$array2[]="<input type=checkbox name='snareOpt[]' value=".$row2[2]."\">".$row2[1]; 
                        }
				} 
                     
                mysql_free_result($result2); 
                 
                    $final2="<tr>\n"; 
                     
                    foreach($array2 as $thumbnail2){ 
                    if($counter2==$max2){ 
                        $counter2=1; 
                        $final2.="\n<tr>\n"; 
                    }else $counter2++; 
                        $final2.="\t<td align=\"left\" width=\"270\" height=\"20\"><span class='optTitles'>".$thumbnail2."</span></td>\n"; 
                    }                      
                        if($counter2){ 
                            if($max2-$counter2){ 
                                $final2 .= "\t<td width=\"270\" height=\"20\"> </td>\n";  
                            } 
                            $final2.="</tr>"; 
                            } 
                        } 
					echo " $final2";

[Psycho]

You can accomplish this MUCH easier with just one query, but I'm having a hard time understanding your code. It really helps others (and yourself in the long run) to use descriptive names for variables/tables/fields and to include appropriate comments in your code. Let me take some time to see if I can unravel this.

[CincoPistolero]

How, one query statement?

 

I need the full list of optPricesID(about a dozen) from one table and the other table could have (0-12) optPricesID. I compare those in the WHERE and I'll only get what is in the second table.

 

I'm willing to learn the easy way, if that is easier.

[Psycho]

Well, it is not necessarily "simpler", but it is the most efficient and, in my opinion, the right way. Having a relational database gives you a great deal of power and it will take time and effort to learn to utilize that power.

 

I did my best to rewrite the code based upon a single query which included a lot of modifications. I also took the liberty to change some variable names to be more understandable (at least to me). Due to the amount of changes needed, I'm sure there are some typos. But, I can't test because I don't have your database.

 

//Get complete list of options with "checked" identifiers
$query = "SELECT op.optPricesID, o.name, op.basePrice,
                 IF(op.optPricesID IN (SELECT optPricesID FROM woUpg WHERE awoID='$awoID'),1,0) as checked
          FROM optPrices op, options o
          WHERE op.optionID = o.optionID AND op.drumTypeID=1";

$result = @mysql_query($query);


if(mysql_num_rows($result)==0)
{
    $finalHTML = "\t<tr><td><center><span class='optTitles'>Nothing to Display</span></center></td></tr>\n"; 
}
else
{
    $max_columns = 2;
    $current_column = 1;

    while($row = mysql_fetch_assoc($result, MYSQL_NUM))
    {
        //Open new row if first column
        if($current_column == 1)
        {
            $finalHTML .= "\t<tr>\n";
        }

        $checked = ($row['basePrice']==1) ? ' checked="checked"' : '';
        $finalHTML .= "\t\t<td align=\"left\" width=\"270\" height=\"20\"><span class=\"optTitles\">";
        $finalHTML .= "<input type=\"checkbox\" name=\"snareOpt[]\"{$checked} value=\"{$row['basePrice']}\">{$row['name']}";
        $finalHTML .= "</span></td>\n";

        //Close preceeding row if max columns reached
        if ($current_column >= $max_columns)
        {
            $finalHTML = "\t</tr>\n";
            $current_column = 1;
        }
        else
        {
            $current_column++;
        }
    }

    mysql_free_result($result); 

    //Add additional cells and close last row if needed
    if ($current_column != 1)
    {
        for ($current_column; $current_column<$max_columns; $current_column++)
        {
            $finalHTML = "\t\t<td></td>\n";
        }
        $finalHTML = "\t</tr>\n";
    }

} 

echo " $finalHTML";
?>

[CincoPistolero]

now I'm getting this error

 

mysql_num_rows(): supplied argument is not a valid MySQL result resource

[CincoPistolero]

So I changed my SQL statement to the below and I get the error in my post above.

 

$query2="SELECT op.optPricesID, o.name, op.basePrice 
							IF(op.optPricesID IN (SELECT optPricesID FROM woUpg WHERE awoID='$awoID'),1,0) as checked
							FROM optPrices op, options o 
							WHERE op.optionID = o.optionID AND op.drumTypeID=1";

 

when I switch back to my original query below, it displays list of check boxes. How can I get the select statement listed first above to return a number of rows?

 

$query2="SELECT op.optPricesID, o.name, op.basePrice FROM optPrices op, options o WHERE op.optionID = o.optionID AND op.drumTypeID=1"; 
                

[CincoPistolero]

it appears as if the select statement is invalid. can anyone see a programmatic issue with my select statement?

 

SELECT op.optPricesID, o.name, op.basePrice IF(op.optPricesID IN (SELECT optPricesID FROM woUpg WHERE awoID='$13'),1,0) as checked 
FROM optPrices op, options o 
WHERE op.optionID = o.optionID AND op.drumTypeID=1

[Psycho]

Well, I DID test out the logic of the query using a couple tables I have. If you use the query I provided, try echoing the query to the page along with the mysql error. That will help to debug that problem.

 

EDIT: I've done more tests using a query with the same type of logic and it still works. I've also reviewed the query I posted several times and don't see anything obviously wrong with it. Definitely need to echo the query out and see the mysql error message

[CincoPistolero]

I put the query in mysql. It returned the main body of data, I only get zeroes in the checked column. My problem originally with your select statement is I forgot a comma.  Now I just need to work on getting it to display to the page as it is returning nothing at the moment, but my error message is now gone.

 

thank you for your help so far. I will bang on it again tonight

 

[CincoPistolero]

With some slight tweaking of my original script and the one provided by MJDAMATO. I've gotten it to work. Big props to mjdamato. Thank you