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.