0

Format of my tables

$sql = "CREATE TABLE tbl_topic (
topic_ID INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
topic_detail VARCHAR(1000) NOT NULL,
user_name VARCHAR(50) NOT NULL,
user_ID INT(11)
)";


$sql = "CREATE TABLE tbl_answer (
answer_ID INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
topic_ID INT(11),
answer_text VARCHAR(1000) NOT NULL,
user_name VARCHAR(50) NOT NULL,
user_ID INT(11)
)";

I'm trying to delete two rows of data where the topic_ID match from two separate tables. My Forum page has a form that posts my topic_ID through.

<td>
    <form method="post" action="delete_topic.php">
        <input type="hidden" name="topicID" value="<?php echo $topic["topic_ID"];?>"/>
        <input type="submit" name = "submit" value = "Remove"/>
    </form>
</td>

I want this information to then go to another file "delete_topic.php" which will run an SQL query to delete the row from both table tbl_answer and tbl_topic.

$top=$_POST["topicID"];
$sql = "DELETE FROM tbl_topic WHERE topic_ID= $top";

I'm unsure how to delete tbl_answer as well as tbl_topic. Any suggestions on how this is done. I have seen methods mainly using JOINS but I'm having a hard time applying it to my situation. I have not used a foreign key either if this would be another method of deleting both data at the same time.

  • Does tbl_answer have a "topic_id" column? Remember we can't see your data structure... if so then `DELETE FROM tbl_answer WHERE topic_ID = ?`. And ... `I have not used a foreign key either` ... well that's not very sensible. If you did then a) you'll get referential integrity, and b) you might be able to employ cascading deletes rather than having to run multiple separate DELETE statements. – ADyson Oct 08 '21 at 13:36
  • **Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli / PDO. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. – ADyson Oct 08 '21 at 13:37
  • https://phpdelusions.net/mysqli and https://phpdelusions.net/pdo also contains good examples of writing safe SQL using mysqli. See also: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. – ADyson Oct 08 '21 at 13:37
  • 2
    Well first of all, maybe you could execute more than _one_ SQL statement in one script, have you thought about that? And second, you could also go and read up on the syntax of the DELETE statement in the documentation, and you would see that you can specify a _list_ of table names to delete from as well. – CBroe Oct 08 '21 at 13:37
  • Does a topic contain multiple answers? if so, I'd suggest deleting all answers with that topic id and after that i'd delete the topic , both in a different query. – Kaede Oct 08 '21 at 13:40
  • Thanks for the update. In that case, refer back to my first comment, or to CBroe's. And actually Namys' comment basically proposes the same thing. – ADyson Oct 08 '21 at 13:47
  • @ADyson I thought I might have just been able to add "DELETE FROM tbl_answer WHERE topic_ID= $top"; underneath however it only runs which ever one is at the bottom. Also yes a topic can contain mutiple answers. –  Oct 08 '21 at 13:53
  • `however it only runs which ever one is at the bottom`...well you do have to use two separate execute/query statements to send each one to the database. Sounds like you're just declaring two strings and having the second one overwrite the first? – ADyson Oct 08 '21 at 13:55
  • $sql = "DELETE FROM tbl_topic WHERE topic_ID= $top"; $sql = "DELETE FROM tbl_answer WHERE topic_ID= $top"; I'm just running it like this. Is that not acceptable. I'm guessing the use of two queries overwrites the other one ? –  Oct 08 '21 at 13:58
  • None of that code runs anything. All you've done there is declare two strings. Strings are just text, they don't _do_ anything. Where do you actually send the SQL to the database? – ADyson Oct 08 '21 at 14:02
  • And p.s. yes when you write $sql = the second time, it overwrites the previous contents of $sql – ADyson Oct 08 '21 at 14:07
  • @ADyson Sorry I'm confused. The SQL statement is getting manually interested into my database. Where it is equal to its self it should then run the sql ? https://www.w3schools.com/php/php_mysql_insert.asp. Thank you for the update. I think I will remake my table with the foreign key and go from their. It seems like better practice. Thank you for your help I'm new to coding. –  Oct 08 '21 at 14:13
  • `Where it is equal to its self it should then run the sql`...no that's not how it works. Executing the SQL always executes the SQL. Whether the execution affects any rows or not is another matter. – ADyson Oct 08 '21 at 14:46
  • But `$sql = ...` just creates some text and puts it in a PHP variable. It doesn't execute the query. So for example if you're using mysqli then as per that example I'd expect your code to go `$sql = ...; mysqli_query($conn, $sql); $sql2 = ...; mysqli_query($conn, $sql2);` so that you declare and then execute _both_ queries. In this example `mysqli_query` is what actually sends the query to the database, which then executes it. (Note I also used two separate variable names $sql and $sql2 in order to make it less brittle overall.) – ADyson Oct 08 '21 at 14:50
  • 1
    Ahh I get it now, thank you for clearing that up I should have realised that a lot sooner. mysqli_query($conn, $sql); is that actual code to execute it while $sql is just holding the data to send. Using two of same variables is of course going to override itself. So if I was just to add mysqli_query($conn, $sql) inbetween it will send both of the queries. Thanks for clearing that up :) @ADyson –  Oct 08 '21 at 15:11
  • `mysqli_query($conn, $sql); is that actual code to execute it while $sql is just holding the data to send`...exactly right – ADyson Oct 08 '21 at 15:17
  • @ADyson I know its a big ask but I have a few other questions relating to this. Would it be possible to open a chat or something. If not thank you for your help anyways. –  Oct 08 '21 at 15:20
  • It would be better to post a new question on Stackoverflow, then the whole community can help – ADyson Oct 08 '21 at 15:35

0 Answers0