Duplicate Entry DB error (any clues)? - El Forum - 07-16-2009
[eluser]crispinatari[/eluser]
I've been trying to figure out why the rows in my table wont update for a few days and nothing is working yet, im trying to update/edit rows in a table
here is the error:
Quote:A Database Error Occurred
Error Number: 1062
Duplicate entry 'micjac' for key 1
UPDATE `tblemployee` SET `employeeID` = 'micjac' WHERE `employeeID` = 0
Quote:here is the form:
Edit Employee Details
Employee ID:
micjac
Department ID:
5551
First Name:
michael
Last Name:
jackson
click update button
here is the SQL
Code: tblemployee CREATE TABLE `tblemployee` (
`employeeID` varchar(6) NOT NULL,
`departmentID` int(4) NOT NULL,
`first_name` varchar(20) NOT NULL,
`last_name` varchar(20) NOT NULL,
PRIMARY KEY (`employeeID`),
KEY `FK_employeeID_departmentID` (`departmentID`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
here is the model and controller files and view
CONTROLLER
Code: <?php
class Emps extends Controller {
function emps()
{
parent::Controller();
//$this->load->scaffolding('emps');
$this->load->model('empsmodel');
$this->load->helper('url');
$this->load->helper('form');
$this->load->helper('html');
$this->load->library('validation');
$this->load->helper('form');
$this->load->scaffolding('entries');
}
function index()
{
$data['cssfile'] = base_url().'css/styles.css';
$data['cssfile2'] = base_url().'css/main.css';
$data['sidemenujs'] = base_url().'js/sliding_effect.js';
$data['title'] = "Employee application";
$data['heading'] = "Employee List";
//default method, gets data from model
//and lists all employees using default view
$data['res']=$this->empsmodel->get_all_emps();
$this->load->view('empslistview', $data);
}
function addemp()
{
$data['cssfile'] = base_url().'css/styles.css';
$data['cssfile2'] = base_url().'css/main.css';
//form for adding an entry';
$data['title'] = "Add Employee";
$data['heading'] = "Add Employee";
$this->load->view('empsaddview',$data);
}
function insertemp()
{
//form for adding an entry';
//calls the model function for actually inserting the new record
//get the data array from the post array sent by empsaddview.php
$data=$_POST;
print_r($data);
//if using helper
$data=$this->input->post();
//send the data to the insertemp function in the model
$this->empsmodel->insertemp($data);
//after insert, redirect back to main page
//header('Location: http://localhost/www/emps/index.php/emps/index');
//using url helper is easier, ie
redirect('empsaddview');
}
function deleteemp()
{
//called by empslistview url, with segment 3 = id
//which is still used by empsmodel->delete_entry
//id is not posted, just part of url in the link code
$this->empsmodel->deleteemp();
//using the url helper
redirect('views/index');
}
function editemp()
{
$this->load->database();
$this->db->where('employeeID',$this->uri->segment(3));
$data['query']=$this->db->get('tblemployee');
$this->load->view('empseditview',$data);
}
function update_emp()
{
$this->load->database();
$this->db->set('employeeID',$_POST['employeeID']);
$this->db->where('employeeID',$this->uri->segment(3));
$this->db->update('tblemployee');
//redirect('emps/editemp/');
redirect('emps/index/');
}
}
?>
MODEL
Code: <?php
class Empsmodel extends Model {
function Empsmodel() {
parent::Model();
}
function get_all_emps()
{
$query = $this->db->get('tblemployee');
return $query->result();
}
function insertemp($data)
{
$this->employeeID = $data['employeeID'];
$this->departmentID = $data['departmentID'];
$this->first_name = $data['first_name'];
$this->last_name = $data['last_name'];
//$this->db->insert('emps', $this, $data);
$this->db->insert('tblemployee', $this, $data);
}
function deleteemp()
{
//the id is part of the url in the link in the empsview
//using sql in a query, but still using url helper
$this->db->query('delete from tblemployee where employeeID = "'.$this->uri->segment(3).'"');
$this->db->where('employeeID',1);
$this->db->delete('tblemployee');
//ActiveRecord makes this easier, ie
$this->db->delete('tblemployee', array('employeeID' => $this->uri->segment(3)));
}
}
?>
Duplicate Entry DB error (any clues)? - El Forum - 07-16-2009
[eluser]Hakkam[/eluser]
Do you want update an EmployeID ( Primary Key ) ??? If so, you need put the original EmployeeID in other input type form and than, use this in Where condition in SQL.
Example.
In Form Views ...
Employee ID Orig: --> maybe hidden
your orig ID
Employee ID:
micjac
Department ID:
5551
First Name:
michael
Last Name:
jackson
click update button
In controller :
function update_emp()
{
$this->load->database();
$this->db->set('employeeID',$_POST['employeeID']);
$this->db->where('employeeID',$_POST['employeeID_orig']);
$this->db->update('tblemployee');
Duplicate Entry DB error (any clues)? - El Forum - 07-16-2009
[eluser]crispinatari[/eluser]
Thnaks for the reply!! does that mean i have to make a new column in my table called employeeID_Origin?
so here is the form view: does this mean i do this to the form?
Code: <html>
<head>
<title><?=$title?></title>
<link rel="stylesheet" type="text/css" href="employee.css" />
</head>
<body>
<h1></h1>
<h3>Edit Employee Details</h3>
<form action="http://localhost/ci1/index.php/emps/update_emp" method="post">
<?//or using form helper //=form_open('blog/insertentry');?>
<?php if($query->num_rows()==1):?>
<?php foreach($query->result() as $row):?>
<p>Employee ID_Origin:<br><input type='text' value=<?=$row->employeeID_Origin;?> name='employeeID_Origin'></p>
<p>Employee ID:<br><input type='text' value=<?=$row->employeeID;?> name='employeeID'></p>
<p>Department ID:<br><input type='text' value=<?=$row->departmentID;?> name='departmentID'></p>
<p>First Name:<br><input type='text' value=<?=$row->first_name;?> name='first_name'></p>
<p>Last Name:<br><input type='text' value=<?=$row->last_name;?> name='last_name'></p>
<?php endforeach;?>
<p><input type='submit' value='update' ></p>
<?php else:?>
<?php echo"error";?>
<?php endif;?>
</form>
</body>
</html>
and list view:
Code: <html>
<head>
<title>Display All Employees</title>
</head>
<body>
<center>
<form action="http://localhost/ci1/index.php/home/index" name="button2" align="left">
<INPUT type="submit" value="Back to Home"/>
</form>
<br>
<form action="http://localhost/ci1/index.php/side_menu/emps_add_page" name="button1" align="right">
<INPUT type="submit" value="Add Employee"/><br>
</form>
</center>
<br>
<?//if using url helper//=anchor('emps/addemp','Add Employee');?>
<table class="emps" width="57%" border="1" cellspacing="0" cellpadding="0" align="center">
<tr>
<th>Employee ID</th>
<th>Department ID</th>
<th>Employee First Name</th>
<th>Employee Last Name</th>
</tr>
<?php foreach($res as $row): ?>
<tr>
<td class="employeeID"><?=$row->employeeID?></td>
<td><?=$row->departmentID?></td>
<td ><?=$row->first_name?></td>
<td ><?=$row->last_name?></td>
<td><?=anchor('emps/editemp/'.$row->employeeID,'Edit');?></td>
<td><?=anchor('emps/deleteemp/'.$row->employeeID,'Delete');?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
<br><br>
</body>
</html>
Duplicate Entry DB error (any clues)? - El Forum - 07-16-2009
[eluser]Hakkam[/eluser]
In your table no need add a column anyway.
You just retrieve EmplodeID and put in input type form EmployeID_orig and in input type form EmployeID. And than when you will update db, the input type EmployeID_orig as in Where condition.
<?php foreach($query->result() as $row):?>
<p>Employee ID_Origin:<br><input type='text' value=<?=$row->employeeID;?> name='employeeID_Origin'></p>
<p>Employee ID:<br><input type='text' value=<?=$row->employeeID;?> name='employeeID'></p>
And in controller :
In controller :
function update_emp()
{
$this->load->database();
$this->db->set(‘employeeID’,$_POST[‘employeeID’]);
$this->db->where(‘employeeID’,$_POST[‘employeeID_Origin’]);
$this->db->update(‘tblemployee’);
Duplicate Entry DB error (any clues)? - El Forum - 07-16-2009
[eluser]crispinatari[/eluser]
Thank you! I did try your way and i get the message: "Disallowed Key Characters."
so i'll play around a little more with the code, also which input type do i hide? because at the moment i have
two input types, which could be confusing for a user, like
Employee ID_Origin:
<input type=‘text’ value=<?=$row->employeeID;?> name=‘employeeID_Origin’>
Employee ID:
<input type=‘text’ value=<?=$row->employeeID;?> name=‘employeeID’>
Duplicate Entry DB error (any clues)? - El Forum - 07-16-2009
[eluser]Hakkam[/eluser]
I think the hidden field is employeeID_Origin.
Duplicate Entry DB error (any clues)? - El Forum - 07-16-2009
[eluser]crispinatari[/eluser]
it didn't work though
|