MySQL: Problem optimizing multi-table row deletion ... errors out :(

[-{ Rene Brehmer }- <metalbunny@xxxxxxxxxxxxxx>]

hi gang

I'm working on optimizing the SQL for my forum system, but have run into a 
problem trying to optimize the function for deleting a thread with 
accompanying stats data and posts from the database using 1 query.

The current function, that works, looks like this:

// delete all traces of a thread
function delThread($threadID) {
  $result = mysql_query("DELETE FROM hf_threads WHERE 
`threadID`='$threadID'");
  $result = mysql_query("DELETE FROM hf_thread_stats WHERE 
`threadID`='$threadID'");
  $result = mysql_query("DELETE FROM hf_posts WHERE `threadID`='$threadID'");
}

I went through the MySQL manual, to try and find a way to optimize this, 
and came up with this variant:

// delete all traces of a thread
function delThread($threadID) {
  $result = mysql_query("DELETE FROM hf_threads,hf_thread_stats,hf_posts 
WHERE `threadID`='$threadID'") or die('Unable to delete 
thread<br>'.mysql_error());
}

only it doesn't work at all ... the error output says this:

Unable to delete thread
You have an error in your SQL syntax near 'hf_thread_stats,hf_posts WHERE 
`threadID`='85'' at line 1

(Obviously I attempted to delete the thread with ID 85 here)

I'm dealing with MySQL 3.23.56-log and PHP 4.3.0 ...

Any ideas for optimizing this will be greatly appreciated, esp since I'm 
not all that great with the more complex SQL syntax...


TIA

Rene
--
Rene Brehmer
aka Metalbunny

If your life was a dream, would you wake up from a nightmare, dripping of 
sweat, hoping it was over? Or would you wake up happy and pleased, ready to 
take on the day with a smile?

http://metalbunny.net/
References, tools, and other useful stuff...
Check out the new Metalbunny forums at http://forums.metalbunny.net/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php