Mysql Automatically insert specific record if doesn't exist or inserts new record if a specific record is update

Question

I'm using php and mysql to develop an application; A little background data about it,it's a login system which stores usernames and passwords and all the works that a login system does. However I have ran into a problem when it comes to deleting users, the function for deleting users works fine but the problem is For example the administrator can delete himself leaving the database with no user. So what I would want to do is when I am on the main page, a function runs to check if a specific username exists

//Example:
Table name: users
columns: username, password
default username and password I want to always remain in table 'users'
username=dames and password=:password 
//End of Example

and if not add the username and password and also if the default username and password has been updated it inserts the default username and password, and and not deleting the updated one. I hope I made it clear enough because id really appreciate some help and suggestions.Thanks

score 1 · Answer 1 · edited May 23 '17 at 11:55

Instead of constantly recreating your admin user, why not just prevent the admin from deleting himself?

if ($user_to_delete == $current_logged_in_user) {
   // Sorry, you can't delete yourself...
}

Or store an array of super-admins in your application's configuration file. Don't permit anyone to delete a super-admin user.

// In the config file...
$super_admins = array("you", "someone_else");

if (in_array($user_to_delete, $super_admins)) {
  // Sorry, you can't delete a super-admin from the UI.
  // Delete him from the application config first...
}

To protect you from yourself a little more, if you only have a need for a single super-admin who can't be deleted, use a constant in the configuration that can't be modified in code:

define('SUPER_ADMIN', 'you');

*Note: You can't build a constant array, but there is a workaround involving serialize() in this question

Going with the super-admin user:define('SUPER_ADMIN', 'you'); sounds very good but how would this be defined in the confi file because my config file only contain my password and username for my database — dames, Aug 25 '12 at 22:05
@dames Whatever format you have in your config file, be it global variables or some config object, or constants, just define one of them as `SUPER_ADMIN` and verify you are not deleting that record. — Michael Berkowski, Aug 26 '12 at 12:29

score 1 · Accepted Answer · edited May 23 '17 at 10:24

If you want to make sure that at least one administrator is always present, only delete the administrator if another one is still present:

DELETE
  u1
FROM
  user u1
LEFT JOIN
  user u2
ON
  u1.access = u2.access
WHERE
  u1.name = "current-user" AND
  ((u1.name != u2.name AND u1.access = "admin") OR (u1.access != "admin"));

The given query above expects a table with a username (name) and an access level (access). It matches the user "current-user", but only if there is another user with the access level "admin". If "current-user" itself has not access level "admin", it is matched anyway.

This way, you just need to create one user with "admin" access level once. More users can be added and deleted, just not the very last user with "admin" access level.

If you want to do everything in SQL, you can create a BEFORE DELETE TRIGGER. There, you simply check for the usernames you do not want to be deleted. See How to abort INSERT operation in MySql trigger? for an example - it is given for INSERT, but works the same for DELETE.

Then again, it really depends on your setup if you want to use configuration files (as suggested) or database logic to customise your application.

Mysql Automatically insert specific record if doesn't exist or inserts new record if a specific record is update

2 Answers2