Friday, 16 November 2007

Populate a select form with a SQL query using PHP

This is another frequent asked question which I receive frequently. The solution is very simple and is illustrated step-by-step in the code below

Solution using PHP
Image a simple query SQL which get all users' names on a generic database's table, in this case USER, which have two attributes: ID_USER_PK (primary key) and NAME (the user name):

<?php
$getUser_sql = 'SELECT * FROM USER';
$getUser = mysql_query($getUser_sql);
?>


If you want to display the query's results into a SELECT form you can use this simple code:

<select name="select" name="userName">
<?php while ($row = mysql_fetch_array($getUser)) {?>
<option value="<?php echo $row['id_user_pk']; ?>">
<?php echo $row['name']; ?></option>
<?php } ?>


Wile statement creates a number of option elements equal to number of total records contained into the table and assigns them a value equal to primary key (in this simple case id_user_pk.


If you want select an option equal to a parameter in input
Now, image if you have a parameter in input, for example a URL variable idUser:

&_GET['idUser']


...passed to the page in this way:

http://localhost/index.php?idUser=1


If you want your SELECT form select the option equal to the value of parameter in input, you have only add a line of code (in bold):

<select name="select" name="userName">
<?php while ($row = mysql_fetch_array($getUser)) {?>
<option <?php if($row['id_user_pk'] == $_GET['idUser']) { echo 'selected="selected"';} ?> value="<?php echo $row['id_user_pk']; ?>">
<?php echo $row['name']; ?></option>
<?php } ?>
</select>

In this way, you force to set the "selected" attribute, in your SELECT form, equal to "selected" if the current record has an id_user_pk equal to the parameter in input.

No comments:

Post a Comment