[PHP-DB] MySQL: Problem optimizing multi-table row deletion ... errors out :(

2004-09-13 Thread -{ Rene Brehmer }-
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 
threadbr'.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


Re: [PHP-DB] MySQL: Problem optimizing multi-table row deletion ... errors out :(

2004-09-13 Thread randy
If the threadID column is an int, you don't need to wrap the $threadID
variable in quotes.

HTH ~randy

On Tue, 14 Sep 2004 00:05:56 +0200, -{ Rene Brehmer }-
[EMAIL PROTECTED] wrote:
 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
 threadbr'.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
 
 



-- 
randy [EMAIL PROTECTED]

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