[RESOLVED] Smartest way to create a number of drop downs from various mysql tables

[cainy1982]

Im looking to create a form that contains a few htnl select boxes using data from a table in MYSQL.

So far I have managed to create a single drop down box and could set up another two but I think im making the process hard work.

So please feel free to show me the smart way (and take the piss out of my long winded messy code - im still learning but take it all on the chin!) to do this, rather than creating 3 seperate queries and 3 sep arrays!

In the code below I have queried the ward and party table but only created th select for the ward as I was getting annoyed at how long this seemed to take!

<?php // This is the hone page for the site.

// set the title for the html header
$page_title = 'Header';

// require the config file
require_once ('../includes/config.inc.php');

// include the header filde
// add header file once created


// require the database settings file using information from config.inc.php
require_once (MYSQL);

// Select the values for the ward drop down menu
 $q="SELECT ward, ward_id FROM ward";
// OREDER BY id DESC is order result by descending - look at ordering by abc
// For debugging return any errors or run the query 
 $r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

// check the query returnbed any results of not we need to tell the user so they understand why the page is blank
if (mysqli_affected_rows($dbc) == 0) {

	// if no new topics have been created tell the user
	echo '<p><h1>No wards available on the system</h1></p>';

		}// close the statement

// Select the values for the political party drop down menu
 $q2="SELECT party.party, party.party  FROM party";
// OREDER BY id DESC is order result by descending - lok at ordering by abc
// For debugging return any errors or run the query 
 $r2 = mysqli_query ($dbc, $q2) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

// check the query returnbed any results of not we need to tell the user so they understand why the page is blank
if (mysqli_affected_rows($dbc) == 0) {

	// if no new topics have been created tell the user
	echo '<p><h1>No political parties available on the system</h1></p>';

		} // close the statement

			// close php and open HTML select tag
			?>
<!-- Start HTML select tag for drop down-->
 <select>

<?php // open php to create the array

	// Start looping data ready for drop downs 
	while ($rows=mysqli_fetch_array($r, MYSQLI_ASSOC)){

	// close php and open HTML loop the array in the open select tag
	?>

<!-- Start HTML select tag and echo array into select tag-->
<option value="<?php echo $rows['ward_id']; ?>"><?php echo $rows['ward']; ?></option>


<?php // open php to close looping and database connection

// exit looping
}
	// close php tag and open HTML to close select tag
	?>

<!-- Close HTML select tag-->
</select>

<?php // Standard end of scripts include footr and close database connection for security

// close connection
mysqli_close($dbc);

// include the footer
// add footer file once created
// close the PHP
?>

Thanks in advance!

[leatherback]

Hi,

What you have done there looks good, and I do not really see a lot of wiggle room for improvement. However, as you have multiple dropdowns, I would put this all in a function, which you can then use to create the dropdown. I have reworked your code a little, and made a function. No server here, so I cannot test it. Also, I am not sure whether the MySQLi calls work the same as the mysql_query functions. But I am sure someone can patch that up where needed. It should give you a starting point in any case. Naturally, you may want to clean it up a little and add some error trapping to it. But this would be the basis of a function I would use:

// Use like:
$query = "SELECT ward, ward_id FROM ward";
$KeyColum = "ward_id";
$LabelColum = "ward";
$sel = 1;

$warddropdown = createDropDown($query, $KeyColum, $LabelColum, $sel);
if($warddropdown <> -1)
  {
  echo $warddropdown;
  }
else
  {
  echo "Sorry, no records found";
  }

function createDropDown($sql,$idcol, $labelcol, $selected)

   $r = mysqli_query($sql) or trigger_error("Query: $sql\n<br />MySQL Error: " . mysqli_error());

   // If no records found, return -1 as an indicator of no records. In the main script handle output to user

   if(mysqli_affected_rows($dbc) == 0) 
     {
     return -1;
     }

   $drop = "<select>"

   // loop through data and fill drop downs

   while($rows=mysqli_fetch_array($r))
     {
     $sel = "";
     if($rows[$idlcol] == $selected)  // Set $sel to 'selected' if ID matches selected ID
       {
       $sel = " selected ";
       }

 $drop .= "<option value=\"".$rows[$idlcol]."\" ".$sel.">\"".$rows[$labelcol]."\"</option>
          ";
}
  $drop .= "</select>";

  return($drop);
  }

[cainy1982]

Thanks for the reply, I will try this tonight.

Thats makes sense to box it inside a function.

Thanks again