Jump to content

Saving json data into a MySql Table


IvHysenbelli

Recommended Posts

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, 

 

post-203085-0-69584800-1481285621_thumb.png

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.