Gossamer Forum
Quote Reply
PPC
I'm (trying) to work on my own PPC Plugin for Links SQL. So far its going pretty well, however I have come accross a problem as to how I am going to format it.

I thought I could get away with adding Bid DESC in the category sort varable, but that still shows up bidding links, even if their account has no credit. What I was thinking now, is to write a new search script, based on the Search.pm code by GT, and then modify it to specify the PPC results. The problem is...I don't see how!

Code:
# Then do the search.
$args->{callback} = \&_cat_search_subcat if ($args->{catid});
my $orig_sb = $args->{sb};
my $orig_so = $args->{so};
$args->{sb} = $CFG->{build_sort_order_search_cat};
$args->{so} = '';
$filter and $args->{sb} =~ s/score//;
my $cat_sth = $categories->query_sth ($args) unless ($filter);
my $cat_count = $filter ? 0 : $categories->hits();

$args->{callback} = \&_search_subcat if ($args->{catid});
$args->{sb} = $orig_sb ? $orig_sb : $CFG->{build_sort_order_search} || '';
$args->{so} = (defined $orig_so and $orig_so =~ /^(asc|desc)$/i) ? $1 : 'ASC';
$filter and $args->{sb} =~ s/score//;
my $link_sth = $links->query_sth ($args);
my $link_count = $links->hits;

Where exactly would I define it to search for only links with credit in links_Users, and also a bid registered in links_Links.

Any help is welcome...I'll just keep playing, and hope I get somewhere Unsure

Cheers
Quote Reply
Re: [Andy] PPC In reply to
Forget all that. I've worked a way around that now. The problem I am now having, is with some 'seemingly' easy code!

Code:
# if they wanna send an email to the link owner...
print $IN->header();
print $opts->{Send_Email};
if ($opts->{Send_Email}) {

print $IN->header();
print "sending";

my $msg = "Your credit has expired for username $link_owner";

require GT::Mail;
$GT::Mail::error ||= ''; # Silence -w
GT::Mail->send (
smtp => $CFG->{db_smtp_server},
sendmail => $CFG->{db_mail_path},
from => $CFG->{db_admin_email},
subject => "Credit Expired",
to => $user_rec->{Email},
msg => $msg,
debug => $Links::DEBUG
) or Links::fatal ("Unable to send mail: $GT::Mail::error");
}

}

That prints out: 1, but doesn't appear to even run the email sub. Anyone see why? I would have expected the sub to run, as a 'true' value is being sent back in that variable Unsure

Any help is much appreciated.

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: [Andy] PPC In reply to
Anyone? Unsure

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: [Andy] PPC In reply to
Never mind...stupid mistake! I was within another 'if' statement, which wasn't being called! Crazy

Got another one now though ;)

Code:
# get $total_received from prefix_PPC_Trans
my $table = $DB->table('PPC_Trans');
my $cond = GT::SQL::Condition->new ( 'Amount', '=', 'MAX(Amount)' );
my $sth = $table->select($cond);

# grab all the money, and add it up...
while (my $hit = $sth->fetchrow_hashref) {

$highest_topup = $hit->{Amount};

}

For some reason this is not picking up the MAX number entered in prefix_PPC_Trans (Amount)....anyone see why? Once I have this out of the way, I can start working on the user side of my PPC plugin, and then get ready for a beta release to select people Wink

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: [Andy] PPC In reply to
my $cond = GT::SQL::Condition->new ( 'Amount', '=', \'MAX(Amount)' );

Ivan
-----
Iyengar Yoga Resources / GT Plugins
Quote Reply
Re: [yogi] PPC In reply to
Cheers for the reply :)

That now gives me this problem;

Code:
System Information
======================================
Perl Version: 5.006
Links SQL Version: 2.1.0
DBI.pm Version: 1.30
Persistant Env: mod_perl (0) SpeedyCGI (0)
GT::SQL::error = Failed to execute query: 'SELECT * FROM lsql_PPC_Trans
WHERE ( Amount = MAX(Amount) ) ' Reason: Invalid use of group function
@INC =
/usr/local/psa/home/vhosts/ace-host.com/httpdocs/dumps/admin
/usr/lib/perl5/5.6.0/i386-linux
/usr/lib/perl5/5.6.0
/usr/lib/perl5/site_perl/5.6.0/i386-linux
/usr/lib/perl5/site_perl/5.6.0
/usr/lib/perl5/site_perl
.

What the hell is this group stuff its on about?

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: [Andy] PPC In reply to
Sorry....

Ivan
-----
Iyengar Yoga Resources / GT Plugins
Quote Reply
Re: [yogi] PPC In reply to
Sorry for what?

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: [Andy] PPC In reply to
I worked out a different way of doing it. I just did an ORDER BY Amount ASC, to get the highest value first, and then did a last in the while {} so that the first 'Amount' grabbed was the top one. Dirty, but it works Smile

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: [Andy] PPC In reply to
 
my $amount = $DB->table('PPC_Trans')->select( 'MAX(Amount)' )->fetchrow;

Last edited by:

Ian: Sep 19, 2002, 10:04 AM
Quote Reply
Re: [Ian] PPC In reply to
Mmmm...this is literally the last few lines of code I need to complete my PPC plugin! Can anyone see why this won't update?;

Code:
$user_links = $DB->table('PPC_Trans'); # first grab a new db handle
$user_links->update ( { Transaction_ID => $txn_id, PayPal_Email => $payer_email, Used => "1" }, { Date => $item_number }, { GT_SQL_SKIP_INDEX => 1 } )
or &error("Error: $GT::SQL::error");

As you can see, I have an 'or' statement in there, which doesn't seem to do anything. The only way I can think of, is that its not running the query, but how could I confirm this?

In my error logs, I have the following;

GT::SQL::Driver::MYSQL::sth (1794): Executing query: UPDATE lsql_PPC_Trans SET Transaction_ID = '4W615465CB7682458' , PayPal_Email = 'andy.newby@ace-installer.com' WHERE Date = 1032625682 from main::do_updates at ppc_paypal.cgi line 116

Should I have quotes around the 'Date' part?

Any help is much appreciated!

BTW: I have tried using Date => \"$item_number" and Date => "$item_number" , but the Date = part still seems to come out without the quotes Unsure

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: [Andy] PPC In reply to
Code:
$user_links = $DB->table('PPC_Trans'); # first grab a new db handle
$user_links->update ( {
Transaction_ID => $txn_id,
PayPal_Email => $payer_email,
Used => "1"
},
{
Date => $item_number
},
{
GT_SQL_SKIP_INDEX => 1
}
) or &error("Error: $GT::SQL::error");




OK, this may be off base, but there is something I don't like here,
about using the condition (especially) Date=>$item_number

This is not something that is guaranteed to be unique, and there
might be a problem in which record you are updating.

Could you be updating the record where Date=>$item_number, but it's
*not* the record you think you are updating?

Also, why is Date <=> $item_number ? Are you trying to obfuscate
your code <G>.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] PPC In reply to
True. What I may do is add an auto incrementing ID field, which could be used to track it.

>>>Also, why is Date <=> $item_number ? Are you trying to obfuscate your code <G>. <<<

Huh?

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: [Andy] PPC In reply to
You are putting $item_number into the Date field.

ie: Date => $item_number

I wondered why.

Also, since that is your "seek" (conditional) parameter, it seems doubly odd.

That's all.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [Andy] PPC In reply to
You definitely have a column where date is 1032625682 ?

You'd be much better using an auto-increment column though as you mentioned.

Last edited by:

Paul: Sep 21, 2002, 1:58 PM
Quote Reply
Re: [Paul] PPC In reply to
Ok...I'm getting there :D I now have a UniqueAutoIncrementing field called SetID. I've got the following code;

Code:
# ok, everything seems fine here, lets update the database, and also prefix_Users with their new purchase...
$user_links = $DB->table('PPC_Trans'); # first grab a new db handle
$user_links->update ( { Transaction_ID => $txn_id, PayPal_Email => $payer_email, Used => 1 }, { SetID => "$item_number" }, { GT_SQL_SKIP_INDEX => 1 } )
or &error("Error: $GT::SQL::error");

And in my error logs I get;

Quote:
GT::SQL::Driver::MYSQL::sth (13259): Executing query: SELECT Transaction_ID,Date,PayPal_Email,Used FROM lsql_PPC_Trans WHERE SetID = 16 from main::do_updates at ppc_paypal.cgi line 117
GT::SQL::Driver::MYSQL::sth (13259): Executing query: UPDATE lsql_PPC_Trans SET Transaction_ID = '9JC32187PN230270F' , PayPal_Email = 'andy.newby@ace-installer.com' from main::do_updates at ppc_paypal.cgi line 117

As you can see, the 'Used' field is set to 0 still...and in the query, located inred, it doesn't actually get even defined in the query.

I have tried;

Used => '1'
Used => "1"
Used => \"1"
Used => \'1'

and none of them seem to work...anyone got any ideas? Unsure

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: [Andy] PPC In reply to
Forget all those variations like \'1' and \"1", you don't need them. The following should work fine:

Code:
$user_links->update ( { Transaction_ID => $txn_id, PayPal_Email => $payer_email, Used => 1 }, { SetID => $item_number } )

If not then there is no such row to update.

What is in $item_number?

Last edited by:

Paul: Sep 22, 2002, 3:53 AM
Quote Reply
Re: [Paul] PPC In reply to
Thanks for the reply. $item_number is the ID number sent through from PayPal, as the purchased items tracking number.

The theory behind this plugin, is that they enter how much they want to top-up. They are then redirected to the appropriate PayPal URL, and an entry is added to the databasse for tracking purposes. When they get sent back after a successful putchase to ppc_paypal.cgi, that number is sent to the database, and it gets that purchase number, and updates the appropriate record to have their PayPal details, and setting SetID to to 1 (i.e it has been used).

I tried it with 1...but it still isn't working :-/ I know the query is working partially, as it updates ALL the other fields, except SetID Unsure

Any more ideas?

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: [Andy] PPC In reply to
Still not working Frown

Code:
GT::SQL::Driver::MYSQL::sth (20527): Executing query: SELECT Transaction_ID,Date,PayPal_Email,Used FROM lsql_PPC_Trans WHERE SetID = 17 from main::do_updates at ppc_paypal.cgi line 118
GT::SQL::Driver::MYSQL::sth (20527): Executing query: UPDATE lsql_PPC_Trans SET Transaction_ID = '31R00117V05419346' , PayPal_Email = 'andy.newby@ace-installer.com' from main::do_updates at ppc_paypal.cgi line 118
GT::SQL::Driver::MYSQL::sth (20527): Executing query: SELECT Username,Credit_Left FROM lsql_Users WHERE Username = 'admin' from main::do_updates at ppc_paypal.cgi line 122
GT::SQL::Driver::MYSQL::sth (20527): Executing query: UPDATE lsql_Users SET Credit_Left = Credit_Left + 0.5 WHERE Username = 'admin' from main::do_updates at ppc_paypal.cgi line 122

So close, yet so far to finishing this plugin!

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: [Andy] PPC In reply to
Anyone?

Code:
# ok, everything seems fine here, lets update the database, and also prefix_Users with their new purchase...
$user_links = $DB->table('PPC_Trans'); # first grab a new db handle
$user_links->update ( { Transaction_ID => $txn_id,
isUsed => "1",
PayPal_Email => $payer_email },
{ SetID => $item_number },
{ GT_SQL_SKIP_INDEX => 1 } ) or &error("Error: $GT::SQL::error");

I'm going to try it on another server, in case it is something to do with that...but I can't do that until my new server comes online... Unsure

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!