What does a DELETE query return?

Germany

Suppose I run a

$sql = "DELETE FROM xyz WHERE a=b";

query against a MySQL database with PHP and use a

$results = mysql_db_query(DBDB, $sql);

statement. What does $results return? Is there any chance to get as a return value the ids of the records which have been deleted?

LordShryku

Nope. You can get the affected rows though, as long as you're not deleting everything from the table

helz

i think $results will return a 0 (or FALSE) if somthing went wrong, and 1 (or TRUE) if everything went ok error-free...

then use affectedrows like lord said to get the number of rows/records that have just been changed (deleted in this case)

Germany

Thanks for your help so far.

Basically what I have to do is DELETE all records in TABLE A which match a specific condition AND (!!!) delete corresponding records in TABLE B.

So TABLE A looks anything like this:

TABLE A

id
name
address
...
...

And TABLE B looks anything like this:

TABLE B

id
moreinfo
moreinfo2
...
...

So if I issue a DELETE FROM 'TABLE A' WHERE condition=true I somehow have to determine which records (ids) have been deleted in order to delete corresponding records in Table B. Or is there a better way of doing it? Joining tables? Your help is more than appreciated...

Germany

Just a thought, would anything like this help and work with MySQL?

DELETE FROM table_b WHERE id NOT IN (SELECT id FROM table_a)

LordShryku

Depends on your mysql version. Probably best to just do a select query first.

$sql = "SELECT id
          FROM table_a
         WHERE condition = true";
$res = mysql_query($sql);
if(mysql_num_rows($res) > 0) {
   $ids = array();
   while($row = mysql_fetch_array($res)) {
      $ids[] = $row['id'];
   }
   // Now you have an array of IDs to delete
   $sql = "DELETE FROM table_a 
                 WHERE id IN ('".implode("','",$ids)."')";
   if(mysql_query($sql)) {
      $sql = "DELETE FROM table_b
                    WHERE id IN ('".implode("','",$ids)."')";
      if(!mysql_query($sql)) {
         die("Could not delete from table b");
      }
   }
   else {
      die("Could not delete from table a");
   }
}
else {
   echo "No results returned";
}

Germany

That is almost exactly what I have in my code today. I just thought there would be a "smarter" way of doing it. I do not like kind of redundant queries and I think the SELECT and DELETE commands use kind of the exact same condition so... doing the same thing twice... must be a better way... :-)

helz

would this work?

$sql = "DELETE FROM table_a.table_b 
                 WHERE id IN ('".implode("','",$ids)."')";

i think its either a . or use AND

LordShryku

Like I said, depends on your mysql version. Mysql wasn't quite smart enough to add subqueries and joined deletes in their earlier versions. I think their newest release supports them though.

helz

oo, when it comes to mysql queries I am still only using basic commands, is there a good place to go to, to learn them?
I remember looking at mysql.com but they aren't very helpful when it comes to learning something new

LordShryku

I don't really know....
MySQL is a completely different beast from other DB's. They seem to make their own rules on a lot of things. W3Schools has a SQL tutorial that might help you. The best advice I could ever give you is to stop using MySQL 😃
They really don't follow the SQL ANSI standards for a lot of things, so stuff you do on MySQL, you can't do on many other DB's.

helz

ah interesting..
I took the W3Schools sql test, got 95% 19/20
question i got wrong:

With SQL, how do you select all the records from a table named "Persons" where the "FirstName" starts with an "a" ?

You answered: SELECT * FROM Persons WHERE FirstName LIKE '%a'

wasn't sure about that question, you know what it is supposed to be?

LordShryku

SELECT * FROM Persons WHERE FirstName LIKE 'a%'
Wildcard was in the wrong place

helz

ah that was choice A
so if % is a wildcard for any number of letters, is there one that only counts for one letter, in some its like a ? for one char and a * for multiple

LordShryku

? or _ usually

helz

ah, great stuff, i guess its true where you learn something every day

ta Lord