2

I'll jump straight into it. Using php I'm having a problem deleting a record from the database if i use a variable as the value. The line of code below works perfectly

mysqli_query($con,"DELETE FROM highScores WHERE Name='David'");

But the name of the user will change, therefore I need to declare it as a variable. I've tried all kinds of variations but nothing seems to work. My latest failed attempt was the code below, which is the way i declare a varible when i'm inserting.

mysqli_query($con,"DELETE FROM highScores WHERE Name='{$name}'");
Improve this question
9
  • Did you try without the curly brackets? That should work. "mysqli_query($con,"DELETE FROM highScores WHERE Name='$name'");" But ideally you should 'sanitize' any variables you use in sql queries, especially if they come from public user input Commented Jun 22, 2013 at 20:06
  • Does your query return an error? Commented Jun 22, 2013 at 20:06
  • Rather than using '{$name}' use directly '$name' Commented Jun 22, 2013 at 20:08
  • @MrVimes I tried that with no success. I also tried to concatinate with periods, that also didn't work. Commented Jun 22, 2013 at 20:09
  • 1
    @MrVimes Thanks a lot! this fixed it. Your comment "Maybe $name doesn't contain what you think it does then" made me check it out. I was declaring the $name below my DELETE. I didn't think it mattered as the value is cookie driven, but it seems that it did matter. If you put it in the answer i'll mark it as correct. Commented Jun 22, 2013 at 20:15

4 Answers 4

Reset to default
3

In situations like this it is good to check that variables actually contain something you expect it to. And I find also that echoing entire query strings is a good way to find out why a query isn''t working.

$sqlquery = "DELETE FROM highScores WHERE Name='{$name}'";

// have a look at the query...

echo "<pre>$sqlquery</pre>";

// use it...

mysqli_query($conn,$sqlquery);

I should warn you that if $name comes from somewhere untrusted, such as a publicly viewable html form, then it needs to be made 'safe' before using it in a query. Look into 'prepared statements'. Once you know your code is correctly populating your variable, make sure it is made safe before putting it in your query.

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

Comments

0

I'm not sure if.. {$variable} is valid in a query.

The way I insert a variable into a query, is called concentration.

mysqli_query($con,"DELETE FROM highScores WHERE Name='" . $name . "'");

The period adds 2 strings together.

However, what you're trying to do is vulnerable to SQL injection. If I were you, I'd be careful on what could be inside $name.

EDIT: My mistake, I'm used to a class that inserts the quotes for me.

Improve this answer

3 Comments

{$variable} is perfectly valid, and your suggestion is not good...as name needs single quotes around it
It is not a query but PHP string.
For the record this works, i checked it out while debugging a similar problem. If doing it this way is best practice, then I don't know, but it definitely works.
0

Try this to get it running:

mysqli_query($con,"DELETE FROM highScores WHERE Name='".$name."'");

Make sure $name is a proper formed string like string(5) David, otherwise it might not lead to the desired results or may even break your query completely. You can make sure of this if you put a mysqli_real_escape_string like this

$name = mysqli_real_escape_string($con,$name);

before you execute the query

Improve this answer

4 Comments

In PHP you don't need to break out of a string to insert a variable, you can do Name='$name' and it should work.
I know, but it better highlights the code if you break them out anyway!
I want to say 'ok' but stackoverflow won't allow my comment to be that short :D
Please var_dump($name); and post the result.
0

I landed here while searching for solutions to the same problem, but just discovered my database user didn't have delete privileges. I had earlier removed this privilege for security reasons.

Improve this answer

Comments

Your Answer

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