在mysql codeigniter中插入和更新一个查询
我是codeigniter的新手,我正在做以下的事情,但我没有得到解决方案。在mysql codeigniter中插入和更新一个查询
以下是一件一件的事情。
-
首先我从数据库中获取数据并通过foreach循环以表格格式显示。 $''''''''''''''''''''''''''''''''); echo form_open('emp_hodm_update/update_hodm',$ attributes); ? >
日期 工作 合作伙伴 主任 时间 任务 状态 行动 日期;?> “类= ”“/> 工作;?>” class =“”/> partner;?>“class =”“/> 主任;?> “类= ”“/> 时间;?>” 类= “”/> 任务;?> 状态;?> 删除 通过点击添加按钮它会创建新的行所以我想更新旧的数据以及通过添加buttond插入到表中创建的新行数据。
但我没有得到任何想法如何做到这一点。
控制器:
public function update_hodm(){
/* Checking the all validation of task form*/
//$this->form_validation->set_rules('date', 'Date', 'required');
$this->form_validation->set_rules('work[]', 'Types of Work', 'required');
//$this->form_validation->set_rules('partner[]', 'Worked With', 'required');
$this->form_validation->set_rules('director[]', 'Director', 'required');
$this->form_validation->set_rules('time[]', 'No Of Hours', 'required');
$this->form_validation->set_rules('task[]', 'Task Details', 'required');
$this->form_validation->set_rules('status[]', 'Task Status', 'required');
if ($this->form_validation->run()) {
/* Taking the data from form*/
$todayDate = date('Y-m-d');
$work=$this->input->post('work');
$partner=$this->input->post('partner');
$director=$this->input->post('director');
$time=$this->input->post('time');
$task=$this->input->post('task');
$status=$this->input->post('status');
$count=count($this->input->post('work'));
$data =array();
for($i=0; $i<$count; $i++) {
$data[$i] = array(
'name' =>$this->session->userdata('emp_name'),
'date' =>$todayDate,
'work' =>$work[$i],
'partner' =>$partner[$i],
'director' =>$director[$i],
'time' =>$time[$i],
'task' =>$task[$i],
'status' =>$status[$i]
);
}
$add=$this->update->update_hodm($data,$todayDate);
/* Display Success message if data updated successfully in database*/
if($add){
$this->session->set_flashdata('hodm_form',"All HODM Data Inserted Successfully.");
$this->session->set_flashdata('hodm_form_class','alert-success');
}else{
/* Displaying the error message*/
$this->session->set_flashdata('hodm_form',"failed to add, Please Try again");
$this->session->set_flashdata('hodm_form_class','alert-danger');
}
return redirect('home');
} else {
$this->load->view('public/digital_hodm_view');
}
}
型号:
public function update_hodm($data,$todayDate){
$this->db->where('date',$todaydate);
$this->db->update_batch('task_form', $data,'date');//date is my table column name
return true;
}
- 请帮我找到解决方案。
预先感谢
使用下面的代码视图中看到 增加一个列的updated_at
查看:
<?php
$attributes = array('class' => 'form-horizontal','id'=>'update_form');
echo form_open('emp_hodm_update/update_hodm', $attributes);
?>
<table class="table table-striped table-hover table-responsive">
<thead>
<tr class="">
<th>date</th>
<th>Work</th>
<th>Partner</th>
<th>Director</th>
<th>Time</th>
<th>Task</th>
<th>Status</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php foreach($result as $row) { ?>
<tr>
<td>
<input class="form-control" type="text" name="date[]" value="<?php echo $row->date;?>" class="" />
</td>
<td>
<input class="form-control" type="text" name="work[]" value="<?php echo $row->work;?>" class="" />
</td>
<td>
<input class="form-control" type="text" name="partner[]" value="<?php echo $row->partner;?>" class="" />
</td>
<td>
<input class="form-control" type="text" name="director[]" value="<?php echo $row->director;?>" class="" />
</td>
<td>
<input class="form-control" type="text" name="time[]" value="<?php echo $row->time;?>" class="" />
</td>
<td>
<textarea class="form-control" name="task[]" rows="2" id=""><?php echo $row->task;?></textarea>
</td>
<td>
<textarea class="form-control" name="status[]" rows="2" id=""><?php echo $row->status;?></textarea>
</td>
<td>
<a href="" class="btn btn-danger">Delete</a>
<!-- Add This -->
<input class="form-control" type="hidden" name="form_id_hidden[]" value="<?php echo $row->form_id;?>"
</td>
</tr>
<?php } ?>
</tbody>
</table>
<input class="btn btn-primary" type="submit" name="submit" value="submit">
<input class="btn btn-primary" type="submit" name="submit" value="add">
<?php
echo form_close();
?>
控制器:
public function update_hodm(){
/* Checking the all validation of task form*/
//$this->form_validation->set_rules('date', 'Date', 'required');
$this->form_validation->set_rules('work[]', 'Types of Work', 'required');
//$this->form_validation->set_rules('partner[]', 'Worked With', 'required');
$this->form_validation->set_rules('director[]', 'Director', 'required');
$this->form_validation->set_rules('time[]', 'No Of Hours', 'required');
$this->form_validation->set_rules('task[]', 'Task Details', 'required');
$this->form_validation->set_rules('status[]', 'Task Status', 'required');
if ($this->form_validation->run()) {
/* Taking the data from form*/
$todayDate = date('Y-m-d');
<!-- Add This -->
$updatedDate = date('Y-m-d H:i:s'); // if not used H:i:s and there is no chnage in data update will return false
$work=$this->input->post('work');
$partner=$this->input->post('partner');
$director=$this->input->post('director');
$time=$this->input->post('time');
$task=$this->input->post('task');
$status=$this->input->post('status');
<!-- Add This -->
$form_id=$this->input->post('form_id_hidden');
$count=count($this->input->post('work'));
<!-- Add This -->
$data = $new_data = array();
$old_ids = array();
for($i=0; $i<$count; $i++) {
<!-- Add This -->
if(!in_array($form_id[$i],$old_ids)){
$old_ids[] = $form_id[$i];
$data[$i] = array(
'name' =>$this->session->userdata('emp_name'),
'date' =>$todayDate,
'work' =>$work[$i],
'partner' =>$partner[$i],
'director' =>$director[$i],
'time' =>$time[$i],
'task' =>$task[$i],
'status' =>$status[$i],
'form_id' =>$form_id[$i],
'updated_at' =>$updatedDate,
);
}else{
$new_data[$i] = array(
'name' =>$this->session->userdata('emp_name'),
'date' =>$todayDate,
'work' =>$work[$i],
'partner' =>$partner[$i],
'director' =>$director[$i],
'time' =>$time[$i],
'task' =>$task[$i],
'status' =>$status[$i],
);
}
}
<!-- Add This -->
$update=$this->update->update_hodm($data,'form_id');
$add = $this->update->addnew_hodm($new_data);
/* Display Success message if data updated successfully in database*/
if($update && $add){ <!-- Add This -->
$this->session->set_flashdata('hodm_form',"All HODM Data Updated and Inserted Successfully.");
$this->session->set_flashdata('hodm_form_class','alert-success');
}else if($update){ <!-- Add This -->
$this->session->set_flashdata('hodm_form',"All HODM Data Updated Successfully.");
$this->session->set_flashdata('hodm_form_class','alert-success');
}
else if($add){
$this->session->set_flashdata('hodm_form',"All HODM Data Inserted Successfully.");
$this->session->set_flashdata('hodm_form_class','alert-success');
}else{
/* Displaying the error message*/
$this->session->set_flashdata('hodm_form',"failed to add, Please Try again");
$this->session->set_flashdata('hodm_form_class','alert-danger');
}
return redirect('home');
} else {
$this->load->view('public/digital_hodm_view');
}
}
Model :
<!-- Add This -->
public function addnew_hodm($data){
if(empty($data)){
return false;
}
$this->db->insert_batch('task_form', $data);
return ($this->db->affected_rows()>0?TRUE:FALSE);
}
<!-- Add This -->
public function update_hodm($data,$where){
if(empty($data)){
return false;
}
$this->db->update_batch('task_form', $data,$where);
return ($this->db->affected_rows()?TRUE:FALSE);
}
。谢谢你的答案。它的工作现在.. – xr33dx
hmmmm欢迎好友 –
如果有独特之处在于表/主键,添加密钥为每个数据。假设关键是task_id
,更新您的控制器代码:
...
...
...
'task' =>$task[$i],
'status' =>$status[$i]
);
if(!empty($task_id[$i]))$data[$i]['task_id'] = $task_id[$i]; //Add task ids for old data
}
$add=$this->update->update_hodm($data,$todayDate);
在模型中,更新与下面的代码功能:
foreach($data as $array)
{
$query = 'INSERT INTO `table` SET ';
$sep = $setvalues = '';
foreach($array as $key=>$value) {
$setvalues .= $sep.$key.' = "'.$value.'"';
$sep = ',';
}
$query .= $setvalues.' ON DUPLICATE KEY UPDATE '.$setvalues.';';
$this->db->query($query);
}
VIEW:
<?php
$attributes = array(
'class' => 'form-horizontal',
'id' => 'update_form'
);
echo form_open('emp_hodm_update/update_hodm', $attributes);
?>
<table class="table table-striped table-hover table-responsive">
<thead>
<tr class="">
<th>date</th>
<th>Work</th>
<th>Partner</th>
<th>Director</th>
<th>Time</th>
<th>Task</th>
<th>Status</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php
foreach($result as $row)
{ ?>
<tr>
<td>
<input class="form-control" type="hidden" name="form_id[]" value="<?php
echo $row->form_id; ?>" class="" />
<input class="form-control" type="text" name="date[]" value="<?php
echo $row->date; ?>" class="" />
</td>
<td>
<input class="form-control" type="text" name="work[]" value="<?php
echo $row->work; ?>" class="" />
</td>
<td>
<input class="form-control" type="text" name="partner[]" value="<?php
echo $row->partner; ?>" class="" />
</td>
<td>
<input class="form-control" type="text" name="director[]" value="<?php
echo $row->director; ?>" class="" />
</td>
<td>
<input class="form-control" type="text" name="time[]" value="<?php
echo $row->time; ?>" class="" />
</td>
<td>
<textarea class="form-control" name="task[]" rows="2" id=""><?php
echo $row->task; ?></textarea>
</td>
<td>
<textarea class="form-control" name="status[]" rows="2" id=""><?php
echo $row->status; ?></textarea>
</td>
<td>
<a href="" class="btn btn-danger">Delete</a>
</td>
</tr>
<!--
start :if extra row is add on the button click
-->
<tr>
<td>
<input class="form-control" type="text" name="date[]" value="" class="" />
<!--
make sure at time of addition value of form_id[] should be empty
-->
<input class="form-control" type="hidden" name="form_id[]" value="" class="" />
</td>
<td>
<input class="form-control" type="text" name="work[]" value="" class="" />
</td>
<td>
<input class="form-control" type="text" name="partner[]" value="" class="" />
</td>
<td>
<input class="form-control" type="text" name="director[]" value="" class="" />
</td>
<td>
<input class="form-control" type="text" name="time[]" value="" class="" />
</td>
<td>
<textarea class="form-control" name="task[]" rows="2" id=""></textarea>
</td>
<td>
<textarea class="form-control" name="status[]" rows="2" id=""></textarea>
</td>
<td>
<a href="" class="btn btn-danger">Delete</a>
</td>
</tr>
<!--
end :
-->
<?php
} ?>
</tbody>
</table>
<input class="btn btn-primary" type="submit" name="submit" value="submit">
<input class="btn btn-primary" type="submit" name="submit" value="add">
<?php
echo form_close();
?>
控制器:
public function update_hodm(){
/* Checking the all validation of task form*/
// $this->form_validation->set_rules('date', 'Date', 'required');
$this->form_validation->set_rules('work[]', 'Types of Work', 'required');
// $this->form_validation->set_rules('partner[]', 'Worked With', 'required');
$this->form_validation->set_rules('director[]', 'Director', 'required');
$this->form_validation->set_rules('time[]', 'No Of Hours', 'required');
$this->form_validation->set_rules('task[]', 'Task Details', 'required');
$this->form_validation->set_rules('status[]', 'Task Status', 'required');
if ($this->form_validation->run()) {
foreach($_POST['form_id'] as $key => $value) {
$data = array(
'name' => 'aman',
'date' => $_POST['date'][$key],
'work' => $_POST['work'][$key],
'partner' => $_POST['partner'][$key],
'director' => $_POST['director'][$key],
'time' => $_POST['time'][$key],
'task' => $_POST['task'][$key],
'status' => $_POST['status'][$key]
);
if (!empty($value)) {
// update
$this->update->update_hodm($data, $value);
}
else {
// insert
$this->update->insert_hodm($data);
}
}
}
else {
$this->load->view('public/digital_hodm_view');
}
}
型号:
public function update_hodm($data,$form_id){
$this->db->where('form_id',$form_id);
$this->db->update_batch('task_form', $data,'date');//date is my table column name
return true;
}
public function insert_hodm($data){
$this->db->insert('task_form', $data);name
return true;
}
我不完全确定这将完全符合您的要求,因为我大部分假设然后代码,但这几乎是你想要的,因为我可以从你的问题了解问题,希望它会为你工作。告诉我,如果额外的帮助需要 –
为什么单击添加按钮时需要更新旧数据?只需在控制器中创建一个添加功能,并在添加按钮被点击时调用该功能 – Sultan
您实际上没有提到问题所在!除了_但我没有得知任何想法如何做到这一点。在这种情况下,解决方案是击中书籍 – RiggsFolly
@苏丹你没有得到我。当我点击添加按钮,然后它创建同一列的新行空白。在填充新行并在旧行更改时,当我单击提交按钮时,它会进入带有旧数据和新数据的表格 – xr33dx