Storing array values in single column MySQL database

Question

In my application , i have the form to enter the details of training session to be held in office. So im storing all details in 'training' table. But in that , one column as 'trainee', in that i have to fill multiple names.SO i used multiple select option in html and im getting all the names in an array. And then i save that in database. Im serailizing that array and store it there. But just i want to store the names. Furthur, i have to get reports of any particular trainee so i have to get the trainee list again in some other pages. Just below i shoed my table structure and php coding.

CREATE TABLE `training` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(45) DEFAULT NULL,
  `category` varchar(45) DEFAULT NULL,
  `course` varchar(45) DEFAULT NULL,
  `trainee` varchar(45) DEFAULT NULL,
  `trainer` varchar(45) DEFAULT NULL,
  `startdate` date DEFAULT NULL,
  `enddate` varchar(45) DEFAULT NULL,
  `venue` varchar(45) DEFAULT NULL,
  `status` varchar(45) DEFAULT NULL,
  `comments` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

Form control to select multiple trainees:

<div class="form-group">
    <label for="tr" class="control-label col-md-2">Trainee</label>      
    <div class="col-md-4"> 
          <select  multiple="multiple" id="trainee" name="tr[]" >
            <?php foreach($empresult as $row){ ?>
            <option><?php echo $row['first_name']?></option>
            <?php  } ?>
        </select>       
    </div>
</div>

Database code:

        $data= $_POST['tr'];
        $trainees= serialize($data);

        $cat=$_REQUEST['cat'];
        $course=$_REQUEST['course'];
        $venue=trim($_REQUEST['venue']);            
        $ins=$_REQUEST['ins'];  
        $start=$_REQUEST['startdt'];
        $end=$_REQUEST['enddt'];            
        $typ=$_REQUEST['type'];
        $stat=$_REQUEST['stat'];
        $cmt=$_REQUEST['cmt'];      

    $sql = "INSERT INTO training(type,category,course,trainee,trainer,startdate,enddate,venue,status,comments) VALUES " . "(:type,:category,:course,:trainee,:trainer,:startdate,:enddate,:venue,:status,:comments)";       
    try {               
          $stmt = $DB->prepare($sql);           
          // bind the values
            $stmt->bindValue(":type", $typ);
            $stmt->bindValue(":category", $cat);
            $stmt->bindValue(":course", $course);
            $stmt->bindValue(":trainee", $trainees);    
            $stmt->bindValue(":trainer", $ins);
            $stmt->bindValue(":startdate", $start);
            $stmt->bindValue(":enddate", $end);
            $stmt->bindValue(":venue", $venue); 
            $stmt->bindValue(":status", $stat); 
            $stmt->bindValue(":comments", $cmt);    
          $stmt->execute(); 
          }
        } 
        catch (Exception $ex)
        {           
          $_SESSION["errorMsg"] = $ex->getMessage();              
        }

In database it get saved as:

a:4:{i:0;s:7:"Anu1234";i:1;s:11:"UmaSri12345"

I chose four trainees, because i set trainee- varchar(45), so half of the list is getting stored in table.

Just im afraid whether this is the correct approach to do that? and to store only string (names) what can i do?

I suggest change serialize() to json_encode() to be readable in database. — aldrin27
– aldrin27, Commented Mar 2, 2016 at 6:38
Keeping trainee details in a separate table would be a good idea. This will help you to query and get data related to trainee — Edwin Alex
– Edwin Alex, Commented Mar 2, 2016 at 6:39

Amit · Accepted Answer · 2016-03-02 09:58:47Z

1

I think it would be nice to keep trainee data in different table and use that trainee id in your training table. Google for Database Normalization. It will give you idea of how to structure database.

answered Mar 2, 2016 at 9:58

Amit

3421 silver badge14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Storing array values in single column MySQL database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related