codeigniter insert into multiple tables

Question

I have a table named testimonial . The table structure is as follows.

enter image description here

But the table dont seem normalized. I would like somthing like.

trip_testimonial

-----------------------------------
id
trip_id
status
-----------------------------------

And another table as

testimonial

   ----------------------------------
    id
    name
    email
    website
    message
    -----------------------------------

But how do I enter the data into them in codeigniter. I have used something like

function add($data){

        $this->db->insert('testimonials', $data);
        if($this->db->affected_rows()>0){
            return TRUE;
        }else{
            return FALSE;
        }
    }

What would be the best approach??

your question is unclear. Do you want to insert into multiple tables? in trip_testimonial what is status? — Muhammad Raheel
– Muhammad Raheel, Commented Feb 1, 2013 at 19:17
the status is just the status for the testimonial which can have either 1 for approved or 0 for pending testimonial — prakashchhetri
– prakashchhetri, Commented Feb 1, 2013 at 19:19
does the trip_testimonial have a foreign key for testimonial ? — Seder
– Seder, Commented Feb 1, 2013 at 19:43

mallix · Accepted Answer · 2013-02-01 21:15:52Z

2

[TRIP]
trip_id
trip_name
trip_desc
....

[TESTIMONIAL]
testimonial_id
trip_id
testim_name
testim_email
......

function add($data){

  $dataTrip = array(
    //fill the array the appropriate data
  );

  //return the last inserted trip_id
  $lastTripId = $this->myModel->myFuncToAddTrip($dataTrip);    

  $dataTestimonial = array(
   'trip_id' => $lastTripId,
   'testim_name' => $data['testim_name'],
   .......
  );

  if($this->myModel->myFuncAddTestimonial($dataTestimonial)){
   //success
  }else{
   //error
  }

}

Use transactions to roll back if at least 1 query fails.

EDIT 1 trip to many testimonials

edited Feb 1, 2013 at 21:15

answered Feb 1, 2013 at 21:05

mallix

1,4392 gold badges22 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Muhammad Raheel · Accepted Answer · 2013-02-01 20:22:29Z

1

Your structure is normalized for one to one relationship just remove the trip_id column

testimonial
-----------------------------------
id
name
email
website
message
date
status

Your add function is fine

function add($data){

    $this->db->insert('testimonials', $data);
    if($this->db->affected_rows()>0){
        return TRUE;
    }else{
        return FALSE;
    }
}

And the data to be inserted

$data['name']       =   'blah';
$data['email']      =   '[email protected]';
$data['website']    =   'website';
$data['message']    =   'message';
$data['date']       =   date('Y-m-d H:i:s');

And set the default value of status to 0 at table creation or with ALTER.
But when you need to insert you can add one more column with different value.

$data['status']     =   1;

And now you can call function add
When selecting you can select with status

SELECT * FROM testimonial WHERE status = 0

OR

SELECT * FROM testimonial WHERE status = 1

UPDATES:

If this is the case than you can do it like this. Add a column to the testimonials table

testimonial
-----------------------------------
id
name
email
website
message
date
status
trip_id

Now select trip id

    SELECT id FROM trip_testimonial WHERE status = 0

And the returned id should be put with testimonial data

$data['name']       =   'blah';
$data['email']      =   '[email protected]';
$data['website']    =   'website';
$data['message']    =   'message';
$data['date']       =   date('Y-m-d H:i:s');
$data['trip_id']    =   $row->id;

And now call your add function

edited Feb 1, 2013 at 20:22

answered Feb 1, 2013 at 19:29

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

2 Comments

prakashchhetri Over a year ago

No, one trip can have multiple testimonials , so trip_id is the foreign key in trip_testimonials

prakashchhetri Over a year ago

thats exactly the same thing that I have posted. But what I want is to Normalize the table as I have stated in the question and insert data into both the table at one in codeigniter]

Collectives™ on Stack Overflow

codeigniter insert into multiple tables

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related