get primary key to put as foreign key in other table

Question

I have this code, what I want to do is to get the primary key lastly inserted into the user table and then put that into the foreign key of the library table. I don't know how to do it with $request or what to do here?

public function register($request = array()) {
    $data = array(
        'user_name' => $request['username'],
        'password' => $request['password'],
        'email' => ($request['email']) 
    );
    $data1 = array(
        'user_id' => '5',// this is where I have to put the primary key from last table
        'library_name' => $request['lib_name']
    );

    $this->model->insert('user', $data);
    $this->model->insert('library', $data1);
    $this->redirect('uploadSongs.php');
}

Do you have a mysql storage engine that supports foreign keys? (InnoDB?) — Tobias, Jul 11 '12 at 12:02

score 1 · Answer 1 · answered Jul 11 '12 at 12:06

1

Use mysql_insert_id()

-- Returns the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement.

answered Jul 11 '12 at 12:06

manurajhada

5,284
3
24
43

shall it work if I have indexed the foreign key of second table and applied cascade on delete and update? – Saqib Jul 11 '12 at 12:17
read about it dear.. see this link for example.. http://www.w3schools.com/php/func_mysql_insert_id.asp – manurajhada Jul 11 '12 at 12:23
thanks but m doing the same thing, still it's generating '0' result! – Saqib Jul 11 '12 at 12:26
ok ok.. this is what you are looking for.. http://stackoverflow.com/questions/8243503/mysql-insert-id-returns-0 – manurajhada Jul 11 '12 at 12:29

score 0 · Accepted Answer · answered Jul 11 '12 at 12:04

0

You can do like this

public function register($request = array()) {
    $data = array(
        'user_name' => $request['username'],
        'password' => $request['password'],
        'email' => ($request['email']) 
    );

    $this->model->insert('user', $data);
   $user_id = $this->model->select('user', $data); //select user_id from db 
   $data1 = array(
        'user_id' => $user_id ,
        'library_name' => $request['lib_name']
    );



    $this->model->insert('library', $data1);
    $this->redirect('uploadSongs.php');
}

answered Jul 11 '12 at 12:04

Poonam

4,591
1
15
20

this is not the optimized way of doing that. there is a function of mysql_insert_id() why not utillize that – Faizan Ali Jul 11 '12 at 12:13
thanks poonam your idea was helpful, just a little change I just had to get ID from mysql_insert_id() as Faizan said than using select query... thanks u all, after all its working :) – Saqib Jul 11 '12 at 15:27

score 0 · Answer 3 · answered Jul 11 '12 at 12:15

0

you can do it like this

public function register($request = array()) {
$data = array(
    'user_name' => $request['username'],
    'password' => $request['password'],
    'email' => ($request['email']) 
);
$data1 = array(
    'library_name' => $request['lib_name']
);

$this->model->insert('user', $data);
$user_id = mysql_insert_id();
$data1['userid'] = $user_id;
$this->model->insert('library', $data1);
$this->redirect('uploadSongs.php');

} If you are using any frameworks. then they have the helper functions to get last insert id, you should use them

answered Jul 11 '12 at 12:15

Faizan Ali

509
12
35

what do i have to do with $user_id = mysql_inser_id(); here while I need that in about at $data1 = array('user_id' => //here) – Saqib Jul 11 '12 at 12:23
1

well you need to build the array before it is being sent to insert.I am dynamizally adding the element to array you see after where I get userid – Faizan Ali Jul 11 '12 at 12:25

score 0 · Answer 4 · answered Jul 11 '12 at 15:26

Thanks guys, it worked all i needed to do was just to change the logic, I was using it stupidly and didn't see where to place the insert query so Its working now all i had to do was just to change the place of the insert query as given

public function register($request = array()) {
$data = array(
    'user_name' => $request['username'],
    'password' => $request['password'],
    'email' => ($request['email']) 
);

$this->model->insert('user', $data);
$data1 = array(
    'user_id' => mysql_insert_id(),// now it works as insert has been done above .... 
    'library_name' => $request['lib_name']
);
$this->model->insert('library', $data1);
$this->redirect('uploadSongs.php');

get primary key to put as foreign key in other table

4 Answers4