Inserting JSON data into MySQL table

Question

I have been following a solution (How to insert json array into mysql database) into how to insert JSON data into a MySQL table. The code seems to run okay but it is still returning an empty data set.

I'm currently testing a hypothesis and my JSON file has 100,000 records in it. I have created a similar JSON file with just three records thinking that the size of the file may have been impeding it but that didn't work either.

I have checked my database name, table name and row names but is correct with the code shown. Any pointers?

<?php 
$json = file_get_contents('E:\xampp\htdocs\MOCK_DATA.json');
$obj = json_decode($json, true);

$connection = mysqli_connect("localhost", "root", "");
mysqli_select_db($connection, "et_test") or die('Couldnt connect database');

foreach($obj as $item)
{
    mysqli_query($connection, "INSERT INTO 'et_test'.'test' (first_name, last_name, colour, country, city)
VALUES ('".$item['first_name']."','".$item['last_name']."','".$item['colour']."','".$item['country']."','".$item['city']."')");
}
mysqli_close($connection);
?>

escape or don't use the `'` in your `die` statement. eg 'No Connection to Database' — dops, Nov 12 '14 at 19:35
Ah that was a typo while typing out code. Fixed now. I used error_reporting(E_ALL);, but still doesn't bring anything up — SPeoples, Nov 12 '14 at 19:37
`'et_test'.'test'` use backticks `\`` - wrong identifiders, as stated. `or die(mysqli_error($connection))` to `mysqli_query()`, in order to get the "real" error ;) which your link clearly shows in the other OP's question. — Funk Forty Niner, Nov 12 '14 at 19:37
It is funny that you didn't use any error checking and the error checking would have revealed to you the problem that you had with your query. Well, maybe not funny. Help me out here @Fred-ii- — Jay Blanchard, Nov 12 '14 at 19:39
@JayBlanchard I believe I already have ;) well, the OP anyway lol two birds with one stone? — Funk Forty Niner, Nov 12 '14 at 19:40
`Mysqli` won't throw PHP errors if it encounters an SQL error. You need to check for errors explicitly. look at the return value of `mysqli_query()`, and if it's `false` report the value of `mysqli_error()` — , Nov 12 '14 at 19:40
Ahhh thank you Fred -ii-! I knew it was something simple! Hopefully I'll get to that stage where I can find stupid errors that easily. Thanks bud! — SPeoples, Nov 12 '14 at 19:40
Want the question closed and marked as solved? If not, you can just delete your question since it's been solved in comments. — Funk Forty Niner, Nov 12 '14 at 19:41
I'd like it marked as solved actually, ya never know, I might need to refer to this again haha :) — SPeoples, Nov 12 '14 at 19:44

score 1 · Accepted Answer · edited May 23 '17 at 10:32

As per OP's wish to close the question and be marked as solved:

This is the problematic piece of code'et_test'.'test'

Use backticks, because using quotes around table names are the wrong identifiders:

http://dev.mysql.com/doc/refman/5.0/en/identifier-qualifiers.html

SQL

mysqli_query($connection, "INSERT INTO `et_test`.`test` ...

Use or die(mysqli_error($connection)) to mysqli_query(), in order to get the "real" error.

Consider getting into using prepared statements, or PDO with prepared statements.

As it stands, your present code is open to SQL injection.

Inserting JSON data into MySQL table

1 Answers1