0

I'm having a problem running a query and just wondering can someone point out my problem. I think I know what's causing it but not sure how to resolve it.

I have a JSON and am trying to put it into a MySQL database. The database fields are arrange in the same order as the fields in the JSON.

I am getting the error Column not found: 1054 Unknown column '-' in 'field list' when I delimit the JSON fields with ``` (accent grave, I think?). When I replace the delimiter with a standard ' I get the following error Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near because of of my entries has an ' in it.

Not sure how to fix it because the first option seems to be my preferred solution as one entry from 99 has an ' in the field value.

Code so far...

I attempted to solve the problem by using the str_replace function as an interim but I'm sure there's a better way.

<?php

$data = file_get_contents('JSON DATA');

$array = json_decode($data, true);
$rows = array();   

print_r ($array['results']);

foreach($array['results'] as $result){
    foreach ($result as $key => $value)
        $rows[$i][] = "'" . $value . "'";
        $i++;
}

$hostname = '';                 // write the rest of your query
$database = '';
$username = '';
$password = '';

try{
    $dbh = new PDO("mysql:host=localhost;dbname=dbname", $username, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $dbh->exec("TRUNCATE TABLE `import_io`");

    $index = 0;

    foreach ($rows as $row) {
        $row = implode(",",$row); //making a string from an array with each item separated by comma
        $row = str_replace ("O'Meara","O\'Meara",$row);
        //print_r ($array['results'][$index]);
        echo ('<br>');
        $query = "INSERT INTO import_io (`total`, `thru`, `strokes`, `name`, `name/_text`, `name/_source`) VALUES ($row)";
        print_r ($query);
        echo('<br>');
        $count = $dbh->prepare($query);
        $count->execute();
        $index++;
    }

    $change_par = "UPDATE `dbname`.`import_io` SET `total` = REPLACE(`total`, 'E', '0') WHERE `total` LIKE 'E'";
    $count = $dbh->prepare($change_par);
    $count->execute();

    $change_dash = "UPDATE `dbname`.`import_io` SET `total` = REPLACE(`total`, '-', '0') WHERE `total` LIKE '-'";
    $count = $dbh->prepare($change_dash);
    $count->execute();

    $dbh = null;// close the database connection
}catch(PDOException $e){
    echo $e->getMessage();
}

?>
Alan
  • 311
  • 6
  • 14
  • I can see that's it answered already but I don't understand what I need to do to fix it, is possible to get help with a solution or do I need to work it out? – Alan Apr 07 '15 at 20:45
  • You at least need to build some code and tell where you have the problem. So yes, you have to work it out. – Norbert Apr 07 '15 at 20:57
  • So I have code already written, do I just post it all up here? My JSON feed has changed and that's what's causing me the problem. – Alan Apr 07 '15 at 21:05
  • Yes, because at this moment your question is not identifiable. – Norbert Apr 07 '15 at 21:17
  • Ok I understand now, I have added what I had done but not sure how correct it is as I've been "hacking" at it. – Alan Apr 07 '15 at 21:39
  • VALUES ($row) is very bad coding. Replace with php PDO and use bind. Then the characters will not be an issue. (And that is the link John inserted) – Norbert Apr 07 '15 at 22:43

0 Answers0