Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Update User Table

Quote Reply
Update User Table
Hi-

I have modified the Jump.pm so that each Hit not only increments the Hits field by one, but also the field called Total_Amount by the value in the field Rate:

$db->update ( { Hits => \"Hits + 1", Total_Amount => \"Total_Amount + Rate"}, { ID => $id }, { GT_SQL_SKIP_INDEX => 1 } );

So if the Rate field is set to 0.10 and the Total_Amount field starts at 0.00, then after the first hit the total amount field increments to 0.10, second hit, 0.20, etc.

I would like to also have it reduce a field called Deposit in the User table for that Link by the Rate value.

So, if the User for the Link started with 50.00 in the Deposit field (in the User Table), and one of his/her links has the Rate value of 0.10, then it would decrease the Deposit field to 49.90 for the first click on that link, 49.80 for the next click, etc...

Does anyone have any idea if it's possible to do this by modifying Jump.pm?

Any help or insights would be much appreciated!Smile

-jw



Quote Reply
Re: [jwalter] Update User Table In reply to
Can you not use the same method but with subtraction?

BTW - Im wondering why you have escaped the two " ?
Quote Reply
Re: [RedRum] Update User Table In reply to
Hi-
I can get it to work by subtraction within the Link table, but I want it to subtract from the field Deposit in the User table using the value in the Rate field in the Links table for that link. I'm not sure how to set it up to subtract from the Users table. (Sorry, I wasn't sure what you meant by the escaped the two ")

-jw
Quote Reply
Re: [jwalter] Update User Table In reply to
Quote:
Hits => \"Hits + 1", Total_Amount => \"Total_Amount + Rate"}

Those bolded slashes are what I meant by escpaing " (sorry for being vague)

Im sure Alex will correct me on this but I'm guessing you'll need to use something like:

Links.Rate and Users.Deposit rather than just Rate and Deposit.

Is that any clearer?

Last edited by:

RedRum: Oct 16, 2001, 12:39 PM
Quote Reply
Re: [RedRum] Update User Table In reply to
Hi,

The \"Hits +1" is because if you just do Hits => "Hits + 1", that turns into Update Links Set Hits = 'Hits + 1' and not Update Links Set Hits = Hits + 1.

Basically if you escape something it tells GT::SQL not to quote it.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] Update User Table In reply to
Ah ok, thanks for putting me straight.
Quote Reply
Re: [RedRum] Update User Table In reply to
Thanks. I tried using Links.Rate and Users.Deposit but that didn't seem to work. I wonder if this is even possible?

-jw
Quote Reply
Re: [jwalter] Update User Table In reply to
Anything is possible with perl except world peace :)

What code did you try?
Quote Reply
Re: [RedRum] Update User Table In reply to
Hi,

I tried the following:

$db->update ( { Hits => \"Hits + 1", Total_Amount => \"Total_Amount + Rating", Deposit => \"Deposit - Rate", Links_Users.Deposit => \"Links_Users.Deposit - Links_Links.Rate"}, { ID => $id }, { GT_SQL_SKIP_INDEX => 1 } );

but I think $db->update refers to only the Links table as defined earlier in Jump.pm. Any ideas?

-jw
Quote Reply
Re: [jwalter] Update User Table In reply to
Hi,

You might try something like this:

Add this line:
my $update_db = $DB->table('Links','Users');

Then:
$update_db->update ( { Hits => \"Hits + 1", Total_Amount => \"Total_Amount + Rating", Deposit => \"Deposit - Rate"}, { ID => $id }, { GT_SQL_SKIP_INDEX => 1 } );

(With a relational query, I think you want to call the columns without the table name prefix.)

I'm not sure if this will work, but might be worth a try. This is untested, of course.

(Edit: deleted extra "Deposit =>" from code)

--
Matt G

Last edited by:

mglaspie: Oct 16, 2001, 9:59 PM
Quote Reply
Re: [jwalter] Update User Table In reply to
The way links works, you need to grab a new database/table handle.

my $db_users = $DB->table('Users');

Now, use

$db_users->update (.....) to make the changes to that table.




PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] Update User Table In reply to
Thanks very much for the help! Smile The problem is that I need to update the Users field 'Deposit' using a value from the Links field 'Rate'. So, if I use a new database/table handle for the Users table would I be able to use the value from the Links table? It seems like it doesn't work... I also tried the code with the handle having both tables designated, but that generated an Internal Server Error.

Basically, I'm trying to reduce Users.Deposit by the amount in Links.Rate and the difficulty is using a value from the Links table to reduce a value in the Users table...

I guess it's a lot harder to do than it seemed at first. Unsure

-jw


Quote Reply
Re: [jwalter] Update User Table In reply to
Take the value from the Links table, and put it in a variable, or simply use the results of that query as the parameter value for the other update.

Assign, or select the Links->{Rate} value and use that to update the Users->{Deposit} field.

You may have to backtrack a few lines, make some variable assignments, then do the updates, for clarity and fewer steps, but it should be pretty obvious when you look at your code how to do it.

You might simply need to select-> the record, then assign the Rate to a variable, then update both the Link and Users tables. Don't try to get fancier than that. It's low overhead, and you still have to make two update calls. Fancier SQL statements could impose higher overhead, and make understanding the code more difficult.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [jwalter] Update User Table In reply to
Should "Rating" be "Rate" instead?

Maybe try this:
my $update_db = $DB->table('Links','Users');

Then:
$update_db->update ( { Hits => \"Hits + 1", Total_Amount => \"Total_Amount + Rate", Deposit => \"Deposit - Rate"}, { ID => $id }, { GT_SQL_SKIP_INDEX => 1 } );

Not sure what the "GT_SQL_SKIP_INDEX => 1" part is for... might not be necessary.

--
Matt G
Quote Reply
Re: [mglaspie] Update User Table In reply to
Hi,

"GT_SQL_SKIP_INDEX => 1"

tells GT::SQL that you don't need to reindex the record (as nothing with search weights has changed).

Cheers,

Alex


--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] Update User Table In reply to
Matt & Pugdog,

Thanks very much for your help, I couldn't get it to work yet, but I'm having someone more proficient than me at PERL look at it to make it work... Thanks again Smile

-jw