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!

-jw

Can you not use the same method but with subtraction?

BTW - Im wondering why you have escaped the two " ?

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

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?

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.

Ah ok, thanks for putting me straight.

Thanks. I tried using Links.Rate and Users.Deposit but that didn't seem to work. I wonder if this is even possible?

-jw

Anything is possible with perl except world peace :)

What code did you try?

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

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

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.

Thanks very much for the help! 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.

-jw

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.

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

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.

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

-jw