October 19, 2008

Joomla affiliate link cloaker

Filed under: Internet,Making money,PHP and MySQL — Joe Brewer @ 6:58 pm

So here it is. Version 3 of the affiliate link cloaker for Joomla 1.5.x

This new version works in a completely different way from the last version which will stop the problems caused by the WYSIWYG editors. However, there are other issues which I will explain about later. For now though, here is how to use it:


June 3, 2008


Filed under: Making money,Marketing,PHP and MySQL — Joe Brewer @ 5:41 am

Coming soon…. a way to integrate phpbay pro into any site (including drupal, joomla and other content management sites) with AuctionAds/ShoppingAds formatting, but with more flexible search options for the ads.

May 12, 2008

Optimising your ebay affiliate profits

Filed under: Internet,Making money,PHP and MySQL — Joe Brewer @ 6:51 pm

A few months ago I came across phpBay – a WordPress plugin and API that integrates ebay listings easily into your website. When a user clicks on a product and places a winning bid, you receive a commission from Ebay.

One feature that I found that was missing was being able to show users local auction listings, for example show people from America US listings or people from the England UK listings. However this is now possible to do with my country class, and once installed will enable you to maximise your affiliate profits. To acomplish this you will need to purchase the web lookup service from MaxMind. The class can be downloaded here.

To make this work with wordpress, simply unpack country.php and geo.php, edit the geo.php so that it contains the key that you receive from MaxMind and upload the files to wp-content/plugins/phpBay/ and the phpBay plugin will take care of the rest. This will work on version 3.0 of phpBay

If you need to get this working on a non wordpress site using the API, then you will just need the country.php which contains the country class. To see it working, see my site DVD For A Dollar. Here is how I accomplished the country detection on that site.

At the top of each file I start a session using session_start() so that if a user browses more than one page of a site then it will be able to retreive the country from MaxMind once and then use it on all subsequent pages. Then I use the following code to initiate the class and retreive the country.


  1. include( ‘country.php’ );
  2. $ebayCountry = new country();
  4. if ( !isset( $_SESSION[‘country’] ) )
  5. {
  6. // replace the ‘x’s on the next line with your MaxMind key
  7. $ebayCountry->licenseKey = "xxxxxxxx";
  8. $ebayCountry->getCountryFromWeb();
  10. $_SESSION[‘country’] = $ebayCountry->ISO;
  11. } else
  12. {
  13. $ebayCountry->ISO = $_SESSION[‘country’];
  14. $ebayCountry->setReturnArray();
  15. }


Now you can initiate the phpBay class and set the country parameters like this:


  1. $ebay = new ebay();
  3. $ebay->eb_siteId = $ebayCountry->ReturnArray[ "siteId" ];
  4. $ebay->eb_language = $ebayCountry->ReturnArray[ "language" ];

If you want a combo box that displays a list of countries that defaults to the local ebay like the one on DVD For A Dollar, place the following code above the code that checks if the country session variable has been set:


  1. if ( isset( $_POST[‘country’] ) && strlen( $_POST[‘country’] ) == 2 )
  2. $_SESSION[‘country’] = $_POST[‘country’];

Here is the code that draws the combo box itself:


  1. function DrawCountryCombo() {
  2. $Countries = array(    "US" => "US Auctions",
  3. "AU" => "Australian Auctions",
  4. "AT" => "Austrian Auctions",
  5. "BE" => "Belgian Auctions",
  6. "CA" => "Canadian Auctions",
  7. "FR" => "French Auctions",
  8. "DE" => "German Auctions",
  9. "HK" => "Hong Kong Auctions",
  10. "IN" => "Indian Auctions",
  11. "IE" => "Irish Auctions",
  12. "IT" => "Italian Auctions",
  13. "NL" => "Dutch Auctions",
  14. "PL" => "Polish Auctions",
  15. "SG" => "Singapore Auctions",
  16. "ES" => "Spanish Auctions",
  17. "CH" => "Swiss Auctions",
  18. "GB" => "UK Auctions"
  19. );
  21. $form = ‘<form method="POST" action="">’ . "\r\n";
  22. $form .= ‘<select onchange="javascript:submit();" id="countrycombo" name="country">’ .
  24. "\r\n";
  26. foreach( $Countries AS $Key => $Value )
  27. {
  28. $form .= ‘<option’;
  29. $form .= ($_SESSION[‘country’] == $Key) ? ‘ selected’ : ;
  30. $form .= ‘ value="’ . $Key . ‘">’ . $Value . ‘</option>’ . "\r\n";
  31. }
  33. $form .= ‘</select>’ . "\r\n" . ‘</form>’ . "\r\n";
  34. return $form;
  35. }

To draw the combo box, simply use the following code:


  1. echo DrawCountryCombo();

And that’s all there is to adding country detection to your ebay affiliate sites. Huge thanks to Wade who wrote the phpbay class for his support with this project.

If you find this code useful, feel free to buy me a beer.

March 20, 2008

How to debug SQL statements for php websites

Filed under: PHP and MySQL — Joe Brewer @ 6:24 am

About a week ago I asked my friend Simon to help me with what I belived was a particulaly difficult problem with a SQL statement. As it happened, I had made a stupid mistake, and that was that I had used Microsoft Access to help me with building queries. The trouble with Access is that the queries that they produce are not optimised, and they are often over complicated. After a quick SQL tutorial from Simon, I was hungry to practise my new skill, so I decided to rewrite the SQL statements in my Free UK dating site.

It’s been roughly 6 months since I did any significant work on the site, and I knew most of the SQL statements were generated from Access, and a few of them even had some hugely inefficient sub queries thrown in by me. All of the work has been done now, which is why I have been a bit quiet here, and there have been some major improvements – there are now no sub-queries, and many queries have been combined. The main benefit is that many pages that had around 30+ queries and now around 5 queries.

To help me debug some of the queries I wrote the following code that displays the eroneous SQL statement and highlights the part of the statement with the error. This makes it far easier to see where the error is if you have a large query.

function DisplayQuery( $Query, $ExtraText = '' )

$search = array(    'FROM',
'LEFT &lt;br /&gt;JOIN',

$replace = array(    '&lt;br /&gt;FROM',
'&lt;br /&gt;WHERE',
'&lt;br /&gt;AND',
'&lt;br /&gt;JOIN',
'&lt;br /&gt;LEFT JOIN',
' &lt;br /&gt;ORDER BY' );

if ( strlen($ErrorText = @mysql_error()) &gt; 0 )

$ErrorNumber = mysql_errno();
$FirstQuotePos = strpos( $ErrorText, "'" );
$LastQuotePos = strpos( $ErrorText, "'", $FirstQuotePos+1 );
$QuerySubString = '';

if ( $FirstQuotePos == $LastQuotePos &amp;&amp; false !== $FirstQuotePos &amp;&amp; false !== $LastQuotePos )
$QuerySubString = substr( $ErrorText, $FirstQuotePos+1, strlen( $ErrorText ) - $FirstQuotePos - 1 );
} elseif ( $ErrorNumber != 1146 &amp;&amp; $FirstQuotePos &lt; $LastQuotePos &amp;&amp; false !== $FirstQuotePos &amp;&amp; false !== $LastQuotePos )
$QuerySubString = substr( $ErrorText, $FirstQuotePos+1, $LastQuotePos - $FirstQuotePos - 1 );
} elseif ( $ErrorNumber == 1146 &amp;&amp; $FirstQuotePos &lt; $LastQuotePos &amp;&amp; false !== $FirstQuotePos &amp;&amp; false !== $LastQuotePos )
$QuerySubString = substr( $ErrorText, $FirstQuotePos+1, $LastQuotePos - $FirstQuotePos - 1 );
$QuerySubString = substr( $QuerySubString, strpos( $QuerySubString, '.' )+1, strlen( $QuerySubString ) - strpos( $QuerySubString, '.' ) );

$Query = str_replace( $QuerySubString, '&lt;span style="background:yellow;"&gt;' . $QuerySubString . '&lt;/span&gt;', $Query );


return '&lt;div style="border: 2px red solid; padding: 4px;"&gt;' . str_replace( $search, $replace, $Query ) . $ExtraText . '&lt;/div&gt;';


Here’s how I use this in my projects:

function exec_sql( $Query, $ShowError = false )

if ( !($result = @mysql_query( $Query ) ) )
if ( $ShowError )
echo DisplayQuery( $Query, '&lt;br /&gt;&lt;br /&gt;' . mysql_error() );

return $result;


If a query is passed to exec_sql() it will die silently unless the $ShowError parameter is set to true. The error will display with the query and error message, and the erroneous part of the query will be highlited:

SELECT membertable.UserID, MemberName, Sex, Birthday, Orientation, LookingFor, Location, MaritalStatus, Smokes, Drinks, Education, Height, Weight, EyeColor, HairColor, Glasses, photonumber, approved
FROM mem22bertable
LEFT JOIN phototable ON (membertable.UserID = phototable.UserID)
WHERE membertable.UserID=301
AND ((phototable.mainphoto = 1 OR phototable.mainphoto IS NULL)
AND (approved = 1 OR approved IS NULL))

Table ‘brewster_dating.mem22bertable’ doesn’t exist

NOTE: I can’t find a plugin for coloured syntax highlighting at the moment that doesnt display php without any issues – some of the characters above are displayed as html entities. If you want to use the code, click on “PLAIN TEXT”, and the code will be converted into plain text which you can copy and use. If anyone knows of a good coloured syntax highliter for wordpress which works with the visual editor please let me know!

« Newer Posts

Powered by WordPress