SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2009
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple Select Box and MySQL

    I have a multiple selection box on my page and need to store the selections to one record in mysql:

    <select name="service[]" id="service" size="5" multiple="multiple">

    <option value="histopathology">Histopathology</option>
    <option value="cytology">Cytology</option>
    <option value="endometrial biopsy">Endometrial Biopsy</option>
    <option value="joint fluid">Joint Fluid</option>
    <option value="bone marrow">Bone Marrow</option>

    </select>


    I need to insert into one row what the user has picked. The table has a column for each selection and can be NULL for the selections not picked. The fields are expected to insert a '1' for the chosen selection. I could loop through the array and insert to mysql, but I need it in one single row.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    could you do a SHOW CREATE TABLE please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2009
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the following is the table I want to write to :


    services CREATE TABLE `services` (
    `service_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `form_number` int(20) NOT NULL,
    `histo` tinyint(1) DEFAULT NULL,
    `cytology` tinyint(1) DEFAULT NULL,
    `endo` tinyint(1) DEFAULT NULL,
    `joint_fluid` tinyint(1) DEFAULT NULL,
    `bone_marrow` tinyint(1) DEFAULT NULL,
    `other` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`service_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    thanks, that will help

    i just realized that you posted in the php forum, not the mysql forum, and i don't do php, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Aug 2009
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what I confused about is the sql insert statement. Do dynamically build the insert statement by looping through the values picked:

    $services = $_POST['service'];
    for ($i = 0; $i < count($service); $i++) {
    $serviceName = $serviceName.','.$service[$i];

    }



    $sql = "INSERT INTO services (histo, cytology, endo, joint_fluid, bone_marrow, other) values (' ".$serviceName ;

    but now I have the NULL values to think about that will not show up in the loop.
    Maybe change the strategy regarding the table?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    no, your table is fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    rlowe — you should change the <option> value attributes to match those column names (i.e. bone_marrow instead of bone marrow)

    Then do this:
    PHP Code:
    if(isset($_POST['my_form_submission...'])) {
        
        
    //Set form_number and other to your needs
        
    $form 1234;
        
    $other '';
        
        
    $cols = array('histo''cytology''endo''joint_fluid''bone_marrow');
        
        
    $query "INSERT INTO `services` (form_number, other, histo, cytology, endo, joint_fluid, bone_marrow) " .
                 
    "VALUES ($form, '$other'";
                 
        foreach(
    $cols as $col) {
            if(
    in_array($col$_POST['service'])) {
                
    $flag 1;
            }
            else 
    $flag 0;
            
            
    $query .= ", $flag";
        }
        
        
    $query .= ')'//close VALUES()
        
        //Debug
        
    echo $query;

    The allowed/expected column names are defined, so users can't mess with your query by creating their own <options>
    The code loops through the expected columns and flags them as 1 or 0 depending on whether they were selected.

  8. #8
    SitePoint Enthusiast
    Join Date
    Aug 2009
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent points. Thank you for your help.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •