0

Possible Duplicate:
MySQL trigger to update a field to the value of id

Please I have a table with three fields :

Id (auto increment)
GroupById
Text

And I want when inserting a new row : If I left the field groupById blank, it must get by default the same value of the field Id.

Please have you any Idea ? Thanks in advance.

Edit : My code :

mysql_query("INSERT INTO group SET GroupById = (must get the current Id), Text = 'bla bla bla' ");
Community
  • 1
  • 1
Sami El Hilali
  • 981
  • 3
  • 12
  • 21

5 Answers5

1

How about a trigger:

DELIMITER $$
CREATE TRIGGER trg_yourtable
BEFORE INSERT ON yourtable
FOR EACH ROW
BEGIN
  IF NEW.GroupById IS NULL THEN
    SET NEW.GroupById = (
        SELECT AUTO_INCREMENT 
        FROM information_schema.TABLES 
        WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = 'yourtable'
    );
  END IF;
END $$

I'm not sure how safe this is... or what happens when you insert multiple rows from one query, or when two connections attempt to insert at the same time... but it works.

bobwienholt
  • 17,420
  • 3
  • 40
  • 48
1

This simple SQL should do what you want:

INSERT INTO myTable (GroupById, Text) VALUES (NULL, 'your text');
SET @lastID = LAST_INSERT_ID();
UPDATE myTable SET GroupById = @lastID WHERE Id = @lastID;
CodeZombie
  • 5,367
  • 3
  • 30
  • 37
0

Looks like you might need to run 2 queries:

  1. insert into 'table' ('GroupById', 'Text') VALUES ('{group id}', '{sometext}')
  2. update 'table' set GroupById = id where GroupById = null - it mightbe 0 instead of null depending on what you insert in db.

You can always optimize it through indexes, limits, order.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
E_p
  • 3,136
  • 16
  • 28
  • 1
    I would be tempted to use the last insert_id in the WHERE clause because there might be a million rows to find WHERE GroupById IS NULL. – Popnoodles Dec 12 '12 at 23:08
  • If you do it on every insert that it is not an issue, plus i mentioned that query should be optimized – E_p Dec 12 '12 at 23:09
0

Use a stored procedure

  1. Get the MAX id
  2. Add 1 to it and add insert it into both rows

You get the desired result in a single transaction.

Hope this helps.

Kneel-Before-ZOD
  • 4,141
  • 1
  • 24
  • 26
  • MAX(Id) is a bad idea. What if a concurrent INSERT executes by another request and increments the primary key before your second statement executes? – CodeZombie Dec 13 '12 at 00:11
  • If a concurrent insert statement executes before the second statement executes, an error will occur (because of the primary key), which will cause the whole procedure will be rolled back. BTW, your solution is not infallible to the same problem you mentioned. – Kneel-Before-ZOD Dec 13 '12 at 06:58
  • Rollbacks are only possible if you choose a storage engine that supports transactions, which is not always the case with MySQL. My solution will work, because LAST_INSERT_ID() is scoped to the current connection. – CodeZombie Dec 13 '12 at 09:42
0

Use this function | Tested

Example of use:

function get_current_insert_id($table)
{
    $q = "SELECT LAST_INSERT_ID() FROM $table"; 
    return mysql_num_rows(mysql_query($q)) + 1;
}

$txt = "text";
$groupID = '';

if ( empty($groupID) ) { $groupID = get_current_insert_id(test);  } 

$query = mysql_query("INSERT INTO test VALUES ('', '$groupID', '$txt') "); 

if ( $query ) { echo 'Saved using ID:' . $groupID; } else { echo 'Oh noes!' . $query; }
bobthyasian
  • 933
  • 5
  • 17