Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

dynamic list box multiple select - option selected problem

Posted on 2004-09-29
7
Medium Priority
?
67,921 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
7 Comments
 
LVL 4

Accepted Solution

by:
Georgiana Gligor earned 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

636 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