Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions
FAQ Community Calendar Today's Posts Search

Reply
 
Thread Tools Display Modes
  #1  
Old 03-14-2007, 04:05 PM
djbaxter djbaxter is offline
 
Join Date: Aug 2006
Location: Ottawa, Canada
Posts: 2,601
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default MySQL syntax error for delete

What is wrong with this query?

Code:
DELETE FROM vbpmtext, vbpm, vbpmreceipt WHERE (vbpm.pmid = vbpmreceipt.pmid) AND (vbpm.pmtextid = vbpmtext.pmtextid) AND vbpmtext.dateline < $time
I am trying to run it from this PHP utility but it also gives me an error run from phpMyAdmin:

PHP Code:
<?php 

require ("global.php"); 
$time time() - (90 24 60 60); 

 
$query "DELETE FROM vbpmtext, vbpm, vbpmreceipt WHERE (vbpm.pmid = vbpmreceipt.pmid) AND (vbpm.pmtextid = vbpmtext.pmtextid) AND vbpmtext.dateline < $time";
 
mysql_query($query);
 print 
$query
?>
This version seems to work:

Quote:
DELETE FROM vbpmtext, vbpm, vbpmreceipt USING vbpmtext, vbpm, vbpmreceipt WHERE (vbpm.pmid = vbpmreceipt.pmid) AND (vbpm.pmtextid = vbpmtext.pmtextid) AND vbpmtext.dateline < $time
in the sense that it doesn't generate a syntax error. Anyone see anything wrong with that?
Reply With Quote
  #2  
Old 03-14-2007, 05:15 PM
nexialys
Guest
 
Posts: n/a
Default

You can't delete 3 table elements at the same time... you have to run three queries for that.
Reply With Quote
  #3  
Old 03-14-2007, 09:26 PM
harmor19 harmor19 is offline
 
Join Date: Apr 2005
Posts: 1,324
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

You don't need to put the query into a variable then the variable into a method.
You can just use $db->query_write()
Example:
PHP Code:
$db->query_write("DELETE FROM " TABLE_PREFIX "temp_host_request WHERE userid = '".$db->escape_string($vbulletin->GPC['postuserid'])."' "); 

The syntax is "DELETE FROM [table]". You don't list the fields.
Reply With Quote
  #4  
Old 03-15-2007, 04:39 AM
Dismounted's Avatar
Dismounted Dismounted is offline
 
Join Date: Jun 2005
Location: Melbourne, Australia
Posts: 15,047
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

That depends if he's using a vBulletin backend or not.
Reply With Quote
  #5  
Old 03-15-2007, 05:12 AM
harmor19 harmor19 is offline
 
Join Date: Apr 2005
Posts: 1,324
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Quote:
Originally Posted by Dismounted View Post
That depends if he's using a vBulletin backend or not.
He called "global.php" so I'm assuming he is.
Reply With Quote
  #6  
Old 03-15-2007, 06:45 AM
Marco van Herwaarden Marco van Herwaarden is offline
 
Join Date: Jul 2004
Posts: 25,415
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

What is the MySQL version you are using and what is the exact error message you get?
Reply With Quote
  #7  
Old 03-15-2007, 12:31 PM
djbaxter djbaxter is offline
 
Join Date: Aug 2006
Location: Ottawa, Canada
Posts: 2,601
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Update: I'm not getting error messages using either of the following MySQL statements:

Version 1 (works):

PHP Code:
 $query "DELETE FROM " TABLE_PREFIX "pmtext, " TABLE_PREFIX "pm, " TABLE_PREFIX "pmreceipt USING " TABLE_PREFIX "pmtext, " TABLE_PREFIX "pm, " TABLE_PREFIX "pmreceipt WHERE (" TABLE_PREFIX "pm.pmid = " TABLE_PREFIX "pmreceipt.pmid) AND (" TABLE_PREFIX "pm.pmtextid = " TABLE_PREFIX "pmtext.pmtextid) AND " TABLE_PREFIX "pmtext.dateline < $time"
Version 2 (works):

PHP Code:
 $query "DELETE " TABLE_PREFIX "pmtext . * , " TABLE_PREFIX "pm . * , " TABLE_PREFIX "pmreceipt . * FROM " TABLE_PREFIX "pmtext, " TABLE_PREFIX "pm, " TABLE_PREFIX "pmreceipt WHERE " TABLE_PREFIX "pm.pmid = " TABLE_PREFIX "pmreceipt.pmid AND " TABLE_PREFIX "pm.pmtextid = " TABLE_PREFIX "pmtext.pmtextid AND " TABLE_PREFIX "pmtext.dateline < $time"
Is there any advantage of one over the other?

I will release this as an add-on when it's fully tested in case anyone else might find it useful.

I'm using PHP v4.4.4 with MySQL v4.1.21.
Reply With Quote
  #8  
Old 03-17-2007, 08:34 AM
Cap'n Steve's Avatar
Cap'n Steve Cap'n Steve is offline
 
Join Date: Feb 2004
Location: Kalamazoo, MI, USA
Posts: 745
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

This is an interesting topic, and I'll have to take a look at your version 1 query since I've never used USING. I've had a lot of trouble doing cross-table deletes recently, and so far I've found that listing the fields (table1.*, table2.*) and avoiding aliases seems to work the best.
Reply With Quote
Reply


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 04:28 PM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.04627 seconds
  • Memory Usage 2,244KB
  • Queries Executed 13 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)bbcode_code
  • (4)bbcode_php
  • (2)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (8)post_thanks_box
  • (8)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (8)post_thanks_postbit_info
  • (8)postbit
  • (7)postbit_onlinestatus
  • (8)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete