mysql command

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dannymc1983
Forum Commoner
Posts: 80
Joined: Wed Feb 16, 2005 7:24 am

mysql command

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

cbrian
Forum Commoner
Posts: 97
Joined: Sun Feb 27, 2005 12:29 pm

Post 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.
marike
Forum Newbie
Posts: 24
Joined: Thu Mar 31, 2005 6:19 pm
Location: New York

Multiple Delete statements - Use mysqli_multi_query

Post 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.
Post Reply