• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 67922
  • Last Modified:

dynamic list box multiple select - option selected problem

I have an artist information update form, that has all previously selected values (i.e. name,gender,style,etc.) echoed in each of the form fields.

The only problem I have is with the dynamically populated multiple select list box. The list box pulls values (style names) from a database table called style.

The style table has 2 columns - style_id & style_name. This table is used to populate the list box. (see select statement below)

In another table called artist_style I have 2 columns: artist_id and style_id. This table is used to keep a record of the styles (from style table) that were previously selected by the artist filling out the form.

my current code only populates the list box and does not highlight prevoiusly selected values as I don't quite know how to do this.

the select statement is:

$query_sql = "SELECT name FROM style ORDER BY name";
$sql = mysql_query($query_sql) or die(mysql_error());
$row_sql = mysql_fetch_assoc($sql);
$totalRows_sql = mysql_num_rows($sql);

the php for the list box is:

<td bgcolor="#CAE1E9" class="medium"><select name="genre[]" size="20" multiple>
    <?php
      do {  
?>
    <option value="<?php echo $row_sql['name'];?>"><?php echo $row_sql['name']?></option>
    <?php
} while ($row_sql = mysql_fetch_assoc($sql));
  $rows = mysql_num_rows($sql);
  if($rows > 0) {
      mysql_data_seek($sql, 0);
        $row_sql = mysql_fetch_assoc($sql);
  }
?>
  </select>

I know that adding the word "selected" after the option value will highlight selections...I think I need some kind of if statement that tells it to echo "selected" when a style_id relates to the artist_id.

I am relatively new to php and mysql and was hoping someone could ammend the above code to create the list box with the selected values highlighted.
0
dblacker
Asked:
dblacker
  • 2
  • 2
  • 2
1 Solution
 
Georgiana GligorCommented:
yes, you need an "if" statement

one note, though: you don't transmit the names ($row_sql['name']) as values for <option>, but use the identifiers instead

So:

first, assume we are on the "style" page. We need to extract the information for the item which has to be displayed:
$style_identifier = $_GET[ "theID" ]; // comes from a previous page
$sql_page = "select * from style where style_id='" . $style_identifier . "'"; // we name it *_page so that we know this is the "current" item,
                                                                                                          // the one we need to show on this page
$query_page = mysql_query( $sql_page ) or die( mysql_error() );
$result_page = mysql_fetch_array( $result_page );

<td bgcolor="#CAE1E9" class="medium">
<select name="genre[]" size="20" multiple>
<?php
while( $row_sql = mysql_fetch_assoc( $sql ) ){  
?>
<!--                                                                           ||| down this line is my insertion point -->
    <option value="<?php echo $row_sql['style_id'];?>" <?php if( $row_sql['id'] == $result_page[ 'style_id' ] ){ echo " selected"; } ?>>
             <?php echo $row_sql['name']?>
   </option>
<?php
}
$rows = mysql_num_rows($sql);
if($rows > 0) {
   mysql_data_seek($sql, 0);
   $row_sql = mysql_fetch_assoc($sql);
}
?>
</select>
</td>

Georgiana
0
 
jdpipeCommented:
I've really gone to town here:

here is your database SQL:
-----------------------
CREATE TABLE `artist_style` (
  `artist_id` int(11) NOT NULL default '0',
  `style_id` int(11) NOT NULL default '0',
  KEY `artist_id` (`artist_id`)
) TYPE=MyISAM;

INSERT INTO `artist_style` VALUES (2, 1);
INSERT INTO `artist_style` VALUES (1, 3);

CREATE TABLE `artists` (
  `artist_id` int(11) NOT NULL auto_increment,
  `name` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`artist_id`)
) TYPE=MyISAM AUTO_INCREMENT=6 ;

INSERT INTO `artists` VALUES (1, 'skatellites');
INSERT INTO `artists` VALUES (2, 'lead zep');

CREATE TABLE `styles` (
  `style_id` int(11) NOT NULL auto_increment,
  `name` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`style_id`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;

INSERT INTO `styles` VALUES (1, 'rock & roll');
INSERT INTO `styles` VALUES (2, 'folk');
INSERT INTO `styles` VALUES (3, 'ska');
---------------------

Here is a PHP page which does the whole thing:
-----------------------
<?php

$DB=mysql_connect("localhost","test","test");
mysql_select_db("test");

if(isset($_POST['save'])){
      $id=save_artist();
      show_artist_form($id);
}elseif(isset($_GET['artist'])){
      show_artist_form($_GET['artist']);
}else{
      show_artist_list();
}

function show_artist_list(){
      $sql="SELECT artist_id,name FROM artists ORDER BY name";
      $res=sql($sql);
      print("<h1>Artist List</h1>");
      while($row=row($res)){
            print("<a href=\"?artist=".urlencode($row['artist_id'])."\">".htmlspecialchars($row['name'])."</a><br/>");
      }
      print("<a href=\"?artist=\"><b>add new</b></a>");
}

function show_artist_form($artist=NULL){
      $artist_styles=array();
      $name="";
?>
  <script>
     function formstatus(s){
        if(s){
           document.forms['f'].elements['save'].disabled=false;
           document.forms['f'].elements['cancel'].value="cancel";
           document.getElementById('msg').innerHTML='&nbsp;';
        }else{
           document.forms['f'].elements['save'].disabled=true;
        }
     }
  </script>
<?php
      
      if($artist){
            $sql="SELECT name FROM artists WHERE artist_id=".esc($artist);
            $res=sql($sql);
            if(num($res)){
                  $row=row($res);
                  $name=$row['name'];
            }else{
                  die("artist not found");
            }
            print("<h1>".htmlspecialchars($name)."</h1>");
            
            $artist_styles=artist_styles($artist);
      }else{
            print("<h1>New artist</h1>");
      }
      
      print("<div style=\"color:green\" id=msg>");
      if(isset($_POST['save'])){
            print("Your changes were saved!");
      }else{
            print("Make your changes then click 'save'");
      }
      print("</div>");
      
      print("<form name=f method=POST action=\".\">");
      print("<input type=hidden name=artist value=\"".htmlspecialchars($artist)."\" onchange=\"formstatus(1)\">");
      print("<input name=\"name\" value=\"".htmlspecialchars($name)."\" onchange=\"formstatus(1)\"><br/>");
      
      $sql="SELECT style_id,name FROM styles ORDER BY name";
      $res=sql($sql);
      while($row=row($res)){
            print("<input type=checkbox name=\"styles[]\"");
            if(in_array($row['style_id'],$artist_styles)){
                  print(" checked");
            }
            print(" value=\"".htmlspecialchars($row['style_id'])."\" onclick=\"formstatus(1)\">");
            print(" ".htmlspecialchars($row['name'])."<br/>");
      }
      
      print("<input type=submit name=save value=save>");
      print("<input type=button name=cancel value=\"back to list\" onclick=\"location='.'\">");
      print("</form>");

      print("<script>formstatus(0)</script>");
      if(isset($_POST['save'])){
            print("<script>document.forms['f'].elements['cancel'].value='back to list'</script>");
      }


}


function save_artist(){
      $id=$_POST['artist'];
      
      if(!$_POST['name']){
            die("Artist name not entered");
      }
      
      if($id){
            $sql="UPDATE artists SET name=".esc($_POST['name'])." WHERE artist_id=".esc($id);
            $res=sql($sql);
      }else{
            $sql="INSERT INTO artists(name) VALUES(".esc($_POST['name']).")";
            $res=sql($sql);
            $id=insid();
      }
      
      if(isset($_POST['styles'])){
            $selected_styles=$_POST['styles'];
      }else{
            $selected_styles=array();
      }
      
      $artist_styles=artist_styles($id);
      
      $sql="SELECT style_id FROM styles ORDER BY name";
      $res=sql($sql);
      while($row=row($res)){
            $current_style=$row['style_id'];
            if(
                in_array($current_style,$selected_styles)
              && !in_array($current_style,$artist_styles)
            ){
                  add_artist_style($id,$current_style)
                    or myerror("Couldn't add style to artist");
            }elseif(
                !in_array($current_style,$selected_styles)
              && in_array($current_style,$artist_styles)
            ){
                  remove_artist_style($id,$current_style)
                    or myerror("Couldn't remove style from artist");
            }
      }
      
      return $id;      
      
}
      

function artist_styles($artist){
      $artist_styles=array();
      $sql="SELECT style_id FROM artist_style WHERE artist_id=".esc($artist);
      $res=sql($sql);
      while($row=row($res)){
            $artist_styles[]=$row['style_id'];
      }
      return $artist_styles;
}

function add_artist_style($artist,$style){
      $sql="INSERT INTO artist_style(artist_id,style_id) VALUES(".esc($artist).",".esc($style).")";
      return sql($sql);
}

function remove_artist_style($artist,$style){
      $sql="DELETE FROM artist_style WHERE artist_id=".esc($artist)." AND style_id=".esc($style);
      return sql($sql);
}
            
function esc($str){
      return "'".mysql_escape_string($str)."'";
}

function sql($sql){
      global $DB;
      return mysql_query($sql,$DB);
}

function row($res){
      if($res){
            return mysql_fetch_assoc($res);
      }else{
            return false;
      }
}

function num($res){
      return mysql_num_rows($res);
}

function insid(){
      global $DB;
      return mysql_insert_id($DB);
}

function myerror($str){
      die($str.": ".mysql_error());
}
0
 
Lance_FrisbeeCommented:
To town you have gone... :)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Lance_FrisbeeCommented:
Oh wow. I'm so smart! :x Thanks ThG... I obviously didn't deserve those. I had an email saying "Good Assist" today, and I was thinking... I never gave any answers :)

Lance
0
 
jdpipeCommented:
Thanks, ThG!
0
 
Georgiana GligorCommented:
thanx dblacker
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now