1

I have 3 table with structure like this,

employee > id (PK), name

family > family_id (PK), id (employee PK)

father > father_id (PK), family_id (family PK), father_name

I know I can get the last inserted id using mysql_insert_id() or LAST_INSERT_ID, and I wanted to get the ID and insert it to each table like this:

INSERT INTO employee VALUES('', '<value>')
//
//
INSERT INTO family VALUES('', '".mysql_insert_id()."')
// id from employee PK
//
INSERT INTO father VALUES('', '".mysql_insert_id()"', '')
// id from family PK

Am I doing the right thing? If yes, is there any better way to do the exact same thing?

Thank you. :)

Xtrader
  • 141
  • 3
  • 10
  • 9
    [Please, stop using mysql_* functions](http://stackoverflow.com/q/12859942/1238019) in new code, they are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Instead of, have a look on [prepared statements](http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html), and use [Mysqli](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php). – zessx Oct 30 '13 at 11:44
  • I'm currently using mysqli right now, but since I still learning about it, I use mysql_functions which I know the most in this question just as an example, thank you for reminding me anyway. :) – Xtrader Oct 30 '13 at 14:29

1 Answers1

0

Yes, that's the right way to do it (given the caveats about the mysql_ fns). But you've quoted an integer value in the 2nd and 3rd examples - which is a bad idea.

The disadvantage to this approach is that it requires multiple round trips to the database even without server side parsing of prepared statements (send query, retrieve results, send request for insert id, retrieve insert id X 3) which can be a performance bottleneck.

If this is the case, then packaging it up as a Mysql function permits the operation to competed as a single call to the DBMS (using a function rather than a procedure, since the former can return the insert id).

symcbean
  • 47,736
  • 6
  • 59
  • 94
  • So I need to convert the value into string using `settype()` before inserting it? – Xtrader Oct 30 '13 at 14:24
  • No - the data type on the table should be an integer - if it's a char/varchar etc then you're going to have major performance problems, and potentially data integrity problems. – symcbean Oct 30 '13 at 14:59