User Driven Query from MySQL SELECT List

[jonnyboy]

Can anyone help please?

I am trying to create a drop down select list from a MySQL table. I know how to create a "selectDistinct" to generate the list, but now I want to create a select list based on certain criteria. Should I be able to do this within a selectDistinct or should I do it in a different way.

Any help greatly appreciated!

Jonathan

[planetsim]

Umm you havent been answered becuase we have no idea what the code looks like to decide whether or not to use what you think you need to use.

[jonnyboy]

Sorry, should have been more specific!

This is some example code for one of my selectDistinct queries. Can I edit this to be involve greater criteria from the database or should I create a slightly diferent select query to popultate my select list?

---

 <form action="<?php echo $scriptName;?>" method="GET">
   <?

function selectDistinct ($connection,
$tableName,
$columnName,
$pulldownName,
$additionalOption,
$defaultValue)
{
$defaultWithinResultSet = FALSE;

 // Query to find distinct values of $columnName
 // in $tableName
 $distinctQuery = "SELECT DISTINCT $columnName
                   FROM $tableName ORDER by $columnName";

 // Run the distinctQuery on the databaseName
 if (!($resultId = @ mysql_query ($distinctQuery, 
                                  $connection)))
    showerror();

 // Retrieve all distinct values
 $i = 0;
 while ($row = @ mysql_fetch_array($resultId))
    $resultBuffer[$i++] = $row[$columnName];

 // Start the select widget
 echo "\n<select name=\"$pulldownName\">";       

 // Is there an additional option?
 if (isset($additionalOption))
    // Yes, but is it the default option?
    if ($defaultValue == $additionalOption)
       // Show the additional option as selected
       echo "\n\t<option selected>$additionalOption";
    else
       // Just show the additional option
       echo "\n\t<option>$additionalOption";

 // check for a default value
 if (isset($defaultValue))
 {
    // Yes, there's a default value specified

    // Check if the defaultValue is in the 
    // database values
    foreach ($resultBuffer as $result)
       if ($result == $defaultValue)
          // Yes, show as selected
          echo "\n\t<option selected>$result";
       else
          // No, just show as an option
          echo "\n\t<option>$result";
 }  // end if defaultValue
 else 
 { 
    // No defaultValue

    // Show database values as options
    foreach ($resultBuffer as $result)
       echo "\n\t<option>$result";
 }
 echo "\n</select>";

} // end of function

// Connect to the DBMS
if (!($connection = @ mysql_connect($hostname,
$username,
$password)))
die("Could not connect to database");

if (!mysql_select_db("intranet"))
showerror();

echo "\nCompany:";

// Produce the select list
// Parameters:
// 1: Database connection
// 2. Table that contains values
// 3. Attribute that contains values
// 4. <SELECT> element name
// 5. An additional non-database value
// 6. Optional <OPTION SELECTED>
selectDistinct($connection,
"external_people",
"company",
"companyName",
"All",
"All");

?>

<input type="submit" value="Go">

</form>

<?php

} // end of if empty($GET["companyName"]) body
else
{
// Secure the user parameter $companyName
$companyName = clean($GET["companyName"], 60);

 // Connect to the MySQL DBMS
 if (!($connection = @ mysql_connect($hostName, 
                                     $username,
                                     $password)))
    die("Could not connect");

 if (!mysql_select_db("intranet", $connection))
    showerror();

 // Start a query ...
 $query = "SELECT   *                       
           FROM     external_people
           WHERE    1";

  // ... then, if the user has specified a company,
  // add the companyName as an AND clause ...
  if ($companyName != "All")
    $query .= " AND company = \"$companyName\"";

  // ... and then complete the query.
  $query .= " ORDER BY name";

  // run the query and show the results
  displayContactsList($connection, $query, $companyName);

  // Close the DBMS connection
  mysql_close($connection);

} // end of else if empty($companyName) body
?>

---

Thanks

Jonathan