CODEWITHSUNDEEP
phpmysqlarrays
Anu
Anu

Reputation: 925

Storing array values in single column MySQL database

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?

Upvotes: 2

Views: 1622

Answers (1)

Amit
Amit

Reputation: 342

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.

Upvotes: 1

Related Questions