PHP MySql query to delete rows doesn't work.

Question

I have this php script:

foreach (get_all_topics () as $topic_id => $topic_info) {
   $result = mysql_query("DELETE FROM marks 
                           WHERE user_id = $user_id 
                            AND topic_id = $topic_id", $db);
    echo "DELETE FROM marks 
           WHERE user_id = $user_id 
             AND topic_id = $topic_id : " . mysql_error() . " : " . mysql_affected_rows();
}

I have set a valid $user_id and get_all_topics() gives valid topic info as printed by echo. However it always returns false and I have a data with user_id = 1 AND topic_id = 1 (and it also gets deleted if I copy and paste echoed string in interactive mysql prompt - so no spelling mistakes!).

NOTE: Other functions that Insert or Update the table works just fine. I'm learning php - mysql and this is the first time I'm deleting something from database. Can anyone please suggest what is the issue with this script? Or can there be any permissions problem with this?

Regards, Mihir Gokani

EDIT: I've changed the code above to

$all_topics = get_all_topics();

foreach ($all_topics as $topic_id => $topic_info) {
   $result = mysql_query("DELETE FROM marks 
                           WHERE user_id = $user_id 
                            AND topic_id = $topic_id", $db);
    echo "DELETE FROM marks 
           WHERE user_id = $user_id 
             AND topic_id = $topic_id : " . mysql_error() . " : " . mysql_affected_rows();
}

and it worked! Anyone having similar problem can try assigning return value to some temp variable and then use that in the foreach loop. Don't know why it works :)

Thanks, Mihir Gokani

Answer 1

Try putting single quotes around $user_id and $topic_id so your code will be

mysql_query( "DELETE FROM marks WHERE user_id='$user_id' AND topic_id='$topic_id'", $db );

Answer 2

I like to build a query string:

$myQuery = "DELETE FROM marks WHERE user_id= " . $user_id . "AND topic_id=" . $topic_id;
mysql_query($myQuery);

Answer 3

1. Delete all these rows once using in (id1, id2, id3)
2. You have not specified database in query (may be you have selected database before) - use databasename.tablename.
3. It depend's what your mysql_error returned since false means that there is an error.
4. May be errors somehow suspended so look in error logs.

PHP Mysql Query Manual page