php variable for mysel field select AND comparing two table resutls

[rohair]

I am trying to create a checklist of bird species observed. I have a table of observations (records) from multiple users, and a table of species (birds). I am having two problems.

 

Problem 1:

the (birds) table includes separate columns for each state using 2-letter postal codes (AZ, CA, MN, etc.). These columns have presence/absense values (0,1,2,etc.) I want the user to be able to use a php form to select any state ($statecolumn) for the checklist. I then want to use the $statecolumn variable from the php form in the MYSQL Query. Such as:

 

SELECT species, $statecolumn FROM birds WHERE $statecolumn > 0

 

What is the proper syntax to do this?

 

 

Problem 2:

 

I want the checklist to indicate if the observer has seen that species in that state. I cannot seem to construct the proper loop to do this the right way.

 

<?php

// THIS IS THE QUERY FOR THE BASE LIST OF SPECIES FOR THAT STATE
$list=mysql_query("Select DISTINCT orderid, species 
    FROM occur
    WHERE state = 'AZ'
    ORDER BY orderid");


// THIS IS THE QUERY FOR THE USER OBSERVATIONS
//$obsv=mysql_query("Select DISTINCT species As Species 
//    FROM records
//    WHERE state = 'AZ'
//    AND user_short = 'JRM'");

$row = mysql_fetch_array( $list );

               while ($row=mysql_fetch_array($list)){
                     echo "<table><tr>";
      
      
     if ($row[1] == "American Goldfinch")
     {
     echo "<tr><TD bgcolor=#FFFFFF><B>_X_</b></TD>";
     }
     else
                    echo "<tr><TD bgcolor=#FFFFFF><B>___</b></TD>";
     
      
                     for ($i=0; $i< mysql_num_fields($list); $i++){
                         echo "<td>".$row[mysql_field_name($list,$i)]."</td>";
                         }
                     echo "</tr></table>";
                     }
?>

Where (and how) do I compre the results from $list and $obsv?

 

Thanks for any guidance.

rohair

[fenway]

Sounds like you need a jon.