delete and join

snoopy13

Hy
I have a problem with deleting information out of several tables...
I have three tables
user:
user_ID, name, country, ok

school
user_ID, school

phone:
user_ID, phonenumber

a user can have more than one entries in school and phone. if I want to delete a user I also want to delete his school details and phone numbers. I tried with:

DELETE FROM user, school, phone WHERE user.user_ID=school.user_ID=phone.user_ID AND user.ok != 'ok'

but it doesn't work. probably because I don't have mysql 4 installed. is there an other possibilty to delete all entries from one user from all tables??

thanks a lot!

samueljonasson

Well,

you could of course use 3 different delete statements.

Sambo

snoopy13

hy
what would such a delete statement look like?could you make an example? if i select the user_ID WHERE ok!='ok' from user ....how can I then use this user_ID in the other statements?
thanks?

PHPThorsten

The principle handling would look like this:

/* Get the ID of this User */
$query = "SELECT user_id FROM user WHERE user_id = '$userid'";

$user_id = mysql_query($query);


/* Delete its Phone numbers */
$query = "DELETE FROM phone WHERE user_id = '$user_id'";
mysql_query($query);

/* Delete its school entries */
$query = "DELETE FROM school WHERE user_id = '$user_id'";
mysql_query($query);

/* Delete its users entries */
$query = "DELETE FROM user WHERE user_id = '$user_id'";
mysql_query($query);

snoopy13

all right
maybe I've explained the problem wrong...
I want to do this delete query maybe once a week...to delete all the stuff that is in the database where the user has not said 'ok' so there normaly are more then one...
could I make something like what you said for more than just one? if I do
SELECT user_ID from user WHERE ok!='ok'
the result is an array...how can I go on with this information stored in an array?

PHPThorsten

OK, assuming you have an array with all these ids you just have to walk through this array and delete them from DB.

while(@list($key, $val)=@each($array)) {
    mysql_query("DELETE FROM table WHERE id = '$val' ");
}

snoopy13

thank you
stupid question..what do $key and $val stand for???

PHPThorsten

$key is the Key-name of the array element, $val the value.

$array['key'] = 'value';

while(@list($key, $val)=@each($array)) { 
    echo "Key: " . $key . "<br>Val: " . $val; 
}

(read the manual 😉 )

snoopy13

sorry, I still don't understand..
the key is a number, isn't it? so what number do i use?
and is @list and @each a function?

PHPThorsten

You should start reading here
http://www.php.net/manual/en/
or here
http://www.php.net/manual/de/

ahundiak

DELETE FROM phone
WHERE phone.user_id = user.user_id AND user.ok <> 'ok';

No need for arrays and stuff.

snoopy13

I'd love to, but It doesn't work : unknown table user (because I don't have mysql 4?)

sicranmer

snoopy13,

Sorry to hijack your thread to say sorry if you mail me your code and I will send back the SQL you require.

Anyway...
My host is still using MySQL v3.? and I am having to do a DB cleansing script a bit like snoopy here except across 20 tables. My question is which way is best (ie most efficient)

I would have thought that the following would be very inefficient with so many network/DB calls.

$array['key'] = 'value'; 
while(@list($key, $val)=@each($array)) { 
mysql_query("DELETE …");
}

while(@list($key, $val)=@each($array)) { 
in_list .= $key || ',';
 }
mysql_query("DELETE … WHERE key IN (" . $in_list .")");

(yes I know and remove the final ,) I expect this to be quicker except this must have limitations.

FINALY MY QUESTION: -
What is the max number of entries and at what point will this become inefficient.

Simon.

sicranmer

Anyone any comments please?

drawmack

I would do something like this

$sql = //query to get the ids to delete
$aryIDs = array();
if($toDelete = mysql_query($sql)) {
  for($i=0;$i<$toDelete;$i++) {array_push($aryIDs,$i,$col);}

  $wcIDs = implode(',',$aryIDs);
  $aryTables = array('table1' => 'keyfield1','table2'=>'keyfield2',...,'tablex'=>'keyfieldx');
  foreach(array_keys($aryTables) as $curTable) {
    $sql = "delete from $curTable where $aryTables[$curTable] in ($wcIDs)";
    if(mysql_query($sql)) {
      echo "$curTable cleanded<br />\n";
    } else {
      echo "<font color="#FF0000">Problems cleaning $curTable</font><br>\n";
    } //end if
  } //end foreach
} end if

Then you can even stick the tables to be cleaned in the database so the database can store the clean parameters for itself this way the next time you have to update the script you just make a database change and then surf the web for a week before you tell your boss that it's done.

sicranmer

drawmack,

that all looks top and I like the idea of making life easy BUT I was wondering
1. how efficient this would be
2. if there were any limitations to the number of keys ($wcIDs)
3. if for example there were 200 keys if the delete statement would be slow due to having to access the index so may times.

Cheers

Si.