Hello. There are two such tables: sqlfiddle.com/#!9/d307e. Tell me how, when adding new data to main, output the value from the color table (col1, col2, col3 ...) depending on what value of cololr was before? That is: I add data to main

$nnomer = "lux"; const SQL_INSERT_MAIN = ' INSERT INTO main (namenomer, datestart, dateend, color) values (?, ?, ?, ?) '; $addmt = $pdo->prepare(SQL_INSERT_MAIN); $add = $addmt->execute([$nnomer, $datestart, $dateend, $color]);

The value of $ nnomer is equal to lux. In the lux database, the last value was col3, so when you add a new color, it should be equal to col4. As soon as the color in the main table reaches col6, then when you add a new color, it will be equal to col1. And so in a circle. If the value of $ nnomer was equal to vip in me, then the color should have been equal to col2, since the last value of color is col1. This is such a kind of auto_increment ... How to implement this?

  • the color number is one plus the remainder of dividing the number of previous entries (matching by condition) by the number of colors. And this parameter can always be calculated, why should it be stored in the database. And if suddenly your record is deleted, then what happens to the colors of subsequent records - Mike
  • Get the last color, get its id, add 1, loop if necessary. - Akina
  • @Mike, the colors of the previous entries do not change. How were, and remain. A new color just always depends on the previous one. If the last col2, then the real col3, and the next col4. - firebear
  • @Akina, yes I tried, but garbage is one. Immediately you need to fit one sql ... - firebear
  • By the way, what is the "last value" how to find which record was the last. she has some specific date or can have a maximum id or something. And I hope the values ​​of the lux field are strictly consistent col2 id 1 more than col1 - Mike

2 answers 2

First you need to decide on the concept of "last entry". Suppose this is a record with a maximum id .

The next question is how to get this record. I would solve the problem through the trigger

 CREATE TRIGGER tr_main_bi BEFORE INSERT ON main FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR NOT FOUND SET NEW.`color` = col1; SELECT IF(mn.`color` = col6, col1, mn.`color` + 1) FROM main mn WHERE mn.`namenomer` = NEW.`namenomer` AND mn.`id` = (SELECT MAX(mx.`id`) FROM main mx WHERE mx.`namenomer` = NEW.`namenomer`) INTO NEW.`color`; END;

And further

 INSERT INTO main (namenomer, datestart, dateend) values (?, ?, ?)

    maybe in the main table the field color should be done int and made an external key to color.id

    then it will be possible to insert the id from the color table by insertion before insertion

     select case when m.id_color = c.max_id then 1 else m.id_color + 1 end case from main m left join (select max(id) as max_id from color) c on 1 = 1 order by id desc limit 1