Return value from SQL database for Sagepay using PHP query

Question

The website I'm working on is sending the incorrect Billing Country information to Sagepay (this field is now mandetory on 3.00 update) and I'm trying to find a way to pass the correct information within the PHP file.

The current line to send this information is:

$crypt .= "&BillingCountry=".$orderArray["country"];

The problem with this is that the "country" field in the orderArray gives the country name as set in our CMS, not the ISO code that Sagepay requires.

The correct ISO codes are held within an SQL database so the information is available. The database table holds unique ID, name, ISO code, ISO number plus some other site-relavent information.

I need to replace the above crypt with this:

$crypt .="&BillingCountry=".$countryISO;

Where the $countryISO is added into the code as a query. This query should look in the ISO Code column (isocode) of the Countries database table and return the ISO code value in correspondance with the "country" name in the orderArray.

I've tried to put something together myself following php used in other files for the site which appear to be acheiving something similar. for example:

$countryISO = $dbA->query("SELECT isocode FROM $tableCountries WHERE name=".$orderArray["country"]);

I've also tried:

$countryISO = $dbA->retrieveAllRecordsFromQuery("SELECT isocode FROM $tableCountries WHERE name=".$orderArray["country"]);

There is a php file included on the site server that has the 'query' and 'retrieveAllRecordsFromQuery' functions, among others (e.g count, fetch, retrieveAllRecords) and also includes a funciton relating to $dbA which I believe connects to the database.

I need some advice as to how I can achieve pulling the ISO Code from the SQL database. Is my code anywhere near what I need?

Answer 1

I've managed to find a solution by using similar code used for another gateway for the website. This has solved the Billing Country issue and is now sending data successfully to Sagepay on the 3.00 protocol.

Solution:

    $countryMatch = $orderArray["country"];
    $cResult = $dbA->query("select * from $tableCountries where name=\"$countryMatch\"");
    if (count($cResult) > 0) {
        $cRecord = $dbA->fetch($cResult);
        $customerCountry = $cRecord["isocode"];
    } else {
        $customerCountry = "";
    }