Good day. In general, I have such a need: There is a table:

id, operator, stantion, data, errors, file

The bottom line is what - the operator (and there are several of them) adds a file, and you need to put it all into a table. Each file from a specific station is a column stantion, and the whole snag is that you need to put in the table UNIQUE stantion values ​​FOR THE DAY. Ie if for some reason the operator has unloaded the report on the same station twice a day, we do not write this to the database for the second time. Tried to do as:

INSERT INTO import_file (`operator`,`stantion`,`data`,`errors`,`file`) VALUES ('$operator', '$stantion', '$data') WHERE ((SELECT COUNT(*) FROM import_file WHERE `data`='$data' AND `stantion`='$stantion' AND `operator`='$operator') = 0)

Naturally, nothing good has been received except for the abuse of MySQL. Maybe I am doing something wrong, maybe I reinvent the wheel, and for this there is some kind of standardized solution?

  • And why not to make validation at level pkhp? Check if there is already a record - if so, then some message will be returned to the user, if not, then write to the database. - Moonvvell

2 answers 2

You can like this:

 INSERT INTO import_file (`operator`,`stantion`,`data`,`errors`,`file`) select '$operator', '$stantion', '$data' from (select 1) X WHERE not exists(SELECT 1 FROM import_file WHERE `data`='$data' AND `stantion`='$stantion' AND `operator`='$operator' )

Or make a unique index in the data, stantion, operator fields and trying to insert a double into the table will return an error, which you can analyze and understand what the record was or use the on duplicate key update construction.

BUT, you substitute the values ​​of variables directly into the request, which leads to a lot of problems and vulnerabilities (up to half of all hacking sites on the Internet occur for this very reason). Therefore, never a single variable directly in the query text do not substitute. Instead, use prepared queries and value binding ( bind_param ).

    In general:

     SELECT COUNT(*) AS total FROM import_file WHERE stantion = $stantion; if(!total){ INSERT ... }else{ UPDATE ... }

    Naturally, as @Moonvvell noted, this is in the model file (or where you send requests).

    • Well, it's all great, just 2 requests - kakbe, but I wanted conciseness ... Well, most likely you will have to do it - first you have to look at the records in the database, and then add - Crasher
    • well kakbe yes :) - Kirill Korushkin