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!

    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);
      }
    
    
    

      Thanks for the reply, I will try this tonight.

      Thats makes sense to box it inside a function.

      Thanks again

        Write a Reply...