Member Avatar for jakx12

Ok so what i'd like to know is how to have two where statements in a mysql delete query. I get an error if i do this:

mysql_query("DELETE FROM user_notifications WHERE username='$acc' AND WHERE notification='$note2'")  
or die(mysql_error());

Any help would be much appreciated, I know this is a silly question, but i havnt done any php programming for a couple of months and need to get back into it!

Thanks,

jakx12

  • 5 Contributors
  • 9 Replies
  • 339 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by vinothkumarc

Dani AI

Generated

As noted, the syntax error came from using two WHERE keywords — SQL uses a single WHERE and you combine conditions with AND (or OR). Verify whether the DELETE actually matched any rows; if it ran with no error but deleted nothing, the WHERE clause simply matched zero rows. See the DELETE syntax in the MySQL manual for details. MySQL DELETE syntax. (dev.mysql.com)

Quick, practical debugging steps (in order):

  • Print or log the values you expect in $acc and $note2 (use var_dump or a temporary echo) so you know they contain what you think. This was suggested by .

  • Run the same WHERE as a SELECT first to confirm rows exist:

    $stmt = $pdo->prepare('SELECT COUNT(*) FROM user_notifications WHERE username = :u AND notification = :n');
$stmt->execute([':u'=>$acc, ':n'=>$note2]);
$matches = (int) $stmt->fetchColumn();

  • If matches is zero, inspect whitespace, exact text, character set/collation or data type mismatches (trim strings, check for unintended characters).

For security and reliability: stop concatenating user input into SQL. mysql_* functions are deprecated/removed in modern PHP; migrate to PDO or MySQLi and use prepared statements to avoid SQL injection. The PHP manual and OWASP both recommend prepared statements over manual escaping. PHP ext/mysql deprecation. PDO prepared statements. OWASP SQL Injection Prevention Cheat Sheet. (php.net)

After executing a DELETE with a modern API check how many rows changed — with MySQLi use mysqli->affected_rows, with PDO check rowCount() on the statement — this tells you whether the WHERE matched anything. mysqli_affected_rows documentation. (php.net)

Finally, and raised quoting/escaping points: escaping can help but prepared statements are the safer, cleaner solution and remove most quoting headaches. Also check database permissions and any foreign-key constraints that might block deletes.

Recommended Answers

All 9 Replies

Member Avatar for rickya100

Hi,

Don't think you need the second WHERE, just the AND.

Test it out.

Member Avatar for jakx12

tested it out but, it does not delete anything from the database :( but there is no error :). So whats wrong?

Member Avatar for MDGM

mysql_query("DELETE FROM user_notifications WHERE username='".$acc."' AND notification='".$note2."' "); that should be fine. if its not, try checking your $acc and $note2 variables...

Member Avatar for cwarn23

mysql_query("DELETE FROM user_notifications WHERE username='".$acc."' AND notification='".$note2."' "); that should be fine. if its not, try checking your $acc and $note2 variables...

You also need to escape the variables like this:

mysql_query("DELETE FROM `user_notifications` WHERE `username`='".mysql_real_escape_string($acc)."' AND `notification`='".mysql_real_escape_string($note2)."' ") or die(mysql_error());
Member Avatar for vinothkumarc

To be a better php programmer always use single quotes like this,

mysql_query('DELETE FROM user_notifications WHERE username="'.mysql_real_escape_string($acc).'" AND notification="'.mysql_real_escape_string($note2).'"') 
or die(mysql_error());

bcoz: php parser will search and parse full string while using double quotes. :) - might look simple - but usefull tip(i think so)

Member Avatar for cwarn23

To be a better php programmer always use single quotes like this,
mysql_query('DELETE FROM user_notifications WHERE username="'.mysql_real_escape_string($acc).'" AND notification="'.mysql_real_escape_string($note2).'"')
or die(mysql_error());
bcoz: php parser will search and parse full string while using double quotes. :) - might look simple - but usefull tip(i think so)

From what I have read in the past that is only a c++ thing. In c++ single quotes represent a single character where as with php, single quotes simply just doesn't apply new lines or returns (eg \n\r). Instead it will just display them as their literal characters. Where as double quotes in php allow those returns to be displayed instead of their literal characters. So you may find with your method that your returns will not turn out!!!

Member Avatar for vinothkumarc

while using double quotes.. parser will search for php variable("my name is vinoth" instead use 'my name is vinoth') inside that string...(execution/parsing time will be greater). If we want to display the string with out any php variable('my name is '.$name)... then better to use single quotes...
its truth.... in PHP (I may not know about other languages)

Member Avatar for cwarn23

while using double quotes.. parser will search for php variable("my name is vinoth" instead use 'my name is vinoth') inside that string...(execution/parsing time will be greater). If we want to display the string with out any php variable('my name is '.$name)... then better to use single quotes...
its truth.... in PHP (I may not know about other languages)

I can understand that when assigning a value to a variable/array that would be true but would it be the same with a mysql query. An example is the following:

$var="line1\nline2";
mysql_query('INSERT INTO `table` SET `column`="'.$var.'"');

Would that record the following:

line1
line2

Sort of a tricky thing when mixing two syntaxs together because you are unsure which syntax will be the one that will take place.

Member Avatar for vinothkumarc

ya ofcourse...
before executing the query.... the parser will parse the arguement... after that only the mysql_query function will execute the query...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.