I have website on React and want to fetch the data from MySQL when I use the cyrillic or latin characters. For example, I have 2 vehicles state numbers. One is in cyrillic and the second one is in latin.

ВС5575ІС
BC5575IC

MySQL query example:

SELECT * FROM vehicles WHERE state_number = BC5575IC

vechiles table:

Type: MyISAM    
Collation: utf8mb4_general_ci

The problem is that it fetches the result only when I typed this state number in latin or cyrillic characters. It depends how this state number was stored in the table (latin/cyrillic). I need to make it fetch for both cases independently, for example it could be stored as latin but I can type cyrillic characters to get the query result or it could be stored as cyrillic and I can get it by typing latin characters. Any ideas how to do it? Thanks.

CodePudding user response：

Great!

I have fixed this issue by myself. I created 2 methods: convertToLatin and convertToCyrillic. In convertToLatin method I check for latin characters and if input was in cyrillic then I map these characters to the appropriate in latinTable. Additionally, I use isDigit to detect the digits and just add to the state number since I do not need to map them.

In convertToCyrillic method I use the same technique but map these characters to cyrillicTable.

Code:

const isDigit = (stateNumber) => {
  return /^\d $/.test(stateNumber);
}

const convertToLatin = (stateNumber) => {
  const isLatinChars = /[a-z]/i.test(stateNumber);
  let latinStateNumber = stateNumber;

  if (!isLatinChars) {
      const latinTable = ["A", "B", "C", "E", "P", "I", "K", "H", "M", "O", "T", "X", "Y", "a", "b", "c", "e", "p", "i", "k", "h", "m", "o", "t", "x", "y"];
      const cyrillicTable = ["А", "В", "С", "Е", "Р", "І", "К", "Н", "М", "О", "Т", "Х", "У", "а", "в", "с", "е", "р", "і", "к", "н", "м", "о", "т", "х", "у"];
      latinStateNumber = "";

      for (const stateNumChar of stateNumber) {
           if (isDigit(stateNumChar)) {
               latinStateNumber  = stateNumChar;
           } else {
               latinStateNumber  = latinTable[cyrillicTable.indexOf(stateNumChar)];
           }
      }
  }

  return latinStateNumber;
};

const convertToCyrillic = (stateNumber) => {
  const isCyrillicChars = /[а-яієї]/i.test(stateNumber);
  let cyrillicStateNumber = stateNumber;

  if (!isCyrillicChars) {
      const cyrillicTable = ["А", "В", "С", "Е", "Р", "І", "К", "Н", "М", "О", "Т", "Х", "У", "а", "в", "с", "е", "р", "і", "к", "н", "м", "о", "т", "х", "у"];
      const latinTable = ["A", "B", "C", "E", "P", "I", "K", "H", "M", "O", "T", "X", "Y", "a", "b", "c", "e", "p", "i", "k", "h", "m", "o", "t", "x", "y"];
      cyrillicStateNumber = "";

      for (const stateNumChar of stateNumber) {
           if (isDigit(stateNumChar)) {
               cyrillicStateNumber  = stateNumChar;
           } else {
               cyrillicStateNumber  = cyrillicTable[latinTable.indexOf(stateNumChar)];
           }
      }
  }

  return cyrillicStateNumber;
};

And now it fetches the vehicle data by state number in latin or cyrillic characters using OR:

const fetchVehicleByNumber = (req, res) => {
  res.setHeader("Cache-Control", "no-cache, no-store, must-revalidate");
  dbPool.getConnection((dbConnError, dbConnection) => {
    if (dbConnError) {
        return res.json(dbConnError);
    }

    const stateNumber = req.params.stateNumber;
    const dbQuery = "SELECT * FROM vehicles WHERE state_number = ? OR state_number = ?";
    dbConnection.query(dbQuery, [`${convertToLatin(stateNumber)}`, `${convertToCyrillic(stateNumber)}`], (error, data) => {
      dbConnection.release();

      if (error) {
          return res.json(`Error: ${error.errno} - ${error.code}: ${error.sqlMessage}`);
      }

      return res.json(data);
    });
  });
};

It works well. The issue is resolved. Thanks.