Solved

dynamic list box multiple select - option selected problem

Posted on 2004-09-29
7
67,904 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

705 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

18 Experts available now in Live!

Get 1:1 Help Now