Page 1 of 1

mysql command

Posted: Sat Apr 02, 2005 10:42 am
by dannymc1983
does anyone know if there is a mysql command to delte a certain row from a number of tables. for example, the standard mysql command for deleteing say a row called "name" from a table called "table1" would be "delete name from table1".
but is there any command that exists wherby i could delete a row called "name" from say 3 tables, table1, table2 & table3?

Posted: Sat Apr 02, 2005 10:48 am
by feyd

Posted: Sat Apr 02, 2005 12:39 pm
by cbrian
I'm not completely sure what you mean, but maybe something like this?

Code: Select all

mysql_query("DELETE `name` FROM `table1`,`table2`,`table3`");
That is untested, so it might not work.

Multiple Delete statements - Use mysqli_multi_query

Posted: Sat Apr 02, 2005 6:42 pm
by marike
If you have MySQL version 4.1 you can use the PHP's mysqli_multi_query, part of the Improved MySQL Extension, mysqli. You can make your connection as usual, and put your $query in a here doc.

Code: Select all

<?php
$mysqli = new mysqli("localhost", "myuser", "mypass");

if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
 }
$query=<<<_SQL_
Use Test;
DELETE FROM `president` WHERE last_name = 'Bush';
USE World;
DELETE FROM `City` WHERE `ID` = 21 LIMIT 1;
SELECT Name FROM `City` ORDER BY ID LIMIT 20, 5;
_SQL_;
Then you would use mysqli_multi_query to run your query, and a while loop to fetch your results to see if everything worked.

Code: Select all

if ($mysqli->multi_query($query)) {
  do {
     /* store first result set */
     if ($result = $mysqli->store_result()) {
      while ($row = $result->fetch_row()) {
         printf("%s\n", $row[0]);
      }
       $result->close();
    }
     /* print divider */
     if ($mysqli->more_results()) {
       printf("-----------------\n");
    }
  } while ($mysqli->next_result());
 }
/* close connection */
$mysqli->close();
?>
It works. The only real drag is you have to compile PHP 5 --with-mysqli. I couldn't tell you how to configure it on Windows, but I would imagine you have to futz around with the dll. Hope this helps.