Update/Edit a row using a form

[eluser]crispinatari[/eluser]
I'm curious as to what the best practice is when updating a row in a table that has a primary key as one of the fields and possibly a foriegn key that would affect other tables in a database, so is it better to in fact set autoincrement primary keys insteads just to save the hassle of time? the method im using sends back an error of "duplicate entry for key 1" ...so the employeeID field doesnt like being edited or added, interesting, are there any tutorials to help with crud?

Code:

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/');
  }

Code:

<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:<br>&lt;input type='text' value=&lt;?=$row-&gt;employeeID;?&gt; name='employeeID'></p>
<p>Department ID:<br>&lt;input type='text' value=&lt;?=$row-&gt;departmentID;?&gt; name='departmentID'></p>
<p>First Name:<br>&lt;input type='text' value=&lt;?=$row-&gt;first_name;?&gt; name='first_name'></p>
<p>Last Name:<br>&lt;input type='text' value=&lt;?=$row-&gt;last_name;?&gt;  name='last_name'></p>
&lt;?php endforeach;?&gt;
<p>&lt;input type='submit' value='update' &gt;&lt;/p>
&lt;?php else:?&gt;
&lt;?php echo"error";?&gt;
&lt;?php endif;?&gt;

&lt;/form&gt;

[eluser]Thorpe Obazee[/eluser]
autoincrement.

[eluser]crispinatari[/eluser]
Oh ok, does this mean i change my employeeID to autoincrement or is it best to create a new autoincrement column such as 1,2,3,4,5,6,7,8,9

[eluser]Cro_Crx[/eluser]
change the employeeID attribute in the database to autoincriment. When you're creating a new employee then don't insert an ID, the autoincriment will do it for you.

[eluser]crispinatari[/eluser]
still unsure because my employeeID is text and you cant autoincrement text because it changes to an int instead which manipulates the data in the column, so would i have to create two employeeID columns say like:

employeeID || employeeID_two
(autocincrement (varchar)
1 arnrim
2 micjak
3 jefgol
4
5
6

[eluser]Thorpe Obazee[/eluser]
It depends. Some companies set the employee IDs of their employees. However this employee 'id' is for your database.

[eluser]Cro_Crx[/eluser]
If you already have a column that is unique and can be used as a primary key then there is no need for another one. Why can't you just use the already existing field, the one in the example above as (employeeID_two).

To stop the database holding two users with the same ID, you should use a callback function when validating the data, something like this:

Code:

$this->form_validation->set_rules('employeeID', 'Employee ID', 'required|callback__id_already_exists');

function _id_already_exists($employee_id)
{
    // This should be put into a model but i'm just putting it here for simplicity
    $this->db->where('employeeID', $employeeID);
    if($this->db->get('tblemployee')->num_rows() > 0)
    {
        return false;
    }

    return true;
}

BTW: To access post variables you can use

Code:

$this->input->post('POST_ITEM')

instead of

Code:

$_POST['POST_ITEM']

It returns false if the item doesn't exist where as using $_POST directly doesn't!

[eluser]crispinatari[/eluser]
[quote author="Cro_Crx" date="1247825702"]change the employeeID attribute in the database to autoincriment. When you're creating a new employee then don't insert an ID, the autoincriment will do it for you.[/quote]

Ok i see what you are saying however the employeeID MUST be apart of the input form and its an input text/varchar so it cant be an autoincriment in that column ...

what if i just make it no primary key in the table?

im new to SQL

[eluser]Cro_Crx[/eluser]
well, no you should make the employeeID the primary key. Just set it to VARCHAR(255) or a greater number if you need more characters, you can set this as a PK.

[eluser]crispinatari[/eluser]
[quote author="Cro_Crx" date="1247848126"]well, no you should make the employeeID the primary key. Just set it to VARCHAR(255) or a greater number if you need more characters, you can set this as a PK.[/quote]

When i tried that it returns an error stating duplicate key though, even though there are no duplicate entires in that table, the primary key is also set to NOT NULL

message:

A Database Error Occurred

Error Number: 1062

Duplicate entry 'davlis' for key 1

UPDATE `tblemployee` SET `employeeID` = 'davlis' WHERE `employeeID` = 0

i'll try another way