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

[hurrican]

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]

Change the syntactually incorrect usergroupid clause to:

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

[Xenon]

hmm, i cannot believe that worked in 3.23

*still wondering*

[hurrican]

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]

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); 


'./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]

Originally Posted by hurrican

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); 


'./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]

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]

Hmm, well, if you're running

'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

'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]

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:

       $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]

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:

$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]