Description of the problem: there are 2 tables tabel_home and table_users and they are connected 1 to many and when adding a new entry to the table tabel_home you have to check tabel_home there is an entry in the tabel_users , if there is to receive its id if there is no add and receive id .

Now I use php to solve this:

 function GetUserId($user_name, $mysqli) { $sql_select = "SELECT id FROM tabel_users WHERE user = $user_name;"; if($result_id = $mysqli->query($sql_select)) { if($result_id != Null) { return $result_id->fetch_assoc()['id']; } else { $sql_insert = "INSERT INTO tabel_users (user) VALUE ($user_name);"; $mysqli->query($sql_insert); return $mysqli->insert_id; } } else { return False; } }

But it is interesting to have a faster and more convenient way of getting or adding a record and getting its id and preferably using SQL .

  • If an ID should be returned, one way or another, then it would never be done. You can make a tricky request, but two are simpler - Aleksey Shimansky
  • @ Alexey Shimansky, this is how the id is returned now, but with the help of php, and for this I am sending 2 SQL queries. But I wonder how this can be implemented using SQL or optimizing the existing code. - users
  • I repeat once again. SQL will have to write a tricky query that it is easier to leave everything as it is - Alexey Shimansky
  • @ Alexey Shimansky, that is, to hammer on changes for the sake of changes? I do not know why, but this code looks like a crutch that needs to be replaced, apparently I'm wrong. - users
  • The code looks like this because of php. but with regards to SQL - specifically in your case there will be two queries so and so .... even in one query there will be a second query ....... you can only simplify trying to insert through INSERT IGNORE and then make a SELECT something like that what is written in @Akina in UPD - Alexey Shimansky

1 answer 1

The simplest solution is to do the insertion without any checks. If there is no such record, it will be inserted. If there is, then the restriction of the unique index tabel_users (user) (I hope that there is one? Otherwise, everything described in the question does not make sense) will not allow to insert - but you just need to ignore this error. After that - no matter which of the options took place - the entry in the table IS. So you can safely do SELECT id for further use. In other words:

 function GetUserId($user_name, $mysqli) { $sql_insert = "INSERT IGNORE INTO tabel_users (user) VALUE ($user_name);"; $mysqli->query($sql_insert); $sql_select = "SELECT id FROM tabel_users WHERE user = $user_name;"; return $mysqli->query($sql_select)); }

UPD:

Or immediately

 function GetUserId($user_name, $mysqli) { $sql = "INSERT IGNORE INTO tabel_users (user) VALUE ($user_name); SELECT id FROM tabel_users WHERE user = $user_name;"; return $mysqli->query($sql)); }

Ps. Amend the return to get the second recordset, which is from SELECT. I'm not strong in php ...

  • I would not say that this option is better, here in any case there are 2 queries to the database, when in some cases I just don’t work INSERT (And it reminded Python where try exsept is better if). Yes, a unique index is for the user field. - users
  • As you use Mysqli - nothing prevents to transfer both requests to the server in one command. See UPD: - Akina
  • And yes - in your case when INSERT is executed, not two, but three requests are executed. Because to get $ mysqli-> insert_id; you have hidden query SELECT LAST_INSERT_ID (); - Akina
  • I switched to your version, the time is almost the same, but now it is not such a huge construction, I also added the preparation of the request and verification that everything worked and I get a string, not an object like in your version. - users