Delete row(s) with respect to username

Question

i've gone through most of the questions similar to this but none addressed my problem. i have table with four columns : id,username,title and date. i want to delete the entire row(s) associated with a specific username when the user clicks a button (anchor tag). pls, how do i achieve this? heres the code i tried.

php

<?php 
session_start();

$uname = $_SESSION['username'];
$dbconn = mysqli_connect('localhost','root','','notesdb');
if(!$dbconn){
 die("Connection failed:". mysqli_connect_error($dbconn));
}
if($stmt = $dbconn->prepare("DELETE * FROM notes_log where username = ? ")){
 $stmt->bind_param("s",$uname);
 $stmt->execute();
 $stmt->close();
}else{
 echo "ERROR: could not prepare SQL statement.";
}
mysqli_close();
// redirect user after delete is successful
header("Location: index.php");

?>

HTML

<a href="deleteAll.php" onclick="return confirm('Are you sure?')">Delete all</a>

The above code redirected the page but nothing was deleted.

did you check for mysqli_error? What do you see when you do not redirect? — Jeff, Oct 25 '18 at 00:33
What's inside `$SESSION['username']`? (Try to var_dump() it to check the value! Are you sure that it contains the right username? The script show any error message after click in the button? — Elias Soares, Oct 25 '18 at 00:33
@Jeff i just checked and yes i do get the error message, do you have any idea what might be wrong? — henrie, Oct 25 '18 at 00:39
Are you trying to delete the current user or some user in a table (other then the current user) `i want to delete the entire row(s) associated with a specific username` Also one minor thing `echo "ERROR: could not prepare SQL statement.";` and then calling `header` is not going to treat you well. — ArtisticPhoenix, Oct 25 '18 at 00:40
@EliasSoares it does contain the right username, i just verified too — henrie, Oct 25 '18 at 00:41
@Nick the same one i echoed "ERROR: could not prepare SQL statement." — henrie, Oct 25 '18 at 00:43
@henrie You should print `$dbconn->error` to see the SQL error. — Barmar, Oct 25 '18 at 00:45
The asterisk isn't something that DELETE uses, only SELECT can. References: https://dev.mysql.com/doc/refman/8.0/en/delete.html --- https://dev.mysql.com/doc/refman/8.0/en/select.html and an aggregate function such as [`COUNT()`](https://dev.mysql.com/doc/refman/8.0/en/counting-rows.html). — Funk Forty Niner, Oct 25 '18 at 00:48

score 4 · Accepted Answer · edited Oct 25 '18 at 01:25

4

Get rid of the * in the query. The syntax is just:

DELETE FROM notes_log where username = ?

See DELETE Syntax.

In a multi-table DELETE you need to put the table names after DELETE, but a single-table DELETE should have nothing there.

And when an SQL operation fails, you should print the SQL error message, not just could not prepare SQL statement, e.g.

echo "ERROR: could not prepare SQL statement: " . $dbconn->error;

Edit: mysqli_close() requires a database connection as its only argument.

Ref: http://php.net/manual/en/mysqli.close.php

You will need to use mysqli_close($dbconn).

edited Oct 25 '18 at 01:25

Funk Forty Niner

74,372
15
66
132

answered Oct 25 '18 at 00:41

Barmar

669,327
51
454
560

i suspected that but didnt give it a try..it worked, thanks – henrie Oct 25 '18 at 00:45
This is a duplicate. – Funk Forty Niner Oct 25 '18 at 00:46
@FunkFortyNiner it may be but i had a problem and i asked for help – henrie Oct 25 '18 at 00:49
@henrie Barmar's been here for a while and longer than I have. However, that doesn't mean that I too cannot close a question as a duplicate. IMHO, I feel that [my comment](https://stackoverflow.com/questions/52979690/delete-rows-with-respect-to-username/52979768?noredirect=1#comment92865992_52979690) answers it better than the duplicate has. I might even submit of my own in that duplicate since nobody ever mentioned what I have. What do you think Barmar, not a bad idea? ;-) – Funk Forty Niner Oct 25 '18 at 00:51
@FunkFortyNiner Thanks, I've added the `DELETE *` question to my list of canonical dupes. – Barmar Oct 25 '18 at 00:57
@FunkFortyNiner i'm really grateful for your comments and answers but before posting this question, i have been searching for any one similar to my problem for over an hour but didnt see any and just minutes after posting mine, i've been able to resolve it. i'll search better next time – henrie Oct 25 '18 at 00:58
You're welcome Barmar - Thing is though, I just flagged the (1st) dupe for moderation, asking if it can be reopened so I could submit an answer of my own, since and did mention in the flag that it would improve on the question and that it was never mentioned in answers or comments. – Funk Forty Niner Oct 25 '18 at 00:58
You're welcome @henrie Btw, I upvoted your question. However, I may occasionally use this one as a possible duplicate for future questions like this, doesn't mean I won't be using my original. I'm glad that did what you could and that the problem was resolved quickly, *cheers*. – Funk Forty Niner Oct 25 '18 at 01:00
@henrie It's understandable. If you don't already know what the problem is, it can be hard to find a duplicate, because you don't know what to search for. – Barmar Oct 25 '18 at 01:00
And even when you DO know what the problem is, there may not be obvious keywords to search for. – Barmar Oct 25 '18 at 01:01
Thank you @Barmar :-) much appreciated, *cheers*. – Funk Forty Niner Oct 25 '18 at 01:05

Delete row(s) with respect to username

1 Answers1