CODEWITHSUNDEEP
phpmysql
user1015214
user1015214

Reputation: 3081

sql statement to chose the value of a field in the last entry

I have created a mysql database table which has two fields, serial # (primary key, auto increment) and version.

What I want to do is for every insert that I do to this database, set the version value to be 1, then 2, 3, 4, 5 and then back down to 1 again.

I can imagine doing this by pulling out the last entry in the table before doing an insert. How can I do this? Is there an easier way? I am using PHP.

Upvotes: 0

Views: 66

Answers (3)

Ricardo Souza
Ricardo Souza

Reputation: 16466

You can use the built in MySQL Triggers: http://dev.mysql.com/doc/refman/5.0/en/triggers.html

You can create a database trigger to do what you want. The syntax is something like this:

CREATE
TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE
ON `database`.`table`
FOR EACH ROW BEGIN
    -- trigger body
    -- this code is applied to every
    -- inserted/updated/deleted row
END;

Edit:

Triggers are created directly in the database side. You can connect using the MySQL CLI or the PHPMyAdmin to run the query.

Also, a lazy solution may be to use a query similar to what aaaaaa123456789 suggested:

SELECT (
    (SELECT version FROM <table name here> ORDER BY id DESC LIMIT 0, 1) % 5
) + 1 as version;

The modulos (%) operator returns the rest of the division of the two numbers and can be used to limit your increments.

Ex.:

 1 % 5 = 1
 2 % 5 = 2
 3 % 5 = 3
 5 % 5 = 0
 7 % 5 = 2 
10 % 5 = 0

PS.: I haven't tryed this query, 'cause I'm not on my dev machine, but from my mind it works.

Upvotes: 2

Shashika Silva
Shashika Silva

Reputation: 55

$next_increment     = 0; 
$qShowStatus         = "SHOW TABLE STATUS LIKE 'yourtable'"; 
$qShowStatusResult     = mysql_query($qShowStatus) or die ( "Query failed: " . mysql_error() . "<br/>" . $qShowStatus ); 

$row = mysql_fetch_assoc($qShowStatusResult); 
$next_increment = $row['Auto_increment'];

Upvotes: 0

aaaaaa123456789
aaaaaa123456789

Reputation: 5852

The easiest way is, as rcdmk suggested, to write a trigger in the database itself, that updates the version field with the corresponding value.

If a trigger is just out of the window for any reason, then you could just do SELECT version FROM <table name here> ORDER BY id DESC LIMIT 0, 1 to get the last value. In fact, bracketting that (as in, putting it between parentheses) and sending 1 + (query here) to the INSERT query as the insertion value is probably the best way to do it, since it avoids race conditions.

Upvotes: 1

Related Questions