-1

I've been happily parsing JSON files to a MYSQL DB but now have one with - and + characters, from what I have read these are not allowed to be used in $variables in PHP. so when I run the PHP script in get this error.

Parse error: syntax error, unexpected '=' in C:\xampp\htdocs\TestSite\frl2_odds_test.php on line 26

Once I remove the 3 variables from the PHP script with the -, + from the script it works and parses the JSON to the DB.

I have read this http://php.net/manual/en/language.variables.variable.php and this https://www.experts-exchange.com/questions/28628085/json-encode-fails-with-special-characters.html but haven't been able to get a solution. any help appreciated... thanks.. code as follows...

PHP

    <?php
$host = "localhost";
$username = "";
$password = "";
$dbname = "football";
$con = mysqli_connect($host, $username, $password, $dbname) or die('Error in Connecting: ' . mysqli_error($con));

$st = mysqli_prepare($con, 'INSERT INTO frl2_odd(match_id, odd_bookmakers, odd_date, odd_1, odd_x, odd_2, odd_1x, o+0.5, u+0.5, o+1.5, bts_yes, bts_no) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');

mysqli_stmt_bind_param($st, 'ssssssssssss', $match_id, $odd_bookmakers, $odd_date, $odd_1, $odd_x, $odd_2, $odd_1x, $o+0.5, $u+0.5, $o+1.5, $bts_yes, $bts_no);

$filename = 'json/testodds.json';
$json = file_get_contents($filename);
$data = json_decode($json, true);

foreach($data as $row) {
$match_id = $row['match_id'];
$odd_bookmakers = $row['odd_bookmakers'];
$odd_date = $row['odd_date'];
$odd_1 = $row['odd_1'];
$odd_x = $row['odd_x'];
$odd_2 = $row['odd_2'];
$odd_1x = $row['odd_1x'];
$o+0.5 = $row['o+0.5'];
$u+0.5 = $row['u+0.5'];
$o+1.5 = $row['o+1.5'];
$bts_yes = $row['bts_yes'];
$bts_no = $row['bts_no'];

mysqli_stmt_execute($st);
}
mysqli_close($con);
?>

JSON

[
{
"match_id": "345064",
"odd_bookmakers": "Interwetten.es",
"odd_date": "2018-10-24 04:39:45",
"odd_1": "2.05",
"odd_x": "2.95",
"odd_2": "3.95",
"odd_1x": "",
"odd_12": "",
"odd_x2": "",
"ah-4.5_1": "",
"ah-4.5_2": "",
"ah-4_1": "",
"ah-4_2": "",
"ah-3.5_1": "",
"ah-3.5_2": "",
"ah-3_1": "",
"ah-3_2": "",
"ah-2.5_1": "",
"ah-2.5_2": "",
"ah-2_1": "",
"ah-2_2": "",
"ah-1.5_1": "",
"ah-1.5_2": "",
"ah-1_1": "3.00",
"ah-1_2": "1.33",
"ah0_1": "1.42",
"ah0_2": "2.65",
"ah+0.5_1": "",
"ah+1_1": "",
"ah+1_2": "",
"ah+1.5_1": "",
"ah+1.5_2": "",
"ah+2_1": "",
"ah+2_2": "",
"ah+2.5_1": "",
"ah+2.5_2": "",
"ah+3_1": "",
"ah+3_2": "",
"ah+3.5_1": "",
"ah+3.5_2": "",
"ah+4_1": "",
"ah+4_2": "",
"ah+4.5_1": "",
"ah+4.5_2": "",
"o+0.5": "",
"u+0.5": "",
"o+1": "",
"u+1": "",
"o+1.5": "",
"u+1.5": "",
"o+2": "",
"u+2": "",
"o+2.5": "2.45",
"u+2.5": "1.48",
"o+3": "",
"u+3": "",
"o+3.5": "",
"u+3.5": "",
"o+4": "",
"u+4": "",
"o+4.5": "",
"u+4.5": "",
"o+5": "",
"u+5": "",
"o+5.5": "",
"u+5.5": "",
"bts_yes": "2.05",
"bts_no": "1.67"
}
]
Paul mac
  • 11
  • 2
  • 2
    `$o+0.5`, `$u+0.5` and `$o+1.5` are not valid PHP variable names. You need to call them something else e.g. `$o_plus_0_5`. See the [manual](http://php.net/manual/en/language.variables.basics.php) – Nick Oct 27 '18 at 23:26
  • When the solution is "variable variables" then 999 times out of 1000 (if not higher) you should stop and rethink your process. Variable variables are basically improperly stored associative arrays. I will urge you in advance to not use variable variables in your project(s). – mickmackusa Oct 28 '18 at 00:42
  • @nick... this is when I need to put the laptop down and go walk on the beach... your correct of course... – Paul mac Oct 28 '18 at 08:14

1 Answers1

0

I'll recommend some validation and default fallback values so that your INSERT always has enough values to chew on...

My line of preparation removes unwanted elements, ordered the desired elements, and reindex the array.

Untested Code: (Demo of Preparation)

$cols = array_fill_keys(['match_id', 'odd_bookmakers', 'odd_date', 'odd_1', 'odd_x', 'odd_2', 'odd_1x', 'o+0.5', 'u+0.5', 'o+1.5', 'bts_yes', 'bts_no'], null);
$types = str_repeat('s', sizeof($cols));

foreach(json_decode($json, true) as $row) {
    $row = array_values(array_replace($cols, array_intersect_key($row, $cols)));
    mysqli_stmt_bind_param($st, $types, ...$row);  // splat operator needs indexed elements to unpack
    mysqli_stmt_execute($st);
}

*I didn't test the implementation of the mysqli calls, I don't typically use procedural syntax.

*I think you'll need to backtick-wrap your funky column names as well.

Okay, I tested this on my localhost to be successful:

if (!$db = new mysqli("localhost", "root", "", "db")) {  // declare and check for a falsey value
    echo "Connection Failure"; // $db->connect_error <-- never show actual error details to public
} else {
    $json = file_get_contents('json/testodds.json');
    $columns = ['match_id', 'odd_bookmakers', 'odd_date', 'odd_1', 'odd_x', 'odd_2', 'odd_1x', 'o+0.5', 'u_+0.5', 'o+1.5', 'bts_yes', 'bts_no'];
    $cols_count = sizeof($columns);
    $cols_defaults = array_fill_keys($columns, null);
    $quoted_cs_cols = implode(',', array_map(function($v){return "`$v`";}, $columns));
    $cs_placeholders = implode(',', array_fill(0, $cols_count, "?"));
    $cols_types = str_repeat('s', $cols_count);
    $sql = "INSERT INTO frl2_odd ($quoted_cs_cols) VALUES ($cs_placeholders)";
    // echo $sql , "<br>";
    if (!$stmt = $db->prepare($sql)) {
        echo "Error @ prepare"; // $db->error; <-- never show actual error details to public
    } else {
        foreach(json_decode($json, true) as $i => $row) {
            $row = array_values(array_replace($cols_defaults, array_intersect_key($row, $cols_defaults)));
            if (!$stmt->bind_param($cols_types, ...$row) || !$stmt->execute()) {
                echo "Error @ execute";  //$stmt->error;  <-- never show actual error details to public
            } else {
                echo "Row Index $i successfully added.<br>"; 
            }
        }
    }
}

The beauty of $columns is that when you want to adjust your desired columns, you only need to modify one line of code and synchronize your db table.

mickmackusa
  • 43,625
  • 12
  • 83
  • 136