Page 1 of 1

PHP Dynamic drop down lists from MySQL data

Posted: Tue May 25, 2010 2:04 pm
by hutch10
NOTE: Updated to add code on 02-JUN-2010 to help better explain issue.

I have a MS Access database with three tables that I want to promote to the web using PHP and MySQL as the tools. For a few weeks I performed searches to see if I could find a resolution to my question and have not been able to find anything of value. Thus my posting the question in this forum.

The people in the database can make change only to their own records; I know how to do this in PHP and MySQL. The issue I have is being able to generate a dynamic pull/drop down list within the form of the individual’s record of their preference of fruits and vegetables and their ranking.

The requirement is that the pull down list will be in alphabetical order, with “No Preference” as the last selection. They are required to place a rating/ranking of each item they have selected (the numbers from 1 to x are controlled and I know how to do that). At least one item must be selected.

The three tables are:
- The People table contains the information on the people along with the key of PeopleID
- The BusRule table contains the business rule category name, the order of display in the pull down list, and the description of the item within that specific category name along with the key of BusRuleID
- The Ratings table contains the joins between the People and BusRule table using their keys of PeopleID and BusRuleID along with its own key of RatingID and the rating the individuals have selected

Below is a sample of what could be found in the tables

Code: Select all

MEMBER table
MemberID   FirstName    LastName
1           Ricky       Ricardo
2           Lucy        Ricardo
3           Ethel       Mertz
4           Fred        Mertz

BUSRULE table
BusRuleID   BRName     BROrder    BRDesc
1           Fruit      1          Apple
2           Fruit      2          Raisins
3           Fruit      3          No Preference
4           Veggies    2          Zucchini
5           Veggies    1          Squash
6           Veggies    3          Tomato
7           Veggies    4          No Preference

RATINGS table
RatingID    MemberID    BusRuleID   Rating
1               1               2      1
2               1               1      2
3               1               7      1
4               2               1      1
5               2               2      2
6               2               6      1
7               2               4      2
When each person looks at their record, they can change their preferences.

The pull downs for Lucy Ricardo (person 2) would look something like:
This is the desired Drop Down list display (one line for each populated item, with a new line to add more selections, if needed)
This is the desired Drop Down list display (one line for each populated item, with a new line to add more selections, if needed)
dropdown.jpg (40.25 KiB) Viewed 246 times
Now, Lucy changes her mind and wants to replace Zucchini with Squash. The pull down list under VEGGIES lists all the veggie selections. So all Lucy needs to do is select Squash on the line where Zucchini is and replace it with Squash, leaving the rating of 2 alone. If Lucy wants to add more Veggies she can as the list is dynamically generated it will display a new line for additional input.

Here's the part of the code as it stands now:

Code: Select all

// This section requires that the user already be logged on to the system, if not, they are redirected to the login page which redirects them back to this page
<?php require_once('Connections/membersdb.php'); ?>
<?php
if (!isset($_SESSION)) {
  session_start();
}
$MM_authorizedUsers = "";
$MM_donotCheckaccess = "true";

// *** Restrict Access To Page: Grant or deny access to this page
function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) { 
  // For security, start by assuming the visitor is NOT authorized. 
  $isValid = False; 

  // When a visitor has logged into this site, the Session variable MM_Username set equal to their username. 
  // Therefore, we know that a user is NOT logged in if that Session variable is blank. 
  if (!empty($UserName)) { 
    // Besides being logged in, you may restrict access to only certain users based on an ID established when they login. 
    // Parse the strings into arrays. 
    $arrUsers = Explode(",", $strUsers); 
    $arrGroups = Explode(",", $strGroups); 
    if (in_array($UserName, $arrUsers)) { 
      $isValid = true; 
    } 
    // Or, you may restrict access to only certain users based on their username. 
    if (in_array($UserGroup, $arrGroups)) { 
      $isValid = true; 
    } 
    if (($strUsers == "") && true) { 
      $isValid = true; 
    } 
  } 
  return $isValid; 
}

$MM_restrictGoTo = "login.php";
if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {   
  $MM_qsChar = "?";
  $MM_referrer = $_SERVER['PHP_SELF'];
  if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";
  if (isset($QUERY_STRING) && strlen($QUERY_STRING) > 0) 
  $MM_referrer .= "?" . $QUERY_STRING;
  $MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);
  header("Location: ". $MM_restrictGoTo); 
  exit;
}
?>
//End of login validation
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}
//This section restricts the data to the members own record
mysql_select_db($database_membersdb, $membersdb);
$query_rsUpdate = "SELECT * FROM qrymembersratings WHERE qrymembersratings.eMail  = '" . $_SESSION['MM_Username'] . "'";
$rsUpdate = mysql_query($query_rsUpdate, $membersdb) or die(mysql_error());
$row_rsUpdate = mysql_fetch_assoc($rsUpdate);
$totalRows_rsUpdate = mysql_num_rows($rsUpdate);
//This secion is for the selection of the fruit drop down menu selections
mysql_select_db($database_membersdb, $membersdb);
$query_rsRatingsFruit = "SELECT * FROM qryratings WHERE qryratings.MemberID = '" . $row_rsUpdate['MemberID'] . "' AND qryratings.BRName = 'Fruit'";
$rsRatingsFruit = mysql_query($query_rsRatingsType, $membersdb) or die(mysql_error());
$row_rsRatingsFruit = mysql_fetch_assoc($rsRatingsFruit );
$totalRows_rsRatingsFruit = mysql_num_rows($rsRatingsFruit );
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Update</title>
</head>
<body>
<p>Update Profile</p>
<form id="update" name="update" method="post" action="">
  <p><input name="memberid" type="hidden" value="<?php echo $row_rsUpdate['MemberID']; ?>" />
    <label>First Name:*
      <input name="FirstName" type="text" id="FirstName" value="<?php echo $row_rsUpdate['First']; ?>" />
    </label>
    <label>LastName:*
      <input name="LastName" type="text" id="LastName" value="<?php echo $row_rsUpdate['Last']; ?>" />
    </label>
    <br />
        <strong>FRUIT</strong>:<br />
//This section lists the pull down with all the correct data but only lists it once.  Am looking for one instance for each item in the list to be displayed (see dropdown.jpg above for results
  <select name="Fruit1" id="Fruit1">
    <?php
do {  
?>
    <option value="<?php echo $row_rsRatingsFruit[BRName]?>"<?php if (!(strcmp($row_rsRatingsFruit[BRName], $row_rsRatingsFruit['BusRuleID']))) {echo "selected=\"selected\"";} ?>><?php echo $row_rsRatingsFruit[BRName]?></option>
    <?php
} while ($row_rsRatingsFruit = mysql_fetch_assoc($rsRatingsFruit));
  $rows = mysql_num_rows($rsRatingsFruit);
  if($rows > 0) {
      mysql_data_seek($rsRatingsFruit, 0);
	  $row_rsRatingsFruit= mysql_fetch_assoc($rsRatingsFruit);
  }
?>
//This is the rating drop down and its number is related to the each item in the fruit dropdown (again, see dropdown.jpg for desired results
  </select>
  <select name="FruitRating1" id="FruitRating1">
    <?php
do {  
?>
    <option value="<?php echo $row_rsRatingsFruit['Rating']?>"<?php if (!(strcmp($row_rsRatingsFruit['Rating'], $row_rsRatingsFruit['BusRuleID']))) {echo "selected=\"selected\"";} ?>><?php echo $row_rsRatingsFruit['Rating']?></option>
    <?php
} while ($row_rsRatingsFruit = mysql_fetch_assoc($rsRatingsFruit));
  $rows = mysql_num_rows($rsRatingsFruit);
  if($rows > 0) {
      mysql_data_seek($rsRatingsFruit, 0);
	  $row_rsRatingsFruit = mysql_fetch_assoc($rsRatingsFruit);
  }
?>
  </select>
  </p>
        <strong>VEGGIES</strong>:<br />
//This section lists the Veggies drop down with all the correct data but only lists it once.  Am looking for one instance for each item in the list to be displayed (see dropdown.jpg above for results
  <select name="Veggies1" id="Veggies1">
    <?php
do {  
?>
    <option value="<?php echo $row_rsRatingsVeggies[BRName]?>"<?php if (!(strcmp($row_rsRatingsVeggies[BRName], $row_rsRatingsVeggies['BusRuleID']))) {echo "selected=\"selected\"";} ?>><?php echo $row_rsRatingsVeggies[BRName]?></option>
    <?php
} while ($row_rsRatingsVeggies= mysql_fetch_assoc($rsRatingsVeggies));
  $rows = mysql_num_rows($rsRatingsVeggies);
  if($rows > 0) {
      mysql_data_seek($rsRatingsVeggies, 0);
	  $row_rsRatingsVeggies = mysql_fetch_assoc($rsRatingsVeggies);
  }
?>
//This is the Veggies rating drop down and its number is related to the each item in the Veggies dropdown (again, see dropdown.jpg for desired results
  </select>
  <select name="VeggiesRating1" id="VeggiesRating1">
    <?php
do {  
?>
    <option value="<?php echo $row_rsRatingsVeggies['Rating']?>"<?php if (!(strcmp($row_rsRatingsVeggies['Rating'], $row_rsRatingsVeggies['BusRuleID']))) {echo "selected=\"selected\"";} ?>><?php echo $row_rsRatingsVeggies['Rating']?></option>
    <?php
} while ($row_rsRatingsVeggies= mysql_fetch_assoc($rsRatingsVeggies));
  $rows = mysql_num_rows($rsRatingsVeggies);
  if($rows > 0) {
      mysql_data_seek($rsRatingsVeggies, 0);
	  $row_rsRatingsVeggies = mysql_fetch_assoc($rsRatingsVeggies);
  }
?>
  </select>
  </p>
</form>
</body>
</html>
//Clear variables
<?php
mysql_free_result($rsUpdate);
mysql_free_result($rsRatingsFruit);
mysql_free_result($rsRatingsVeggies);?>
How do you dynamically code the two pull/drop down lists based on the BusRule and Ratings table where it looks like the dropdown.jpg and not baddropdown.jpg image (below) which is what is happening with the above code?
This is how the current (bad) Drop Down List displays
This is how the current (bad) Drop Down List displays
baddropdown.jpg (24.09 KiB) Viewed 203 times
Since this is more geared towards PHP coding, that is why it is posted here and not in the database section. If this is off topic, please do not state off topic, please let me know where I should have posted this in the first place and I will repost in the correct forum. This way others will learn from my mistake.