1

I have an old registration system, and a new one. (two tables).
The old system holds an old category, and the new one holds some new categories that couldn't be implemented into the old system just yet.
So moving everything to one of the systems is not an option at the moment.

My problem:
I need to connect the two tables so the ID's operate as "auto increment" and will follow each other somehow.

Here is a scenario:
// table#1 = old system (car)
// table#2 = new system (tractor, truck, marine)

A new car is registered in table#1 and gets the ID : 13579,
then some other vehicle is registered in table#2 and should get the ID : 13580,
and when a new car is registered in table#1 again, it should get ID : 13581.
and so on...

In my mind it goes something like this:
Right before adding the new vehicle into one of the tables, always do a lookup in the other table and check which one is the last/highest ID and do +1 before setting the new ID...

UPDATE 20120614:
Here is one solution I came up with: 

$qry_tbl1 = mysql_query('SELECT MAX(id) AS tbl1_max_id FROM tbl1');
$get_tbl1 = mysql_fetch_assoc($qry_tbl1); extract($get_tbl1);

$qry_tbl2 = mysql_query('SELECT MAX(id) AS tbl2_max_id FROM tbl2');
$get_tbl2 = mysql_fetch_assoc($qry_tbl2); extract($get_tbl2);

$new_max_id = max(array($tbl1_max_id,$tbl2_max_id)) + 1;

This is not a good solution, but the only one I got to work.
Immediately after the user hits the submit button, I do this and the INSERT a new record. Then, further down the script, based on $new_max_id i update that record with additional data...

Is this an acceptable solution?

ThomasK
  • 2,210
  • 3
  • 26
  • 35
  • 1
    one way would be to query the last ID in table1 and do a +1 as you create an insert statement for the second table using the results from your query of table1. – Eli Jun 12 '12 at 08:19
  • Tag your question with [tag:homework] if that's the case, and post whatever `php`/`sql` you've came with already. – Fabrício Matté Jun 12 '12 at 08:22
  • If many users might add vehicles at the same time, you should group the request reading the previous id and the request setting the new id in a transaction and use a lock to avoid concurrency issues. – Aurel Jun 12 '12 at 08:27
  • It's not homework. And I don't have any php code for this yet because I couldn't figure out where to start... Can I rely on this method: `SELECT MAX(ID) AS last_id FROM other_table` Then do `$new_id = $get['last_id'] + 1;` – ThomasK Jun 12 '12 at 09:15

1 Answers1

1

I think you can use this approach.

Or you can create new table, with such fields:

id (auto increment), table_id, table_name

And save into it table_id - is id for your table entity, table_name - is some attribute, that determines to which table belong this id. During select you have to use this table to determine what id to use. This will avoid id calculations.

Viacheslav Kondratiuk
  • 8,493
  • 9
  • 49
  • 81
  • Create a new table with auto increment and insert into this table first, then get the ID with mysql_insert_id() to get the new ID for which ever table. ? It sounds promising, although I would really like to not have another table... – ThomasK Jun 12 '12 at 09:02
  • No, you need to insert at both tables. At table1 and this relation_table. For example: `table1: id=356(it's auto increment)`, and you need to save at relation_table: id=800(it's auto increment) table_id=356, table_name=table1. And when you make select you have to join tables. – Viacheslav Kondratiuk Jun 12 '12 at 09:42