Question 21082 in Mysql Question posted in
The official MySQL documentation can be found here.

How to automatically delete empty rows in SQL? – Mysql

I have a SQL databases of users forms, and want to set up some routine or maybe even PHP that will automatically delete empty rows which looks like this:

Column1=(Name=John Surname=Doe) | Column2=(Name=John Surname=) | Column3=(Name=John Surname=Doe)

In this case I would like for database to automatically delete Column2 because surname is missing…
Manually, I could do it like this:

DELETE FROM myTable WHERE Surname='';

Can this be helpful? Never used it.

Can this help?

Or to make some PHP script on load of my website and onload of display of my table (I forgot to mention that I have another web page where I view my table…) which will delete emtpy rows, aka run this code: DELETE FROM myTable WHERE Surname='';

I cannot forbid or put required on my input form because of other reasons…

Any help is apricated, thank You.

Tags:

2

Answers


  1. 0

    First solution:

    In Cpanel/DirectAdmin/… create a Cron Job for PHP.

    Second solution:

    CREATE EVENT IF NOT EXISTS delete_event
ON SCHEDULE EVERY 30 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 24 HOUR
DO
DELETE FROM myTable WHERE Surname='';

    Third solution:

    When running the script related to the table, put the delete command.

    Login or Signup to reply.
  2. 0

    Add trigger. This will remove the inserted row inmediatly. The new keyword is for access the columns in the inserted row

    replace id with the primary key of myTable

    DELIMITER $$
CREATE TRIGGER delete_if_empty
AFTER INSERT ON myTable
FOR EACH ROW
BEGIN
  IF new.Surname IS NULL OR new.Surname = '' THEN
    DELETE FROM myTable WHERE id = new.id;
  END IF;
END$$
DELIMITER ;

    Another solution could be adding NOT NULL constraint on surname column to prevent insert row like this. Depending on how you handle result of the insert query, probably you will need to add some lines to your code add to avoid breaking of the posterior logic

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search