p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

beginning_php thread: Dropdown selection


Message #1 by "Wayne Frisch" <wfrisch@b...> on Sat, 6 Apr 2002 19:55:06
I have a dropdown menu created by the following code in a include file;

$country = "<td>Country:</td><td>
<select name=Country>
<option>England</option>
<option>Canada</option>
<option>US</option>
</select></td>";

When a user selects the country I would like it to go to a mysql table
and held in the table each country with have the appropiate code to 
populate a dropdown in the same form automatically

EXAMPLE:
User selects England

dB info
database: photo_manager 
table: coin_meta

In the table coin_meta the field England hold the following

<td>England:</td><td>
<select name=currency_value>
<option>1/2 Pence</option>
<option>Farthing</option>
<option>etc1</option>
<option>etc2</option>
<option>etc3</option>
</select></td>";


any tips valued on how to code the page to do this.

Thanks

Wayne
Message #2 by "Gellings, C.O." <gellingsco@p...> on Sat, 6 Apr 2002 22:10:48 +0200
you need to create a form-page

<?
==> store your db name, server name, password, & login in variables to make
it flexible (I do it in an include file, which I enclose by :
include("./dirtree/filename.whatever"); ).
?>
<html ....

<body>
(<table>)
<form method="get" action"[samepage.php]">
<table>
<tr>
<td>Country:</td><td>
> <select name="Country" onchange="javascript: document.forms[0].submit()">
> <option value="england">England</option>
> <option value="Canada">Canada</option>
> <option value="US">US</option>
> </select></td>
<?
if($Country != '')
{
	?><tr>
	<td>
	<select name="currency_value"><?
		getOptions("Servername", "Login","Password","DBName",$Country,
"coin_meta")
	?></select>
	</td><?
}
?></table>
</form>
(</table>)
</body .................

<?

function getOptions($DBServer, $DBLogin,$DBPassword,$DBName,$value,
$tablename)
{
	$DBConnect = mysql_connect($DBServer, $DBLogin,$DBPassword);
	$DBSelect = mysql_select_db($DBName, $DBConnect );

	$result = mysql_db_query($DBName, "SELECT * FROM ".$tablename);
	while($rs = mysql_fetch_array ($result))
	{
	?><option value="<? echo $rs[(id)]."\"";
	if($rs[(id)] == $value) echo " selected";?>><? echo
$rs[(descriptionfield)]."</option>\n";
   }
   mysql_free_result ($result);
   mysql_close($DBConnect);
}
?>

Your coin_meta table should look like this
id, descriptionfield, any other fields you want to add

You can do this too for your country data then you run the function on the
spot where you want it without the 'if' - clause.

Best cheers,

Carl


Message #3 by "Peter Simard" <peter@p...> on Sat, 6 Apr 2002 21:23:23
Here's a function that will do what you want:
---------------------------------------------
<?php
function getCurrencyValues()
{
$db = "";//your database name goes here
$user = "";//your username on the DB goes here
$dbPass = "";//your pawword goes here
$host = "localhost";
$conn = mysql_connect( $host, $user, $dbPass ) or die ( mysql_error
("Unable to connect"));
print("Successfully connected to database");
$sql = "SELECT DISTINCT country, currency, curr_value FROM 
country_currency_data";
$result = mysql_db_query( $db, $sql, $conn ) or die( mysql_error("Query 
Failed"));

    for($i = 0; $i < mysql_num_rows( $result ); $i++)
     {
      $tmp = mysql_fetch_row( $result );
      print("<OPTION value=\"$tmp[0]:$tmp[1]:$tmp[2]\">$tmp[0]:$tmp
[1]:$tmp[2]</OPTION>\n");
     }
}
?>
<SELECT NAME="country_currency">
<?php getCurrencyValues() ?>
</SELECT>
</FORM>
--------------------------------------------------------
And here's the sql to create the table for the above, or just modify the 
above function to your purposes.

DROP TABLE IF EXISTS country_currency_data;
CREATE TABLE country_currency_data (
  ccid int(11) NOT NULL default '0',
  country varchar(30) default NULL,
  currency varchar(30) default NULL,
  curr_value double NOT NULL default '0',
  PRIMARY KEY  (ccid)
) TYPE=MyISAM;

Put some data into your new table:

for eaxample:
INSERT INTO country_currency_data VALUES (0, 'us', 'dollar', '1');

Then load the .php page and you should see a drop down box all populated

Cheers,

Pete
Message #4 by "Peter Simard" <peter@p...> on Sun, 7 Apr 2002 19:59:38
Ideally you want to abstract out the DB factors here, but I wrote this 
quick for demo prposes.

Pete
Message #5 by "Nikolai Devereaux" <yomama@u...> on Mon, 8 Apr 2002 09:29:30 -0700
The function does what you want, but I'd do things a bit differently.

See, it doesn't make too much sense in my mind to generate <OPTION> tags
when you're not generating the <SELECT> and </SELECT> tags to go with them.

getCurrencyValues() sounds like a function that returns some array of
currency values; that OPTION tags are generated and nothing is returned
seems counter-intuitive.

I'd write a function that got an array of the values, and write another
function, create_select() that returned or output a complete
<SELECT><options...></SELECT> tag family for any array of values.


Nik

Message #6 by "Peter Simard" <peter@p...> on Mon, 8 Apr 2002 19:33:20
Nik;

Would this along the lines of what you're saying, or is this still too 
kludgy?

Pete

<?php
define("DB","forum");
define("USER","???");
define("DBPASS","???");
define("HOST","localhost");

$table = "country_currency_data";
echo "Successfully connected to database<BR>";
function createSelect($nam, $tbl, $query)
    {
    printf("<FORM name=\"frm$nam\">\n");
    printf("<SELECT name=\"sel$nam\">\n");
    getCurrencyValues($tbl, $query );
    printf("</SELECT>\n");
    printf("</FORM>\n");
    }
function getCurrencyValues( $t, $q )
{
 $table = $t;
 $sql = $q;
$conn = mysql_connect( HOST, USER, DBPASS ) or die ( mysql_error());

$result = mysql_db_query( DB, $sql ) or die( mysql_error("Query Failed"));

    for($i = 0; $i < mysql_num_rows( $result ); $i++)
     {
      $tmp = mysql_fetch_row( $result );
      print("<OPTION value=\"$tmp[0]:$tmp[1]:$tmp[2]\">$tmp[0]:$tmp
[1]:$tmp[2]</OPTION>\n");
     }
}
?>
<SELECT NAME="country_currency">
</SELECT>
</FORM>
<?php createSelect( "test", "country_currency_data", "SELECT * FROM 
country_currency_data" ); ?>

Message #7 by "Wayne Frisch" <wfrisch@b...> on Mon, 8 Apr 2002 20:09:08
Thanks everone for all your support. As usual the support here is 
exceptional. I am about to try all the suggestions today

Wayne


> you need to create a form-page

<?
==> store your db name, server name, password, & login in variables to make
it flexible (I do it in an include file, which I enclose by :
include("./dirtree/filename.whatever"); ).
?>
<html ....

<body>
(<table>)
<form method="get" action"[samepage.php]">
<table>
<tr>
<td>Country:</td><td>
> <select name="Country" onchange="javascript: document.forms[0].submit()">
> <option value="england">England</option>
> <option value="Canada">Canada</option>
> <option value="US">US</option>
> </select></td>
<?
if($Country != '')
{
	?><tr>
	<td>
	<select name="currency_value"><?
		getOptions
("Servername", "Login","Password","DBName",$Country,
"coin_meta")
	?></select>
	</td><?
}
?></table>
</form>
(</table>)
</body .................

<?

function getOptions($DBServer, $DBLogin,$DBPassword,$DBName,$value,
$tablename)
{
	$DBConnect = mysql_connect($DBServer, $DBLogin,$DBPassword);
	$DBSelect = mysql_select_db($DBName, $DBConnect );

	$result = mysql_db_query($DBName, "SELECT * FROM ".$tablename);
	while($rs = mysql_fetch_array ($result))
	{
	?><option value="<? echo $rs[(id)]."\"";
	if($rs[(id)] == $value) echo " selected";?>><? echo
$rs[(descriptionfield)]."</option>\n";
   }
   mysql_free_result ($result);
   mysql_close($DBConnect);
}
?>

Your coin_meta table should look like this
id, descriptionfield, any other fields you want to add

You can do this too for your country data then you run the function on the
spot where you want it without the 'if' - clause.

Best cheers,

Carl


Message #8 by "Nikolai Devereaux" <yomama@u...> on Mon, 8 Apr 2002 12:13:25 -0700
> Would this along the lines of what you're saying, or is this still too
> kludgy?

You're still creating the <SELECT> tag in a different place than you're
creating the OPTIONS tags.


> function createSelect($nam, $tbl, $query)
> {
>   printf("<FORM name=\"frm$nam\">\n");
>   printf("<SELECT name=\"sel$nam\">\n");
>   getCurrencyValues($tbl, $query );
>   printf("</SELECT>\n");
>   printf("</FORM>\n");
> }

The createSelect() doesn't have anything to do with the database, it's
purely a form tool function.  So it should create your SELECT and all the
OPTIONS tags.  You KNOW it doesn't have anything to do with the database
because the tablename and SQL query that you pass to it are unused within
the scope of this function, save for passing them to getCurrencyValues().


> function getCurrencyValues( $t, $q )
> {
>   $table = $t;
>   $sql = $q;
>   $conn = mysql_connect( HOST, USER, DBPASS ) or die ( mysql_error());
>
>   $result = mysql_db_query( DB, $sql ) or die( mysql_error("Query
Failed"));
>
>   for($i = 0; $i < mysql_num_rows( $result ); $i++)
>   {
>     $tmp = mysql_fetch_row( $result );
>     print ("<OPTION value=\"$tmp[0]:$tmp[1]:$tmp[2]\""
>          . ">$tmp[0]:$tmp[1]:$tmp[2]</OPTION>\n");
>   }
> }

A few comments:
  I personally think that there should already be a connection to the
database open, so this function doesn't need to make the connection.  Also,
I think that the function mysql_db_query() has been deprecated.  If your
site uses more than one database, then you can certainly select the db
before performing a query on it, but repeating the connection string seems
pointless.

  The first few lines of this function in effect copy the parameters to
variables of new names.  If you didn't want $t and $q as variable names,
then name them $table and $sql in the parameter list and delete the first
two lines of your function.

  Notice here that you're outputting OPTION tags.  OPTION tags are pretty
meaningless unless they're surrounded by <SELECT> tags.  So this function is
completely useless unless you happen to call it between some other code that
outputs the select tags for you.  It's best to keep HTML well-formed, which
not only means you should write your code such that all tag scopes that are
opened are closed, but you should prevent a situation where someone can use
your functions to generate bad HTML.

For instance:
<SELECT>
<div ...>
getCurrencyValues(...);
</SELECT>
</div>

Yes, this is the fault of the HTML coder, but could've been avoided if the
<SELECT> tags were created in the same function that the OPTIONS were.

Lastly, I know I mentioned this before, but here it is again.  The function
name is "getCurrencyValues".  You'd think that the function GETS something
for you.  No one would think anything strange of this code:

  $cvals = getCurrencyValues(..);

The name of the function implies that it returns something (namely, currency
values).  So this code here doesn't look right:

  printf("<SELECT name=\"sel$nam\">\n");
  getCurrencyValues($tbl, $query );
  printf("</SELECT>\n");



I was thinking more something like the following.  Keep in mind that there's
a bunch of ways to do this.  (I just wrote a variation of this yesterday
that uses a different indexing scheme for the arrays.)


--  config.inc  --
<?php
  $DB_HOST = "db.domain.com";
  $DB_USER = "username";
  $DB_PASS = "password";
  $DB_NAME = "database";
  ...
?>
-----------


--  db.inc  --
<?php
  // db abstraction wrappers
  function db_connect(..){..}
   ...

  // pseudo-stored procedures

  /**
   *  this returns an array that maps country codes to a currencies.
   *  e.g.
   * {
   *   [USA] => 'USD'
   *   [UK]  => 'GBP'
   * }
   */
  function getCurrencyValues()
  {
    $query = "SELECT country, currency FROM country_currency_info";
    $result = db_query($query); // notice use of generic wrapper.

    $ret = array(); // will store return values
    while(list($country, $currency) = each(db_fetch_row($result)))
    {
        $ret[$country] = $currency;
    }

    return $ret;
  }
?>
-----------


--  form_tools.inc  --
<?php

  /**
   *  This creates a SELECT and OPTIONS family of tags.
   *  $options is a string-indexed array where each key is
   *  the "value" attribute of the option, and the value is
   *  the text between the OPTION tag.
   *
   *  $default is an optional string parameter which lets you
   *  specify if one of your options values should be preselected.
   *
   *  Example:

$cereals = array('rk' => 'Rice Krispies',
                 'ch' => 'Cheerios',
                 'cp' => 'Cocoa Puffs',
                 'bo' => 'Honey Bunches of Oats');
echo create_select('cereal', $cereals, 'cp');

   *
   *  This will generate the following HTML:
   *

  <SELECT name="cereal">
    <OPTION value="rk">Rice Krispies</OPTION>
    <OPTION value="ch">Cheerios</OPTION>
    <OPTION value="cp SELECTED>Cocoa Puffs</OPTION>
    <OPTION value="bo">Honey Bunches of Oats</OPTION>
  </SELECT>

   */
  function create_select($name, $options, $default = "")
  {
    $tags = "<SELECT name=\"$name\">\n";

    // http://www.php.net/foreach
    foreach($options as $value => $text)
    {
      $tags .= "  <OPTION value=\"$value\"";

      if($value == $default)
      {
        $tags .= ' SELECTED';
      }

      $tags .= ">$text</OPTION>\n";
    }

    $tags .= "</SELECT>\n";

    return $tags;
  }
?>
-----------


As always, keep in mind that I typed all this in the email window and no
where else -- hence, the code is untested, subject to bugs and typos, and
will definitely NOT brew your coffee or remind you of your upcoming
anniversary*.


Take care,

Nik

* Go buy a gift now and hide it just in case.

Message #9 by "Peter Simard" <peter@p...> on Tue, 09 Apr 2002 11:03:36 -0400
Nik;

Are we assuming for this example that $cereals array is hard coded? 
Does this not limit it's use as pertains to the country=3D>currency key
 value pairs, or is this fine since country=3D>currency pairs don't change,
 so we simply hard code in
those country=3D>currency pairs we plan to use?
Take care,

Pete

Message #10 by "Nikolai Devereaux" <yomama@u...> on Tue, 9 Apr 2002 11:35:13 -0700
> Are we assuming for this example that $cereals array is hard coded?

$cereals was hardcoded because the data itself is unimportant.  It's the
format of the data (the meaning of the key/value pairs of the array) and
what's done with the data that we're talking about here.  Remember, the
$cereals example was just an example to illustrate how the create_select()
function could be called, and what it would generate with some sample input.

The $cereals = array(...) line can be substituted with this:

$cereals = getCereals();

which illustrates what I think getXXX() functions should be used for.


> Does this not limit it's use as pertains to the country=>currency
> key value pairs, or is this fine since country=>currency pairs
> don't change, so we simply hard code in
> those country=>currency pairs we plan to use?

NOnono, don't hard code anything.  Remember that I also provided a sample
getCurrencyValues() function.  This returns an array where the key is the
country name and the value is the currency name.  This isn't the be-all
end-all way to create this array; again, it's just an example.

The point is that all create_select() expects is a string and an array.  You
have a function that returns you an array, which conveniently happens to
consist of key/value pairs that we can use.

You can use these two functions in your script like this:

<?php
   echo create_select('currencyValues', getCurrencyValues());
?>

This would generate the following HTML:

  <SELECT name="currencyValues">
    <OPTION value="USA">USD</OPTION>
    <OPTION value="UK">GBP</OPTION>
       ...
  </SELECT>



Remember, it's not the actual data that I'm interested in.  The functions
allow you to separate unrelated tasks from each other.  Your main form
doesn't have any HTML in it.  Your form code doesn't have any SQL in it.
Your database code doesn't have any HTML in it.

It's almost like database schema normalization -- nothing's redundant, and
different tables just use each other to access information instead of
duplicating it.

Hopefully, this cheesy chart helps illustrate the idea:

+-----------------------------------------------------------------+
|   file    | outputs anything?  | contains HTML? | contains SQL? |
+-----------+--------------------+----------------+---------------+
| main file |       Yeep         |                |               |
| form inc  |                    |     Yeep       |               |
| db inc    |                    |                |     Yeep      |
+-----------------------------------------------------------------+



Hope this helps,

Nik

Message #11 by "Peter Simard" <peter@p...> on Tue, 09 Apr 2002 16:00:00 -0400
Thanks Nik.

Your final explanation cleared things up.  

"Genericising" my code has been the biggest barrier for me as I attempt to
 move from straight scripting ( read a procedural model ) to a more
 function based model.

As always, thanks for your expertise,

Pete
Take care,

Pete

Message #12 by "Nikolai Devereaux" <yomama@u...> on Tue, 9 Apr 2002 13:47:14 -0700
No problem.

It takes a lot longer to create a site by going the long way about it and
writing all the functions and abstraction wrappers etc.  But if you're
planning on doing more PHP sites in the future, you'll find that you can
reuse the utility functions you wrote the first time around, and because
everything's so separated, you can only use the stuff that makes sense for
your project without reinventing the wheel for so much of the simple
drudgery involved with starting from scratch.

In my first code-filled email, I had pseudo-stored procedures listed in
db.inc.  This isn't actually how I'd do things;  I'd create a file called
dbprocs.inc or something like that to contain all the functions that mimic
stored procedures (this should be the only file that contains the SQL needed
for a site to run.)

db.inc simply wraps whatever database you're using.  I like the phpnuke idea
where you can specify in the config file which database you're using in a
variable, and the db.inc will wrap the correct functions appropriate to that
database.  The rest of the site doesn't know or care WHICH database you're
using, it only uses generic db_XXX() functions.

I'm one of the only people I know that tries to mimic stored procedures to
hide any SQL in your scripts.  They really don't do a lot for your site
except put all your queries in one place, so that if something goes wrong
with a common query, you fix it once.  That helps eliminate a lot of typos
associated with most cut n' paste style coding.  It makes the main part of
your site much more readable since another programmer doesn't have to sift
through a long query and error check song n' dance when he could've just
read "getCurrencyValues()" instead.


Take care,

Nik


  Return to Index