DELETE and UPDATE with mysql and php

Question

I am building gallery with categories and having problem with this subject.

Only 2 tables. 1st table has categories

Second table has photos data. Second table has category_id from 1st table. Pretty simple...

now when i delete the category i would like to update 2nd table with category_id = NULL ot = ''. Is it possible ?

I tried using triggers but no success yet. getting syntax errors...

$sql = "
CREATE TRIGGER update_gal_items AFTER DELETE ON gallery_category
FOR EACH ROW
BEGIN
  UPDATE gallery_photos SET photo_category='' WHERE photo_category=$id;
END
";

Any help please ?

Use foreign key constraint on category_id with UPDATE to NULL — Madhur Bhaiya, Sep 16 '18 at 10:59
My problem was using multiple queries one after another. thats why i tried triggers. there is no problem to update or delete separatly. i want a DELETE action trigger UPDATE in another TABLE — Pavel Nasonov, Sep 16 '18 at 11:00
Anyways, your trigger syntax is wrong. You cant have php variables inside the trigger. Trigger is defined at mysql server side itself — Madhur Bhaiya, Sep 16 '18 at 11:02
In a delete trigger I would expect to see reference to OLD. values. — P.Salmon, Sep 16 '18 at 11:03
Thats another problem i guess. How do i let the trigger now what id should it use to remove the row i need ? — Pavel Nasonov, Sep 16 '18 at 11:03
'Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger' - https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html clearly there are no NEW. values in a delete trigger. — P.Salmon, Sep 16 '18 at 11:05
`$sql = " DELIMITER $$ CREATE TRIGGER update_gal_items AFTER DELETE ON gallery_category FOR EACH ROW BEGIN UPDATE gallery_photos SET photo_category=NULL WHERE photo_category=OLD.category_id; END$$ DELIMITER ; "; ` still getting syntax error. string(226) "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ CREATE TRIGGER update_gal_items AFTER DELETE ON gallery_category ' at line 1" — Pavel Nasonov, Sep 16 '18 at 11:09
cause i want to give the option to reuse the same photos for diffrent category. — Pavel Nasonov, Sep 16 '18 at 11:12
@PavelNasonov check https://stackoverflow.com/a/52353451/2469308 — Madhur Bhaiya, Sep 16 '18 at 11:15

score 2 · Answer 1 · answered Sep 16 '18 at 11:04

2

You should rather apply Foreign key constraint:

ALTER TABLE gallery_photos
ADD FOREIGN KEY (photo_category) REFERENCES gallery_category(category_id)
ON DELETE SET NULL

Note that I am assuming category_id as field name in your parent table (gallery_category).

Now, if you still want to use Triggers, then define the following trigger on your gallery_category table:

DELIMITER //
DROP TRIGGER IF EXISTS gallery_category_delete_trigger //

CREATE DEFINER=`root`@`localhost` TRIGGER gallery_category_delete_trigger 
    AFTER DELETE ON gallery_category  
    FOR EACH ROW 
BEGIN

      UPDATE gallery_photos 
         SET photo_category = NULL
      WHERE photo_category = OLD.category_id;

END //
DELIMITER ;

answered Sep 16 '18 at 11:04

Madhur Bhaiya

28,155
10
49
57

@PavelNasonov yes it is a username to DB. But I generally keep it to `root@localhost`, to avoid issues when other sessions/users trying to access DB and value does not auto update – Madhur Bhaiya Sep 16 '18 at 11:16
well there is kinda movement getting new error in phpmyadmin `You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)` – Pavel Nasonov Sep 16 '18 at 11:22
@PavelNasonov it seems like you have permission issues! You most likely dont have permissions to create new triggers – Madhur Bhaiya Sep 16 '18 at 11:24
it actually doesnt matter if to use trigger or something else. is there any other way it can be achived ? – Pavel Nasonov Sep 16 '18 at 11:25
1

@PavelNasonov Three ways that I can think of: Foreign keys, Triggers, and making changes to your application code. First two requires editing permissions at MySQL server side. Last one requires you to code the following steps: Delete the `category_id` from parent table. Update to set null for all the rows matching the `category_id` in the child table. – Madhur Bhaiya Sep 16 '18 at 11:29
i actually wated the 3rd option when i started :) after google search i ended up trying to appy triggers. – Pavel Nasonov Sep 16 '18 at 11:35
@PavelNasonov SO, where are you stuck in your 3rd option ? – Madhur Bhaiya Sep 16 '18 at 11:36

Pavel Nasonov · Answer 2 · 2018-09-16T12:40:40.597

0

Well running to diffrent queries solves the problem...

global $conn;
if( isset( $_POST['cat_id'] ) ) {
  $id = (int)$_POST['cat_id'];
}
$sql = "UPDATE `gallery_photos` SET `photo_category` = '' WHERE `photo_category` = ?";
$stmt1 = $conn->prepare( $sql );
$stmt1->bind_param('i', $id);
$stmt1->execute();
if (! $stmt1) {
       $this->log->error ( "Mysqli prepare error: " . mysqli_error ( $conn ) );
       throw new MysqliExecuteException ( "Mysqli error: " . mysqli_error ( $conn ) );
 }

$sql_n = "DELETE FROM `gallery_category` WHERE `category_id` = '$id'";
$stmt = $conn->prepare($sql_n);
$stmt->execute();
if (! $stmt) {
       $this->log->error ( "Mysqli prepare error: " . mysqli_error ( $conn ) );
       throw new MysqliExecuteException ( "Mysqli error: " . mysqli_error ( $conn ) );
 }

Thanks everyone for their time. :)

edited Sep 16 '18 at 12:40

answered Sep 16 '18 at 11:38

Pavel Nasonov

61
5

1

Please parameterize these queries. This is very much open to SQL injection. If you cant move to parametrized queries, atleast **use single quotes** around `$id` and `(int)` typecasting for the `$id` (assuming it to be an integer field). Read here: https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string – Madhur Bhaiya Sep 16 '18 at 11:43
so i guess your answer does not match your question !!! what you wanted was to delete the rows of table2 that referred to the table1 row you deleted ! – YvesLeBorg Sep 16 '18 at 13:31
@YvesLeBorg no i wanter to update rows in the A after i delete a row in table B – Pavel Nasonov Sep 16 '18 at 18:00

DELETE and UPDATE with mysql and php

2 Answers2