There is a certain card file of people:

enter image description here

The essence of the card file is to store such cards here, each of which contains three names initially. Each card is numbered. The surnames in the cards and the card file are generally arranged in the sort order by alphabet.

In MySQL, the table storing this file looks like this:

  • id
  • card_number (card number)
  • position (position of the last name in the card)
  • surname (last name)

Task: add a new surname to the card file with saving the positions by the surname that are already there, but in the nearest sorting card.

For example, we need to add the surname "Ivaschenko". The surname Ivashchenko should be after Ivanov and before Ilitayev, according to the sorting rules, but since we cannot change the order of the names that already exist in the card, we should just add it to the end of card No. 1, after the last name "Ilitaev"

As a result, I need to somehow determine the number of the card in which I am going to add this name for a given last name (for example, for Ivashchenko No. 1).

  • one
    So far, only the thought has come to transfer this business to the PHP side, namely, to select a cut of records (surnames) by the first letter of the added last name, write it into the php array, add a new last name to the end of the array, use PHP to sort the array, find it in It has an added last name, find the element of the array that goes before the added last name, and determine its ID, card_number and position, and that's it. Crutchy, long, but it should work ... - Enshtein
  • And Ирсеньева where to add to the first or second. And initially there are cards on all letters. What if you didn’t initially have 3 surnames for a rare letter, will you get a card and come up with a third surname for it from the ceiling? - Mike
  • Irseneva should be added to card number 2, after Iulov, because you cannot touch the surnames already in the cards. Initially there are no cards for all the names. If, for example, we need to add the name of Ivashchenko, and there are no names starting with AND in the card index, then we will go alphabetically upwards, i.e. to the letter A before the first letter found and the same mechanism will work. New cards can not start. - Enshtein
  • one
    And why not in card number 1? according to the PHP algorithm you provided, you will find exactly the record from card 1, since on sorting it in front of Irsenev - Mike
  • one
    Why alphabetically you Irsenyev after Isanbayev. In the version of the Russian alphabet known to me, the letter 'P' comes before the letter 'C'. Do you know any other alphabet? - Mike

1 answer 1

 select * from card where surname=( select max(surname) from card where surname<'Иващенко' )

Will give the same record that you would give PHP algorithm. If you need to determine some "distance" between the last names - in order to find the really "closest" one, then you will have to sweat in PHP as well. But this is basically the same thing to be solved. For example, you can get a record similar to the SQL query that comes after the inserted one and between the two already on php to calculate which is “closer”

If the above query did not return any records, then you have given the last name that should be first in the card index. Those. Such a record must be accurately added to the first card file.

  • 2
    Alternatively: SELECT * FROM card WHERE surname <'Ivaschenko' ORDER BY surname DESC LIMIT 1 - Enshtein
  • 3
    In principle, yes, I think equally in speed - Mike