0

I have another question! My CRUD webapp is almost there, I do have a problem writing queries for my database! Database has 3 tables: Department, Location, Personnel.

Relationship is retrieved by using LEFT JOIN:

SELECT p.lastName,
p.firstName,
p.jobTitle,
p.email,
d.name as department,
l.name as location
FROM personnel p
LEFT JOIN department d ON (d.id = p.departmentID)
LEFT JOIN location l ON (l.id = d.locationID)
ORDER BY p.lastName, p.firstName, d.name, l.name

I need to write a delete query which will check for dependencies in database and warn user that if he is trying to delete entry that has dependencies.

I have this:

$query = 'DELETE FROM location WHERE id = ' . $_REQUEST['delete'];

which deletes entry but doesn't check for anything. I have been looking for solution for the past few hours and found nothing! How to write such a query! Can anyone help? Thanks Chi Chi

Casimir et Hippolyte
  • 88,009
  • 5
  • 94
  • 125
Chi Chi Huang
  • 43
  • 1
  • 5
  • 2
    If you want to prevent the deletion of rows that have other rows referencing them, read up on how foreign keys work in your database, and the various related `ON DELETE` options. – Alex Howansky Apr 18 '21 at 17:27
  • 1
    Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. Instead of building queries with string concatenation, always use [**prepared statements**](https://secure.php.net/manual/en/pdo.prepare.php) with [**bound parameters**](https://secure.php.net/manual/en/pdostatement.bindparam.php). See [**this page**](https://phptherightway.com/#databases) and [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) for some good examples. – Alex Howansky Apr 18 '21 at 17:28
  • Where is the JS part named in the title? – Markus Zeller Apr 18 '21 at 17:29

1 Answers1

1

$query = 'DELETE FROM location WHERE id = ' . $_REQUEST['delete'];

Assuming location has the Primary Key. If this is being used as a Foreign Key in any other table, $query should contain an error message provided constraints are enabled on these tables. This is automatic.

If no constraints are enable, then one has to check using a query if this location ID has been used in some other table and don't delete it or delete it.

teknik
  • 153
  • 9