Solved

dynamic list box multiple select - option selected problem

Posted on 2004-09-29
7
67,905 Views
Last Modified: 2013-12-12
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
Comment
Question by:dblacker
  • 2
  • 2
  • 2
7 Comments
 
LVL 4

Accepted Solution

by:
Georgiana Gligor earned 500 total points
ID: 12187506
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
 
LVL 7

Expert Comment

by:jdpipe
ID: 12187844
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
 
LVL 2

Expert Comment

by:Lance_Frisbee
ID: 12192169
To town you have gone... :)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Expert Comment

by:Lance_Frisbee
ID: 12218576
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
 
LVL 7

Expert Comment

by:jdpipe
ID: 12222507
Thanks, ThG!
0
 
LVL 4

Expert Comment

by:Georgiana Gligor
ID: 12224207
thanx dblacker
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now