Dynamic popups with PHP

software development

#1

I’ve done some forms with Perl that have pulldowns that are dynamically filled with choices from a query of a MySql table like

$year = WebDB::get_lookup_values (
$dbh,
“SELECT DISTINCT Year FROM tmpImportWebSpecs ORDER BY Year”);
unshift (@{$year}, “Any”);
.
.
.
table ({-border => 1},
Tr (
td (“Year published:”),
td (popup_menu (-name => “year”,
-values => $year))

But now I’m trying to do the same thing in PHP. I couldn’t find anything in my PHP references that showed me how to do this, so I tried to set it up in Dreamweaver 2004 and then seeing what the code looked like. I know, people have told me before that using Dreamweaver to generate dynamic content is not good. I got this incredibly complicated procedure from DW. First I had to define a record set, which is DW’s way of setting up a MySQL connection, a query, and filling arrays with the query results with mysql_fetch_assoc. Then I had to Insert>Form>List/Menu, use the Property inspector on the new List/Menu, and open the Dynamic List/Menu dialog box where I could stick in the name of the Recordset. The result was this wacky looking code:

<?php do { ?> ><?php echo $row_distinctyear['YEAR(tblFishPassLyle.chkDt)']?> <?php } while ($row_distinctyear = mysql_fetch_assoc($distinctyear)); $rows = mysql_num_rows($distinctyear); if($rows > 0) { mysql_data_seek($distinctyear, 0); $row_distinctyear = mysql_fetch_assoc($distinctyear); } ?>

It works but isn’t there a cleaner way to do this in PHP? What is the "if…{echo “SELECTED”;} part trying to do?

This signature line intentionally blank.


#2

Wow, you aren’t kidding about that being wacky code!

If I understand what it’s doing correctly, the relevant code is this stuff which I’ve formatted to make it look a bit more understandable:

[code]<?php do { ?>

<?php echo $row_distinctyear['YEAR(tblFishPassLyle.chkDt)']?> <?php } while ($row_distinctyear = mysql_fetch_assoc($distinctyear)); ?> [/code] Unlike the perl code you're used to, PHP typically reads results from a database query one row at a time. mysql_fetch_assoc() is the function that PHP uses to retrieve the next row from the result set, returning FALSE when there are no more results to retrieve. mysql_fetch_assoc() fetches each result into an associative array where each key=>value pair represents a 'column name' => 'row value for that column' relationship. 'YEAR(tblFishPassLyle.chkDt)' is the column name assigned by DreamWeaver to the column that contains year values.

That bit about if(blah) echo “SELECTED” is intended to make the dropdown automatically select the entry for 2008. The way DW did it strikes me as being remarkably clunky, and if you want your site to validate as XHTML, I should point out that the code you have there will not validate.

To clean up the code, I would do the following:

  • Alias the table column so that you can use an array key that makes sense to humans.
  • Get rid of that line about SELECTED completely.
  • Set an initial value for $row_distinctyear[…] – note that the do-while loop means that it will not get set to a database value until the loop has already run through once.
  • Forget about all that code after the loop, you probably don’t need it.