0

I have made a table with rows of information in it, next to the rows I have included a 'delete' button, which I was hoping would delete that specific row of information from the SQL. However, I keep getting errors that it failed to connect as I do not know how to give the row a specific number so the SQL knows which row to delete. Here is the code for the table:

However, it does not work, the page doesnt refresh or delete from SQL.

I hope someone can help me sort out my issue. Thank you,

Improve this question
4
  • 1
    Please have a look at php.net/manual/en/mysqli.multi-query.php Commented Feb 16, 2018 at 14:50
  • One problem with mysqli's multi query function is that you can't use prepared statements annymore but you need to follow back to mysqli_real_escape_string() function don't forget to use mysqli_set_charset() first to get atleast some protection against SQL injections. Commented Feb 16, 2018 at 14:53
  • Break your queries up into multiple strings and then execute them one at a time on the same connection. The connection is where the bulk of the overhead comes from, executing three separate statements will take no more time than executing the three in a single string. IIRC, the DB connector is doing exactly that in the back anyway. And you should also invest time into learning PDO. Mysqli may not be deprecated, but it isn't as full featured as pdo, and if a user is clicking a button to delete items, then you had better be sanitizing your input. Commented Feb 16, 2018 at 14:54
  • plus to add to @user1119648 's comment.. PDO supports better exception handling (out off the box you need to use setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); tho ) vs MySQLi.. And PDO can connect to multiple databases with the same code if you write ANSI SQL your SQL statements should run on ANSI complaint databases with the same code. Commented Feb 16, 2018 at 15:03

1 Answer 1

Reset to default
1

Change this part of your code:

// sql to delete a record
$sql = "DELETE FROM `system` WHERE system.id=5;
DELETE FROM `booking` WHERE booking.id=5;
DELETE FROM `details` WHERE details.id=5;";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $conn->error;
}

to:

$sqls = array("DELETE FROM `system` WHERE system.id=5;",
              "DELETE FROM `booking` WHERE booking.id=5;",
              "DELETE FROM `details` WHERE details.id=5;");

foreach ($sqls as $sql) {
    if ($conn->query($sql) === TRUE) {
        echo "Record deleted successfully";
    } else {
        echo "Error deleting record: " . $conn->error;
    }
}

But i really think the best approach is to modify your database schema and enable cascade on delete using foreign keys. (https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html)

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Hello, that worked, thank you! Do you have any idea how I might get the PHP to delete the selected row that the user chose, as I tried 'system.id=5, details.id=5...' on the row with id=5 to see if it would work. would i have to do 'system.id=$system.id'??
Just need to concatenate like: "DELETE FROM system WHERE system.id=" . $id . ";"
Sure, you need to associate a id into your html construction like: <a href='delete.php?id=<?php echo $id; ?>'><button>Delete Booking</button></a>
thank you so much for all your help, i just dont understand why my php is not picking up the row of data to delete it - aaahhh!
You need to pass it as parameter in the href='delete.inc.php', this part is where you need to change you aren´t passing any id to the delete.php file. Also in the delete.php file you need to get this property with $_GET['id'].
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.