Codeigniter: Move one row from one table to another at one time

Question

I'm kinda stuck here! I have to two tables free_members and domstic_members and I need to move selected rows from free_members tables to the domestic_members table. why I'm saying selected rows because of I at the end of every row in free_members tables there is an upgrade option which will move that row to the domestic_members table. I have the code but it's not working the way I want. right now what is happening is when I click on upgrade option it just copies the whole table records and sends it's to the other table. this here is the code

Controller

function upgradeprofile() {
    //  $this->load->database();
    $this->load->model('freemember_model');

    $this->freemember_model->upgrade();
}

model

function upgrade() {
    $query = $this->db->get('free_members');
    foreach ($query->result() as $row) {
        $this->db->where('id', $member_id);
        $this->db->insert('domestic_members', $row);
    }
}

View

<center>  Search Member:
                    <input type="text" name ='search' id='search' placeholder="Search a member"/></center>
                    <input type="hidden" name ='limit_from' id='limit_from'/>
                    </form>
<br><center><button type="button" onclick="CallPrint1('background12')">Print</button>
                <div id="background12" class="box-body">
                  <table id="example1" class="table table-bordered table-striped">
                    <thead>
                      <tr>
                        <th>Sr#</th>
                        <th><center>Full Name</th>


                        <th><center>Mobile Number</th>
                         <th><center>Membership# / CNIC</th>
                        <th><center>Email Address</th>

                        <th><center>Province</th>
                        <th><center>District</th>
                        <th><center>Tehsil</th>
                        <th><center>Union Council</th>
<?php /*?>  <?php if($sessiondata['deletemembers']):?><?php */?>
     <?php if($sessiondata['username'] != 'bilal.sabir'):?>
                        <th><center>Delete</th>
  <?php endif;?>

    <?php /*?><?php if($sessiondata['data_modification'] && $sessiondata['username'] != 'bilal.sabir' ):?><?php */?>
         <?php if($sessiondata['username'] != 'bilal.sabir'):?>
                        <th><center>Print</th>
                        <th><center>Edit</th>
                        <th><center>Change Pwd</th>
                        <th><center>Upgrade</th>
    <?php endif;?>


                      </tr>
                    </thead>
                    <tbody>
                    <?php $sr=0;?>
                      <?php foreach ($freemembers as $member): ?>
<tr><td><center><?php echo $sr+=1; ?></td><td><center><?php echo $member->first_name; ?></td>
<td><center><?php echo $member->mobile; ?></td><td><center><?php echo $member->cnic; ?></td>
<td><center><?php echo $member->email; ?></td>
<td><center><?php echo $member->province; ?></td><td><center><?php echo $member->district; ?></td><td><center><?php echo $member->tehsil; ?></td><td><center><?php echo $member->uc; ?></td>
<?php /*?> <?php if($sessiondata['deletemembers']):?><?php */?>
     <?php if($sessiondata['username'] != 'bilal.sabir'):?>

<td><center><a href="<?php echo base_url() . "index.php/admin/deletefreemember/" . $member->id; ?>">delete</a></td>
<?php endif;?>
  <?php /*?><?php if($sessiondata['data_modification'] && $sessiondata['username'] != 'bilal.sabir' ):?><?php */?>
       <?php if($sessiondata['username'] != 'bilal.sabir'):?>
<td><center><a href="<?php echo base_url() . "index.php/admin/printcardfree/" . $member->id; ?>">print</a></td>

<td><center><a href="<?php echo base_url() . "index.php/admin/editprofilefree/" . $member->id; ?>">edit</a></td>
<td><center><a href="<?php echo base_url() . "index.php/admin/changepasswordfree/" . $member->id; ?>">change pwd</a></td>
<td><center><a href="<?php echo base_url() . "index.php/admin/upgradeprofile/" . $member->id; ?>">Upgrade</a></td>
<?php endif;?>
</tr>
<?php endforeach; ?>
                    </tbody>
                  </table>

this is the view of my table free_members

Answer 1

Based upon your View, you have this...

index.php/admin/upgradeprofile/" . $member->id

So if $member->id = 100 the url will be

index.php/admin/upgradeprofile/100

So you are passing a member_id as mentioned in your model code BUT you are not getting it nor are you using the where correctly.

So you need to get a handle on the member_id...

This is ONE Solution/idea...This relies on you providing a correctly validated integer... You need to always Check/validate your inputs...

Controller

function upgradeprofile($member_id) {
    // Need to check that $member_id is an integer
    // To be added by the OP

    $this->load->model('freemember_model');

    // $member_id is passed in via the URL.
    $this->freemember_model->upgrade($member_id); 
}

Model

function upgrade() {
    // Which member do we want to get from the free_members Table?
    $this->db->where('id', $member_id);
    $query = $this->db->get('free_members');
    // Did we get a result? i.e was a valid member id passed in?
    if($query !== false) {
        $row = $query->row();
        $this->db->insert('domestic_members', $row);
    } else {
       // Do nothing or handle the case where an illegal number was used...
    }
}

Note: This code is not tested and is only to be used as a guide. It can be refactored a number of ways but I am trying to keep it simple for now.

So please, do not just copy and paste this without at least "thinking" about what you are trying to do and how the code might be written to achieve it.

The main points here are... 1. You have to use a member id of some kind so you can retrieve the record for the free member so you can insert it into the other table. 2. You are providing this "member id" in the url so you need to extract it. 3. You can do that by either setting it as a parameter in the method being called... Or you can use segments ( look it up ).

So once you have the member id you can fetch the free member entry and insert it into the other table BUT What do you want to happen to the entry in the free members table because that is still going to be there and now you have two copies of the same information... Delete it?

If you need further explanation - ask.

One last point. You should be making sure you fully understand what is you are doing and not just doing it because someone said to or you thought it was a good idea... Have a Good Reason and think of the implications...

Answer 2

I'm not familiar with Codeigniter but MySQL syntax should be this:

INSERT INTO table1 (table1.row1, table1.row2) SELECT table2.row1, table2.row2 FROM table2 WHERE PUT_YOUR_CONDITION_IF_NEEDED

I've found this for Codeigniter.