MySQL PDO inserting wrong values (CSV data source)

Question

I have a table like so.

CREATE TABLE `GBPAUD` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `currency_pair` varchar(11) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `sell` float NOT NULL,
  `buy` float NOT NULL,
  `spread` float NOT NULL,
  PRIMARY KEY (`id`)
)

I have written a script that opens CSV files, itterates the rows and inserts them into the table.

After the script has run and i look in the database the table appears like this.

The code that inserts the data looks like so.

private function insert($currencyPair, $date, $buy, $sell, $spread){
    $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $result_set = $this->pdo->prepare("INSERT INTO ".str_replace('_', '', $this->instrument)." (currency_pair, date, sell, buy, spread) VALUES (:currency_pair, :date, :sell, :buy, :spread)"); 
    $result = $result_set->execute(array(':currency_pair' => $currencyPair, ':date' => $date, ':buy' => (float)$buy, ':sell' => (float)$sell, 'spread' => (float)$spread)); 
}

I print out the values just before the exacute stament and the values are correct.

Array
(
    [:currency_pair] => GBP/AUD
    [:date] => 2007-11-01 14:06:04.000
    [:buy] => 2.273400
    [:sell] => 2.272500
    [spread] => 0
)

Anyone have any idea why its not inserting my data?

EDIT: DB connection code

define("DSN", "mysql:dbname=rates_test;host=localhost;port=3306");
define("USER", "blah");
define("PASS", "blah");
$pdo = new PDO(DSN, USER, PASS);

EDIT 2

I have taken the insert out of the function and added to the while loop im doing so you can see whats happening.

while( false !== ( $data = fgetcsv($file) ) ) {
        if(array(null) !== $data){ //skip blank lines
            $currencyPair = $data[$column['columns']['instrument']];
            $date = $data[$column['columns']['date']];
            $sell = $data[$column['columns']['sell']];
            $buy = $data[$column['columns']['buy']];
            $spread = (float)$buy - (float)$sell;

            echo "value => " . $currencyPair . "\r\n";
            echo "value => " . $date . "\r\n";
            echo "value => " . $sell . "\r\n";
            echo "value => " . $buy . "\r\n";
            echo "value => " . $spread . "\r\n";

            echo var_dump(array(':currency_pair' => $currencyPair, ':date' => $date, ':buy' => (float)$buy, ':sell' => (float)$sell, ':spread' => (float)$spread));                     

            $result_set = $this->pdo->prepare("INSERT INTO ".str_replace('_', '', $this->instrument)." (currency_pair, date, sell, buy, spread) VALUES (:currency_pair, :date, :sell, :buy, :spread)");         
            $result = $result_set->execute(array(':currency_pair' => $currencyPair, ':date' => $date, ':buy' => (float)$buy, ':sell' => (float)$sell, ':spread' => (float)$spread));    
        }   
}

and here is the result

value => GBP/AUD
value => 2007-10-28 21:21:48.000
value => 2.229000
value => 2.229900
value => 0

array(5) {
  [":currency_pair"]=> string(15) "GBP/AUD"
  [":date"]=> string(47) "2007-10-28 21:21:48.000"
  [":buy"]=> float(0)
  [":sell"]=> float(0)
  [":spread"]=> float(0)
}

Edit 3:

I solved it, but its a bit hacky. Also i have no control over over these CSV's so any invisible characters can be in it. Can anyone please confirm if this is enough to handle any invisible characters there may be? ( i have not put this into a function yet, but i am doing the same for ever variable im inserting)

    $buy = preg_replace('/[\x00-\x1F\x80-\xFF]/', '', $buy);
    $buy = (strpos($buy, ':') && strpos($buy, '-')) ? array_shift(explode('.', $buy)) : $buy;

I do not like what i am doing with the date, but i cannot think of any other ways (i cannot parse a legitamate date straight from the CSV because of the invisable characters) even without the invisible characters removed i cannot parse a date because some feilds have more than 6 micro seconds (PHP can only handel 6)

May be because 'spread' => (float)$spread). You missed :. Change it to like ':spread' => (float)$spread) — Nana Partykar
– Nana Partykar, Commented Aug 16, 2016 at 10:27
Thanks for pointing that out, i'm surprised PDO didn't complain about it, but unfortunately this does not fix the issue. — Jay Povey
– Jay Povey, Commented Aug 16, 2016 at 10:33
You are changing table name by replacing _. So, is table name correct? Have you checked it? — Nana Partykar
– Nana Partykar, Commented Aug 16, 2016 at 10:34
private function insert($currencyPair, $date, $buy, $sell, $spread){ $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $result_set = $this->pdo->prepare("INSERT INTO GBPAUD (currency_pair, date, sell, buy, spread) VALUES (:currency_pair, :date, :sell, :buy, :spread)"); $result = $result_set->execute(array(':currency_pair' => $currencyPair, ':date' => $date, ':buy' => (float)$buy, ':sell' => (float)$sell, ':spread' => (float)$spread)); } — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Aug 16, 2016 at 10:36

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

I just wrapped a bit of code around your posted code and it works fine. I did not even change the code for the spread to :spread suggestion.

I did however add a try/catch block as I see you set the mode to throw Exceptions, but the catch block was never activated.

<?php

class tst
{
    private $pdo;
    private $instrument = 'gbp_aud';

    public function __construct()
    {
        /*** mysql hostname ***/
        $hostname = 'localhost';
        /*** mysql username ***/
        $username = 'test';
        /*** mysql password ***/
        $password = 'test';
        /*** database name ***/
        $dbname = 'test';

        try {

            $this->pdo = new PDO("mysql:host=$hostname;dbname=$dbname;charset=UTF8", $username, $password);
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            $this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
            $this->pdo->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND,'SET NAMES UTF8');

        } catch (PDOException $e) {
            echo 'Connection failed: ' . $e->getMessage();
            exit;
        }

    }

    private function insert($currencyPair, $date, $buy, $sell, $spread){
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        try {
            $result_set = $this->pdo->prepare("INSERT INTO ".str_replace('_', '', $this->instrument)." (currency_pair, date, sell, buy, spread) VALUES (:currency_pair, :date, :sell, :buy, :spread)");
            $result = $result_set->execute(array(':currency_pair' => $currencyPair, ':date' => $date, ':buy' => (float)$buy, ':sell' => (float)$sell, 'spread' => (float)$spread));
        }
        catch(PDOException $e) {
            print_r($this->pdo->errorInfo());
            exit;
        }
    }

    public function doit($currencyPair, $date, $buy, $sell, $spread){
        $this->insert($currencyPair, $date, $buy, $sell, $spread);
    }
}

$test = new tst();

$currencyPair   = 'GBP/AUD';
$date           = '2007-11-01 14:06:04.000';
$buy            = 2.273400;
$sell           = 2.272500;
$spread         = 0;
$test->doit($currencyPair, $date, $buy, $sell, $spread);

$currencyPair   = 'GBP/AUD';
$date           = '2007-11-02 13:06:04.000';
$buy            = 2.276600;
$sell           = 2.278800;
$spread         = 0.4;
$test->doit($currencyPair, $date, $buy, $sell, $spread);

Results:

I just read your last question, and I have to assume that you still have some odd characters in your data feed to this process.

Do a var_dump() of the array that you feed to the ->execute() statement, that will likely show more than a simple print_r()

UPDATE

The issue is that the older files are encoded in UNICODE and the new files are simple ASCII single byte encoded.

I converted the Older files offline so to speak back to ASCII and this code loaded an old and new file quite happily

The only remaining complication if that the older files dont have column names on row 1 and the field order is a little different, but thats just a FLOC. See the code below.

<?php

class tst
{
    private $pdo;
    private $instrument = 'gbp_aud';

    public function __construct()
    {
        /*** mysql hostname ***/
        $hostname = 'localhost';
        /*** mysql username ***/
        $username = 'test';
        /*** mysql password ***/
        $password = 'test';
        /*** database name ***/
        $dbname = 'test';

        try {

            $this->pdo = new PDO("mysql:host=$hostname;dbname=$dbname;charset=UTF8", $username, $password);
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            $this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
            $this->pdo->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND,'SET NAMES UTF8');

        } catch (PDOException $e) {
            echo 'Connection failed: ' . $e->getMessage();
            exit;
        }

    }

    private function insert($currencyPair, $date, $buy, $sell, $spread){
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        try {
            $result_set = $this->pdo->prepare("INSERT INTO ".str_replace('_', '', $this->instrument)." (currency_pair, date, sell, buy, spread) VALUES (:currency_pair, :date, :sell, :buy, :spread)");
            $result = $result_set->execute(array(':currency_pair' => $currencyPair, ':date' => $date, ':buy' => (float)$buy, ':sell' => (float)$sell, 'spread' => (float)$spread));
        }
        catch(PDOException $e) {
            print_r($this->pdo->errorInfo());
            exit;
        }
    }

    public function doit($currencyPair, $date, $buy, $sell, $spread){
        $this->insert($currencyPair, $date, $buy, $sell, $spread);
    }
}

$test = new tst();

// One old and one new format file
$files = array('GBP_AUD_Week1.csv', 'GBP_AUD_Week5.csv');

foreach ($files as $file) {
    $old_format = true;
    if (($handle = fopen($file, "r")) !== FALSE) {
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            // test old or new file layout
            if ( $data[0] == 'lTid' ) {
                // New file layout
                $old_format = false;
                // Skip the title row
                 continue;
            }
            if ( $old_format ) {
                $test->doit($data[1], $data[2], $data[3], $data[4], $data[4]-$data[3]);
            } else {
                $test->doit($data[2], $data[3], $data[4], $data[5], $data[5]-$data[4]);
            }
        }
        fclose($handle);
    }
}

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 16, 2016 at 11:22

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Jay Povey Over a year ago

you are probably right, its is probably to do with odd characters, seen as your code works. do you think addcslashes() will be enough to show all of the invisable characters

Jay Povey Over a year ago

Ah right ok, this is the main issue im having, I have no control over the CSV. This script is scraping mass amount of data from a website. ratedata.gaincapital.com the data on this website is very inconsistant. I need to figure out a way to strip literally everything other than the visible data as i never know what the input will be.

Jay Povey Over a year ago

sure here is a link to google drive. I can not thank you enough for this help. what i am doing is very time sensative and iv been stuck all day. <3

Jay Povey Over a year ago

I have solved the issue with some regex (its very hacky though) i have added another edit. Can you please take a look

Jay Povey Over a year ago

Thats so strange, so do you think my regex will be enough to resolve this issue? also do you have a better way i can handel this date? (more info in edit 3)

|

Collectives™ on Stack Overflow

MySQL PDO inserting wrong values (CSV data source)

1 Answer 1

UPDATE

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related