vb.org Archive - MySQL Query worked in 3.23 but not 4.1 (Help Requested)

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - MySQL Query worked in 3.23 but not 4.1 (Help Requested) (https://vborg.vbsupport.ru/showthread.php?t=73677)

hurrican

01-02-2005 03:13 PM

MySQL Query worked in 3.23 but not 4.1 (Help Requested)

Hello Everyone,
I was running MySQL v3.23 until yesterday - I Upgraded to 4.1, with the latest PHP and Apache. I want to run a script locally on the server to remove all the users who were imported from my UBB system, and the lastvisit = 0 (b/c they haven't visited since i've upgraded to vb).

Here's the actual query im running minus the connect details:

PHP Code:


		
			
DELETE FROM user WHERE lastvisit=0 AND usergroupid=2,3,10,11

This did not produce an error on 3.23, but it does now, gives me

error 1064: You have an error in your SQL syntax. Check the manual that corresp
onds to your mysql server version for the right syntax to use near '3,10,11'

So I read up a little, and put 2 OR 3 OR 10 OR 11, but that just totally deleted my entire USER table ( Good thing I make backup of everything before I start :nervous: )

I have this one to run via cron job (worked before) to remove any users who have not visited in the past 365 days. Now this doesn't work either.

PHP Code:


		
			
DELETE FROM user WHERE lastvisit ".(time() - (60 * 60 * 24 * 365))." AND usergroupid=2,3,10,11

Any help would be greatly appreciated!

filburt1

01-02-2005 03:20 PM

Change the syntactually incorrect usergroupid clause to:

Code:

...AND usergroupid IN (2, 3, 10, 11)

Xenon

01-02-2005 03:25 PM

hmm, i cannot believe that worked in 3.23

*still wondering*

hurrican

01-02-2005 04:26 PM

Something so simple, yet complex, lol. I Appreciate it! Worked like a charm :) I'm getting a lot better, but still got some training to do, not off training wheels just yet :)

Hope you both had a great Christmas/New Year :)

hurrican

01-13-2005 10:54 PM

Well, It worked one time, and not the next time. I run this via cron script - did not include entire cron script, just the delete statement.. The problem I have is that it will not remove anyone.. I never put it into use on my real db yet. I had to end up recompiling MySQL/PHP/Apache -- Did I maybe not include something I should have when I compiled PHP?? If so, my entire ./configure is below -- thanks for any help, again! :)

My exact DELETE query is this:

PHP Code:


		
			
mysql_query("DELETE FROM user WHERE lastvisit ".(time() - (60 * 60 * 24 * 487))." AND usergroupid IN (2, 3, 10, 11)", $db);

Code:

'./configure' '--with-apxs2=/etc/httpd/bin/apxs' '--with-freetype-dir=/usr/local' '--with-png-dir=/usr/local' '--with-gd=/usr/local/gd' '--enable-gd-native-ttf' '--with-ttf' '--with-gdbm' '--with-jpeg-dir=/usr/local' '--with-openssl' '--with-png' '--with-xml' '--with-zlib' '--enable-track-vars' '--with-kerberos=/usr/kerberos' '--with-mysql' '--enable-memory-limit' '--enable-calendar'

WetWired

01-14-2005 03:00 AM

Quote:

Originally Posted by hurrican

PHP Code:


		
			
mysql_query("DELETE FROM user WHERE lastvisit ".(time() - (60 * 60 * 24 * 487))." AND usergroupid IN (2, 3, 10, 11)", $db);

Code:

'./configure' '--with-apxs2=/etc/httpd/bin/apxs' '--with-freetype-dir=/usr/local' '--with-png-dir=/usr/local' '--with-gd=/usr/local/gd' '--enable-gd-native-ttf' '--with-ttf' '--with-gdbm' '--with-jpeg-dir=/usr/local' '--with-openssl' '--with-png' '--with-xml' '--with-zlib' '--enable-track-vars' '--with-kerberos=/usr/kerberos' '--with-mysql' '--enable-memory-limit' '--enable-calendar'

You need a condtional ("<"?) for the lastvisit, and parenthesis arround each condition couldn't hurt.

hurrican

01-14-2005 04:08 PM

Quote:

Originally Posted by WetWired

You need a condtional ("<"?) for the lastvisit, and parenthesis arround each condition couldn't hurt.

I tried doing that also, but still receive the same results.. It's very weird that it worked before I had to recompile it, I remember testing it w/ filburt1's suggestion and worked fine.. I'm baffled :nervous:

WetWired

01-14-2005 05:33 PM

Hmm, well, if you're running

Code:

'DELETE FROM user WHERE (lastvisit<'.(time() - (60 * 60 * 24 * 487)).') AND (usergroupid IN (2, 3, 10, 11))'

and you're not getting any deletions, maybe there aren't any users that meet the criteria. Really, though, shouldn't you be doing a

Code:

'SELECT userid FROM user WHERE (lastvisit<'.(time() - (60 * 60 * 24 * 487)).') AND (usergroupid IN (2, 3, 10, 11))'

then doing a delete for each user table? Otherwise, you leave a whole bunch of orphaned records in your DB; in fact, I think the usertext table entry will usually be larger than the user table entry if the user was ever active.

hurrican

01-15-2005 01:55 AM

WetWired - Thanks for all your help! I just hacked the file with several combinations of what you and filburt have told me to do, and finally must have gotten a good combination.. This leads my to my next question (about the orphaned records).. The hack called "Unactivated User Management" has the following code:

Code:

       $DB_site->query("UPDATE post SET username='".addslashes($user[username])."',userid=0 WHERE userid='$user[userid]'");

       $DB_site->query("DELETE FROM user WHERE userid='$user[userid]'");

       $DB_site->query("DELETE FROM userfield WHERE userid='$user[userid]'");

       $DB_site->query("DELETE FROM access WHERE userid='$user[userid]'");

       $DB_site->query("DELETE FROM calendar_events WHERE userid='$user[userid]'");

       $DB_site->query("DELETE FROM customavatar WHERE userid='$user[userid]'");

       $DB_site->query("DELETE FROM moderator WHERE userid='$user[userid]'");

       $DB_site->query("DELETE FROM privatemessage WHERE userid='$user[userid]'");

       $DB_site->query("DELETE FROM subscribeforum WHERE userid='$user[userid]'");

       $DB_site->query("DELETE FROM subscribethread WHERE userid='$user[userid]'");

       $DB_site->query("DELETE FROM session WHERE userid='$user[userid]'");

This is the additional fields that apparently need to be added to my php/cronjob script to delete the orphaned information. Because I am doing this in a cronjob using a plain jane PHP script, how would I go about putting all of this in one script file. MYSQL would not know what $user[userid] is since I just use the plain php script not calling any variables. Any help is greatly appreciated!

BTW -- Where do you live in Texas? I am also from Texas and live in Beaumont. Thanks for all of your help and have a great weekend!

WetWired

01-15-2005 05:08 AM

Well, you couldn't do this for the usernames (which should revert to the name of the user when they posted the post, anyway), but for the deletion of tables, after you do the select for users meeting the criteria, you need to run a loop to make a list of IDs. Once you have the list of IDs, you can feed them to each query in turn. So:

Code:

$idlist='0';

$result=$DB_site->query('SELECT userid FROM user WHERE (lastvisit<'.(time() - (60 * 60 * 24 * 487)).') AND (usergroupid IN (2, 3, 10, 11))');

while($userinfo=$DB_site->fetch_array()){

  $idlist.=','.$userinfo['userid'];

}

$DB_site->query("DELETE FROM user WHERE userid IN (".$idlist.")");

$DB_site->query("DELETE FROM userfield WHERE userid IN (".$idlist.")");

$DB_site->query("DELETE FROM access WHERE userid IN (".$idlist.")");

$DB_site->query("DELETE FROM calendar_events WHERE userid IN (".$idlist.")");

$DB_site->query("DELETE FROM customavatar WHERE userid IN (".$idlist.")");

$DB_site->query("DELETE FROM moderator WHERE userid IN (".$idlist.")");

$DB_site->query("DELETE FROM privatemessage WHERE userid IN (".$idlist.")");

$DB_site->query("DELETE FROM subscribeforum WHERE userid IN (".$idlist.")");

$DB_site->query("DELETE FROM subscribethread WHERE userid IN (".$idlist.")");

$DB_site->query("DELETE FROM session WHERE userid IN (".$idlist.")");

To find userid's from previous cleanups, the query would be
[sql]SELECT usertextfield.userid FROM usertextfield LEFT JOIN user ON usertextfield.userid=user.userid WHERE ISNULL(user.username)[/sql]

hurrican

01-15-2005 02:26 PM

I must be an idiot, because I can't get it to work, lol.. If I were to totally remove my DELETE from and paste your code, I get: Fatal error: Call to a member function on a non-object in /wwwscripts/yearnotactive.php on line 9

Here is my original code the entire length of the file

PHP Code:


		
			
<?php

$db = mysql_connect('localhost', 'user', 'pass') or

        die("Could not connect to database with error (".mysql_error().")");



$db_sel = mysql_select_db('testing', $db) or

        die("Could not select database $db_name with error (".mysql_error().")");



mysql_query("DELETE FROM user WHERE (lastvisit<'.(time() - (60 * 60 * 24 * 487)).') AND (usergroupid IN (2, 3, 10, 11)",$db);

?>

I changed all of the $DB_site->query to mysql_query, I also had set it up to require admin/config.php (and commenting out the first 5 lines) I moved the ,$db); to the very end of the last statement, etc. Is what I am trying to accomplish not actually doable using a file like this? eek! Thanks :)

Dean C

01-15-2005 02:52 PM

PHP Code:


		
			
mysql_query("DELETE FROM user WHERE (lastvisit<'.(time() - (60 * 60 * 24 * 487)).') AND (usergroupid IN (2, 3, 10, 11)",$db);

Should be:

PHP Code:


		
			
mysql_query("DELETE FROM user WHERE (lastvisit<" . (time() - (60 * 60 * 24 * 487)) . ") AND (usergroupid IN (2, 3, 10, 11)",$db);

hurrican

01-16-2005 03:50 PM

Thanks Dean! Got that corrected :) How can I add those other DELETE strings from a php script that was made by hand (as my original above) to delete the 'ghost' records?

All times are GMT. The time now is 07:59 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01162 seconds Memory Usage 1,790KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (7)bbcode_code_printable (7)bbcode_php_printable (2)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (13)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.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/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: