Gossamer Forum: Products: Gossamer Links: Development, Plugins and Globals: Can you use an UPDATE in a global?

Apr 29, 2002, 11:23 AM

Andy

Veteran / Moderator (17366 posts)

Apr 29, 2002, 11:23 AM

Post #1 of 21

Shortcut

Can you use an UPDATE in a global?

Hi. I've been searching high and low in the forums and globals part of the resources section. I can't seedm to find any code that references being able to use an UPDATE command via a global. For example, I would need to run;

SELECT * FROM Links_Links WHERE ID = $id

or something similar. Also, whilst I'm asking...is there a faster way to update an entry? I'm looking at this working on updating quite a lot..so about 100k times a day! I'm just conserned about speed and processor usage etc.

Any feedback would be welcome :)

Andy (mod)
andy@ultranerds.co.uk

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates

Apr 29, 2002, 11:43 AM

Paul

Veteran (19537 posts)

Apr 29, 2002, 11:43 AM

Post #2 of 21

Shortcut

Re: [Andy.] Can you use an UPDATE in a global? In reply to

Hmm Im not sure what you mean about doing updates...can't you just use the sql modules?

Apr 29, 2002, 12:00 PM

webmaster33

Veteran (2312 posts)

Apr 29, 2002, 12:00 PM

Post #3 of 21

Shortcut

Re: [Andy.] Can you use an UPDATE in a global? In reply to

Andy,

I suppose you can do UPDATE in globals, too, as any other sql queries.
I'll check the GT::SQL docs and I will be back in a few minutes.
Back now.

The GT::SQL::Table module docs says:

Quote:

$obj->update ( { Setme => 'NewValue'}, { WhereCol => 5 });
would set the column 'Setme' to 'NewValue' where the column 'WhereCol' is 5.

This translates to:
UPDATE Table SET SetMe='NewValue' WHERE WhereCol = 5

As queries are possible in globals, I suppose update is also possible.
As for the CPU usage/performance: its up to you to do updates 100K times a day, but you know how busy is your site, how much data you update in a step of a global call, and if your server would be able to serve so much updates.
My personal opinion is 100K updates a day is not too much for a MySQL server, but depends how strong is your server and how much update you do in a step.

Best regards,
Webmaster33

Paid Support from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...

Last edited by:

webmaster33: Apr 29, 2002, 12:12 PM

Apr 29, 2002, 12:10 PM

Paul

Veteran (19537 posts)

Apr 29, 2002, 12:10 PM

Post #4 of 21

Shortcut

Re: [webmaster33] Can you use an UPDATE in a global? In reply to

The modules support updates...the code is the same as always...

$DB->table('Table')->update( ... );

....I don't understand the confusion Smile

Apr 29, 2002, 12:40 PM

Andy

Veteran / Moderator (17366 posts)

Apr 29, 2002, 12:40 PM

Post #5 of 21

Shortcut

Re: [Paul] Can you use an UPDATE in a global? In reply to

Cool...thanks. So would;

$obj->update ( { ImpCount => ImpCount + 1 }, { ID => $ID });

work?

Thanks. I think I can see how powerful this plugin stuff is...just gotta get used to it :p

Andy (mod)
andy@ultranerds.co.uk

Apr 29, 2002, 12:41 PM

Paul

Veteran (19537 posts)

Apr 29, 2002, 12:41 PM

Post #6 of 21

Shortcut

Re: [Andy.] Can you use an UPDATE in a global? In reply to

$obj isn't literal...you'd need:

$DB->table('Table')->update ( { ImpCount => \'ImpCount + 1' }, { ID => $ID });

Apr 29, 2002, 12:46 PM

Andy

Veteran / Moderator (17366 posts)

Apr 29, 2002, 12:46 PM

Post #7 of 21

Shortcut

Re: [Paul] Can you use an UPDATE in a global? In reply to

Ah cool. BTW, maybe I'm being silly here, but why do you only escape one of the 's?

Like;

\'ImpCount + 1'

shouldn't it be either

'ImpCount + 1'

or

\'ImpCount + 1\'

Or maybe I'm just being completly stupid Tongue

Andy (mod)
andy@ultranerds.co.uk

Apr 29, 2002, 12:49 PM

Paul

Veteran (19537 posts)

Apr 29, 2002, 12:49 PM

Post #8 of 21

Shortcut

Re: [Andy.] Can you use an UPDATE in a global? In reply to

>>Or maybe I'm just being completly stupid<<

No comment. Wink

The \ isn't escaping...its referencing so you only need one.

Basically it is the same as

$foo = \$bar

.....except that as you don't have the scalar pre-defined it is easier to just reference it "on the fly"

The reference then tells the SQL module to not use quotes around the ImpCount + 1 bit otherwise mysql won't do the right query.

Last edited by:

Paul: Apr 29, 2002, 12:50 PM

Apr 29, 2002, 12:50 PM

yogi

Veteran / Moderator (2199 posts)

Apr 29, 2002, 12:50 PM

Post #9 of 21

Shortcut

Re: [Andy.] Can you use an UPDATE in a global? In reply to

It's got nothing to do with plugins, really. It just the GT library that you should use.

I recommend "Help -> GT Module Documentation", nice documentation of all the modules. The module related to your question is GT::SQL::Table. Remember that you can always use $DB, which is a GT::SQL object. To create a GT::SQL::Table object, use

Code:
my $table = $DB->table ('Links');

Ivan
-----
Iyengar Yoga Resources / GT Plugins

Apr 29, 2002, 12:56 PM

Andy

Veteran / Moderator (17366 posts)

Apr 29, 2002, 12:56 PM

Post #10 of 21

Shortcut

Re: [yogi] Can you use an UPDATE in a global? In reply to

Thanks for that Paul..I think Unimpressed

LOL.

And Ivan...thats amazing. Didn't realise there was all this documentation! Thanks for alerting me to this Smile

Andy (mod)
andy@ultranerds.co.uk

Apr 29, 2002, 1:32 PM

Andy

Veteran / Moderator (17366 posts)

Apr 29, 2002, 1:32 PM

Post #11 of 21

Shortcut

Re: [yogi] Can you use an UPDATE in a global? In reply to

Mmm..ok...getting there. That documentatino stuff is great :)

Still got a problem though;

Code:
sub { 

my $ID = shift; 
my $table = $DB->table('Links');  
$table->update ( { ShowCount => \'ShowCount + 1' }, { ID = $ID } ); 

}

The error I am getting is;

>>>>>>>A fatal error has occured:

GT::Config (1692735): Unable to compile 'update_count' in file 'c:/PROGRA~1/NUSPHERE/APACHE/NSCGI-~1/LINKS_~1/admin/templates/default/globals.txt': Can't modify constant item in scalar assignment at (eval 10) line 5, at EOF
at c:/PROGRA~1/NUSPHERE/APACHE/NSCGI-~1/LINKS_~1/admin/GT/Template.pm line 462.

Please enable debugging in setup for more details.
<<<<<<

Anyone got an idea as to what I'm doing wrong?

Thanks

Andy (mod)
andy@ultranerds.co.uk

Apr 29, 2002, 1:36 PM

yogi

Veteran / Moderator (2199 posts)

Apr 29, 2002, 1:36 PM

Post #12 of 21

Shortcut

Re: [Andy.] Can you use an UPDATE in a global? In reply to

{ ID = $ID } should be { ID => $ID } ...

Ivan
-----
Iyengar Yoga Resources / GT Plugins

Apr 29, 2002, 1:40 PM

Paul

Veteran (19537 posts)

Apr 29, 2002, 1:40 PM

Post #13 of 21

Shortcut

Re: [Andy.] Can you use an UPDATE in a global? In reply to

= = =>

Apr 29, 2002, 1:41 PM

Paul

Veteran (19537 posts)

Apr 29, 2002, 1:41 PM

Post #14 of 21

Shortcut

Re: [yogi] Can you use an UPDATE in a global? In reply to

Darn you beat me :)

Apr 29, 2002, 1:49 PM

Andy

Veteran / Moderator (17366 posts)

Apr 29, 2002, 1:49 PM

Post #15 of 21

Shortcut

Re: [Paul] Can you use an UPDATE in a global? In reply to

LOL...thanks guys..the global is working ok now...all I gotta do is put it into a plugin, and write the install.pm file, and then tidy up the PHP interface for viewing the stats of views.

The idea of this plugin came from a customer ages ago. To start with, I just made a script that was called via an IFRAME. The idea was it would record the impressions a link has had, including searches and category views. This proved a little un-reliable and didn't seem to count the stats correctly. So, now I have a _little_ plugin knowledge, I thought I would try and knock up a plugin :) And...hopefully, by tomorrow I will have it completed, and ready for download. Obviously it will only work in dynamic mode (at the moment, I'll work on making it work with static, but it won't be anywhere as near as reliable due to the fact the IFRAMEs may not load).

Anyway..I need some sleep... Wink

Andy (mod)
andy@ultranerds.co.uk

Apr 29, 2002, 11:55 PM

Andy

Veteran / Moderator (17366 posts)

Apr 29, 2002, 11:55 PM

Post #16 of 21

Shortcut

Re: [Paul] Can you use an UPDATE in a global? In reply to

Ok. I'm getting there with this. Just got one more tihng to do, and then I can work on a 'how-to'. Last problem I am having, is creating a new column in the Prefix_Links table. I need to make one called 'impressions'. At the moment I have;

# update the new tables in MySQL...
my $creator = $DB->creator('Links');
$creator->ai ('impressions');
$creator->create or die "Unable to create: $GT::SQL::error";

However, this is causing errors, as it trys to re-create the Links table. Is there a wayt to create a column withouthavingt to re-create the Links table?

Thanks in advance :)

Andy (mod)
andy@ultranerds.co.uk

Apr 29, 2002, 11:59 PM

yogi

Veteran / Moderator (2199 posts)

Apr 29, 2002, 11:59 PM

Post #17 of 21

Shortcut

Re: [Andy.] Can you use an UPDATE in a global? In reply to

Code:
my $editor = $DB->editor ('Links');

Again, the documentation is your friend (GT::SQL::Editor)... Wink

Ivan
-----
Iyengar Yoga Resources / GT Plugins

Apr 30, 2002, 12:06 AM

Andy

Veteran / Moderator (17366 posts)

Apr 30, 2002, 12:06 AM

Post #18 of 21

Shortcut

Re: [yogi] Can you use an UPDATE in a global? In reply to

I must be going blingd. I thought I went through all of the sections for GT::SQL. Sorry about that Blush

I'm now using the correct format, but it still won't like working. I've got;

Code:
# update the new tables in MySQL... 
 my $creator = $DB->editor('Links'); 
 $creator->cols (  
                        impressions => { 
                                    type => 'INT', 
                                    not_null => 1, 
                                    regex => '^\d' 
                                       } 
                        ); 

 $creator->create or die "Unable to create: $GT::SQL::error";

Can you see anything obviouse there that isn't right? The error I get is;

Error running installation code: GT::SQL::Editor (1601833): Unknown method 'cols' called at (eval 6) line 122.

Thanks

Andy (mod)
andy@ultranerds.co.uk

Last edited by:

Andy.: Apr 30, 2002, 12:17 AM

Apr 30, 2002, 2:38 AM

Paul

Veteran (19537 posts)

Apr 30, 2002, 2:38 AM

Post #19 of 21

Shortcut

Re: [Andy.] Can you use an UPDATE in a global? In reply to

Hmm you like to make things nice and simple don't you mixing creator with editor....

As yogi said read the _docs_

Its add_col

Last edited by:

Paul: Apr 30, 2002, 2:39 AM

May 1, 2002, 11:30 AM

Alex

Administrator (9387 posts)

May 1, 2002, 11:30 AM

Post #20 of 21

Shortcut

Re: [Andy.] Can you use an UPDATE in a global? In reply to

Just a comment on:

$table->update ( { ShowCount => \'ShowCount + 1' }, { ID = $ID } );

The reason you want \'ShowCount +1', rather then just 'ShowCount + 1', as by default everything is quoted for you automatically. So if you did:

$table->update ( { ShowCount => 'ShowCount + 1' }, { ID = $ID } );

that translates to:

UPDATE table SET ShowCount = 'ShowCount + 1' WHERE ID = '$ID'

which is not what you want. To not quote something that normally is auto quoted, you pass in a reference:

$table->update ( { ShowCount => \'ShowCount + 1' }, { ID = $ID } );

Cheers,

Alex
--
Gossamer Threads Inc.

May 1, 2002, 11:33 AM

Andy

Veteran / Moderator (17366 posts)

May 1, 2002, 11:33 AM

Post #21 of 21

Shortcut

Re: [Alex] Can you use an UPDATE in a global? In reply to

Thanks for the explanation Alex. You learn something new every day Smile

At least I understand now WHY I'm doing something...lol

Andy (mod)
andy@ultranerds.co.uk