Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

SQL Commands in nph-build.cgi

Quote Reply
SQL Commands in nph-build.cgi
Hi-

I can run the following two commands in SQL monitor in order to determine the users that have no links. I use a custom field in the Users table called 'Has_Links'

(1) Initialize Has_Links to 'No'

UPDATE lsql_Users
SET Has_Links = 'No'

(2) Set Has_Links to 'Yes' where the user has at least one link:

UPDATE lsql_Users, lsql_Links
SET lsql_Users.Has_Links = 'Yes'
WHERE lsql_Users.Username = lsql_Links.LinkOwner

That works fine in SQL Monitor. Now, I need to get this to run automatically whenever I run nph-build.cgi. I tried the following code, but it didn't work:

$DB->table('Users')->update ( { User_Has_Links => 'No' });

$links_condition = GT::SQL::Condition->new ('Users.Username', '=', 'Links.LinkOwner');

$DB->table({'Users','Links'})->update ( { Users.User_Has_Links => 'Yes' },$links_condition);

I guess this is probably because I'm trying to designate two tables on the last line, and am not doing it right, or maybe I have a problem with the condition? I'm not sure... Does anyone see how this could be written to work on nph-build.cgi?

--Frank
Quote Reply
Re: [FrankM] SQL Commands in nph-build.cgi In reply to
Try;

Code:
$DB->table('Users')->update ( { Has_Links => 'No' });

...and;

Code:
$links_condition = GT::SQL::Condition->new ('Users.Username', '=', 'Links.LinkOwner');

$DB->table('Users','Links')->update ( { User_Has_Links => 'Yes' },$links_condition);

These are untested, but as far as I can see, they should work :)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [FrankM] SQL Commands in nph-build.cgi In reply to
Hi,

I don't think its quite that straightforward - but I could be wrong Tongue. I would try something like this:

my $usersdb = $DB->table('Users');
$usersdb->update ( { User_Has_Links => 'No' });
my $sth = $DB->table('Links')->select('DISTINCT(LinkOwner)');
while (my $user = $sth->fetchrow_array){
$usersdb->update ( {User_Has_Links => 'Yes' },{Username => $user});
}

Last edited by:

afinlr: Nov 23, 2004, 6:50 AM
Quote Reply
Re: [afinlr] SQL Commands in nph-build.cgi In reply to
Thanks very much! I think the second solution will work for me. I also found that it was easier to do in a PHP script, because I could use the more straightforward SQL commands in PHP:

// Reset User_Has_Links equal to No for all users
$reset_query = "UPDATE lsql_Users SET lsql_Users.User_Has_Links = 'No'";
$reset_result = mysql_query($reset_query);

// Set Has_Links to Yes where Users have Links
$update_query = "UPDATE lsql_Users, lsql_Links SET lsql_Users.User_Has_Links = 'Yes' WHERE lsql_Users.Username = lsql_Links.LinkOwner";
$update_result = mysql_query($update_query);

--Frank