Saving json data into a MySql Table

[IvHysenbelli]

I'm trying to save some json data to an mysql table through php but I do have some issues I'm just stuck into the loop and the validation doesn't go like I want to. These are some dummy data of the json Object

 

[entiteteTeReja] => Array
(
            [cmimeReja] => Array
                (
                    [0] => Array
                        (
                            [KODNIVELCMIMI] => N1
                            [KODARTIKULLI] => BOBLT0009
                            [KODNJESIA1] => cope
                            [MONEDHAKOD] => LEK
                            [DTFILLIMIT] => 2015-01-01T00:00:00.000Z
                            [DTMBARIMIT] => 9999-12-31T00:00:00.000Z
                            [CMIMI] => 3450
                            [CMIMI2] => 3450
                            [KODNJESIA2] => cope
                            [DTMODIFIKIM] => 2015-05-11T16:42:45.046Z
                            [IDSTATUSDOK] => 1
                            [BRUTONETONIVELCMIMI] => 1
                            [NIVELCMIMIBAZE] => 
                            [KOHEFILLIMI] => 1900-01-01T00:00:00.000Z
                            [KOHEMBARIMI] => 1900-01-01T23:59:59.000Z
                        )
			.
			.
			.
			.

                    [834] => Array
                        (
                            [KODNIVELCMIMI] => N2
                            [KODARTIKULLI] => BOBLT0009
                            [KODNJESIA1] => cope
                            [MONEDHAKOD] => LEK
                            [DTFILLIMIT] => 2015-01-01T00:00:00.000Z
                            [DTMBARIMIT] => 9999-12-31T00:00:00.000Z
                            [CMIMI] => 3450
                            [CMIMI2] => 3450
                            [KODNJESIA2] => cope
                            [DTMODIFIKIM] => 2015-05-12T16:58:24.746Z
                            [IDSTATUSDOK] => 1
                            [BRUTONETONIVELCMIMI] => 1
                            [NIVELCMIMIBAZE] => 
                            [KOHEFILLIMI] => 1900-01-01T00:00:00.000Z
                            [KOHEMBARIMI] => 1900-01-01T23:59:59.000Z
                        )
                        .
			.
			.
			.

                    [834] => Array
                        (
                            [KODNIVELCMIMI] => N3
                            [KODARTIKULLI] => BOBLT0009
                            [KODNJESIA1] => cope
                            [MONEDHAKOD] => LEK
                            [DTFILLIMIT] => 2015-01-01T00:00:00.000Z
                            [DTMBARIMIT] => 9999-12-31T00:00:00.000Z
                            [CMIMI] => 3450
                            [CMIMI2] => 3450
                            [KODNJESIA2] => cope
                            [DTMODIFIKIM] => 2015-05-12T16:58:24.746Z
                            [IDSTATUSDOK] => 1
                            [BRUTONETONIVELCMIMI] => 1
                            [NIVELCMIMIBAZE] => 
                            [KOHEFILLIMI] => 1900-01-01T00:00:00.000Z
                            [KOHEMBARIMI] => 1900-01-01T23:59:59.000Z
                        )

lso this is the Php Script that I tried so far,
 

foreach($obj['entiteteTeReja']['cmimeReja'] as $key => $x){

	$query = "SELECT COUNT(*) AS nr FROM cmimedatatable WHERE KODARTIKULLI = '".$x['KODARTIKULLI']."' ";
	$result = $mysqli->query($query);
    
    if( nr == 1){
		if($x['KODNIVELCMIMI'] == 'N1'){
			$query = "UPDATE cmimedatatable SET C1 = ".$x['CMIMI']." WHERE KODARTIKULLI = '".$x['KODARTIKULLI']."' ";
		}
		else if($x['KODNIVELCMIMI'] == 'N2'){
			$query = "UPDATE cmimedatatable SET C2 = ".$x['CMIMI']." WHERE KODARTIKULLI = '".$x['KODARTIKULLI']."' ";		
		}
		else if($x['KODNIVELCMIMI'] == 'N3'){
			$query = "UPDATE cmimedatatable SET C3 = ".$x['CMIMI']." WHERE KODARTIKULLI = '".$x['KODARTIKULLI']."' ";
		}
		else if($x['KODNIVELCMIMI'] == 'N4'){
			$query = "UPDATE cmimedatatable SET C4 = ".$x['CMIMI']." WHERE KODARTIKULLI = '".$x['KODARTIKULLI']."' ";
		}
		else if($x['KODNIVELCMIMI'] == 'N5'){
			$query = "UPDATE cmimedatatable SET C5 = ".$x['CMIMI']." WHERE KODARTIKULLI = '".$x['KODARTIKULLI']."' ";
		}
		else if($x['KODNIVELCMIMI'] == 'N6'){
			$query = "UPDATE cmimedatatable SET C6 = ".$x['CMIMI']." WHERE KODARTIKULLI = '".$x['KODARTIKULLI']."' ";
		}
	}
	else if ( nr == 0){
	   if($x['KODNIVELCMIMI'] == 'N1'){
			$query = "INSERT INTO cmimedatatable(KODARTIKULLI, C1,C2, C3, C4, C5, C6) VALUES('".$x['KODARTIKULLI']."',".$x['CMIMI'].",0,0,0,0,0)";
		}
		else if($x['KODNIVELCMIMI'] == 'N2'){
			$query = "INSERT INTO cmimedatatable(KODARTIKULLI, C1,C2, C3, C4, C5, C6) VALUES('".$x['KODARTIKULLI']."',0,".$x['CMIMI'].",0,0,0,0)";
		}
		else if($x['KODNIVELCMIMI'] == 'N3'){
			$query = "INSERT INTO cmimedatatable(KODARTIKULLI, C1,C2, C3, C4, C5, C6) VALUES('".$x['KODARTIKULLI']."',0,0,".$x['CMIMI'].",0,0,0)";
		}
		else if($x['KODNIVELCMIMI'] == 'N4'){
			$query = "INSERT INTO cmimedatatable(KODARTIKULLI, C1,C2, C3, C4, C5, C6) VALUES('".$x['KODARTIKULLI']."',0,0,0,".$x['CMIMI'].",0,0)";
		}
		else if($x['KODNIVELCMIMI'] == 'N5'){
			$query = "INSERT INTO cmimedatatable(KODARTIKULLI, C1,C2, C3, C4, C5, C6) VALUES('".$x['KODARTIKULLI']."',0,0,0,0,".$x['CMIMI'].",0)";
		}
		else if($x['KODNIVELCMIMI'] == 'N6'){
			$query = "INSERT INTO cmimedatatable(KODARTIKULLI, C1,C2, C3, C4, C5, C6) VALUES('".$x['KODARTIKULLI']."',0,0,0,0,0,".$x['CMIMI'].")";
		}
	}
  	$mysqli->query($query);

}

And this is how the data are saved in my table, 

 

[mac_gyver]

please, don't try to store data into a database table like it is a spreadsheet. each data item should be stored in a separate row in the table. your table should have columns for KODARTIKULLI, KODNIVELCMIMI, and CMIMI.

 

next, you don't have to try to select data (which you are not actually fetching and storing the count into a php variable in your code) to determine if you are going to insert a new row or update an existing row. there's a single query that does that - INSERT ... ON DUPLICATE KEY UPDATE ...   the KODARTIKULLI and KODNIVELCMIMI columns would be defined as a composite unique index to both enforce one row per combination of those values and to allow this query to work.

 

you should be using a prepared query in any case, but using one when running a query inside of a loop will result in the most efficient operation (saves some time in the parsing and planning of the sql statement). the query would be prepared once, before the start of your loop, with place-holders in the sql statement for the data, then the data would be supplied to the sql query statement when you execute the query inside of the loop. unfortunately, the php mysqli extension is not the best choice to do this. if you can you should switch to use the php PDO extension.

 

in short, all the code and queries you have shown can be replaced with just a few lines of code and one sql query statement.

[NotionCommotion]

What is not working?

Edited December 9, 2016 by NotionCommotion