1

I am trying to allow an android user to replace a database table. My app reads in a excel spreadsheet from the phone, adds each row into a json, and then serializes the json to get a string:

[{"name":"catheter","alts":"cathater, cathiter, cath","num":"134"}, 
{"name":"cast","alts":"","num":"2212"}]

My app then sends the serializes json to a php file on my server where I

var_dump(json_decode($serializedString));

to get to the array in question:

array(2) {  [0]=>  object(stdClass)#2 (4) {    ["name"]=>    string(8) 
"catheter"    ["alts"]=>    string(24) "cathater, cathiter, cath"    
["num"]=>    string(3) "134"    }  [1]=>  object(stdClass)#3 (4) {    
["name"]=>    string(4) "cast"    ["alts"]=>    string(0) ""    
["number"]=>    string(0) ""    }}

I'm php stupid, but I have tried a BUNCH of stuff. This will tell me how many items there are in the array:

$count = 0;
$something = json_decode($serializedJsonString, true);
foreach($something as $obj){
    $count = $count+1;
}
echo $count;

I really need to be able to refer to each items key-value pairs so I can add them to a database which has columns for name, alt_names, and number. Thanks a bundle.

William Stidhem
  • 29
  • 4

3 Answers3

2

May be this can help you : 

$serializedString= '[{"name":"catheter","alts":"cathater, cathiter, cath","num":"134"}, 
{"name":"cast","alts":"","num":"2212"}]';

$res = json_decode($serializedString, true);

function for isert data (call it while looping the array)

function insert($element , $mysqli){
    if(isset($element['name']) && isset($element['alts']) && isset($element['num'])){
     $stmt = $mysqli->prepare("INSERT INTO myTable (name, alt_names,number ) VALUES (?, ?, ?)");
     $stmt->bind_param($element['name'], $element['alts'],$element['num']);
     $stmt->execute();
     $stmt->close();
   }

}

your main script : 

$host = 'insert here the host';
$db = 'name of your database';
$username= 'db username';
$password = 'password db';


 // create database connection
 $mysqli = new mysqli($host, $username, $password, $db);
 $mysqli->set_charset("utf8mb4");
 $mysqli->autocommit(FALSE); // manage transaction

foreach($res as $key => $value){
   insert($value,$mysqli);
}
$mysqli->commit();
Yassine CHABLI
  • 3,459
  • 2
  • 23
  • 43
  • This goes in the right general direction, but downvoted for advocating a solution which is vulnerable to SQL injection, and uses a different library to the one the OP said they want to use... – ADyson Mar 26 '19 at 09:46
  • 1
    Also consider that this could be made into a single SQL insert statement which would be more efficient... – ADyson Mar 26 '19 at 09:47
  • @ADyson , using prepared statement avoid slq injection , i already update my answer. thank you – Yassine CHABLI Mar 26 '19 at 09:58
  • Thanks but you're still using PDO when the question clearly states they want to use mysqli – ADyson Mar 26 '19 at 09:58
  • Better...but `catch (PDOException $e)` ?? – ADyson Mar 26 '19 at 10:06
  • 1
    Agreed. Enough for an upvote. N.B. A perfect solution would either turn this into a single SQL statement (more efficient), or at least wrap it all inside a transaction to ensure integrity. And ideally it would still handle exceptions (just not PDO exceptions). But thanks for tidying it up. It should demonstrate the concept nicely to the OP. – ADyson Mar 26 '19 at 10:10
1
$count = 0;
$something = json_decode($serializedJsonString, true);
foreach($arr as $item){
    $name = $item["name"];
    $alts = $item["alts"];
    $num = $item["num"];
//make insert query here.
}
mdeora
  • 4,152
  • 2
  • 19
  • 29
1

You could do something like this:

$db = new MySQLi(HOST, USER, PASS, SCHEMA, PORT);

$count = 0;
$elems = json_decode($serializedJsonString, true);

$stmt = $db->prepare('INSERT INTO items (name, alt_names, number) VALUES (?,?,?)');

$db->begin_transaction();
foreach($elems as $elem){
    $stmt->bind_param("ssi", $elem['name'], $elem['alts'], $elem['num']);
    $stmt->execute();
    $count = $count+1;
}

$stmt->close();
$db->commit();
$db->close();
echo $count;

This is also safe against any SQL injection. And I've supposed that location and id are nullable and not available at the moment of this operation.

oniramarf
  • 843
  • 1
  • 11
  • 27
  • The only problem with this now is you're not reading the JSON structure correctly. $elems will be an array of objects - look at the sample data in the question. You need to loop that and construct an INSERT statement for each object. I wonder if you intended the prepare() and bind_param() to be inside your foreach loop? – ADyson Mar 26 '19 at 09:57
  • Yeah, but with prepared statements it's not that easy. Performances could be improved with a transaction (since writes would be performed all on commit) or without using prepared statements and cleaning the inputs with `real_escape_string`. – oniramarf Mar 26 '19 at 10:23
  • what's not that easy?? I don't get your point. This code will not work, that's what I was saying. The bind_param() call will fail since $elem does not exist at that time. I think you made a mistake putting that outside your loop. I wasn't talking about transactions or performance. Although I agree, it would be better for integrity if all the statements were wrapped in a transaction, or if you made the code generate one single big INSERT statement to insert all the rows at once. – ADyson Mar 26 '19 at 10:27
  • Sorry, didn't get that. Also, speaking of copy-paste of code that won't work, I wasn't sure about prepared statement and checked out [w3school](https://www.w3schools.com/PHP/php_mysql_prepared_statements.asp) link about that and I made that mistake watching that piece of code. – oniramarf Mar 26 '19 at 10:43
  • w3schools is notorious for inaccurate/ out of date tutorials etc, unfortunately. I rarely use it. Better to rely on the PHP documentation pages. Anyway thanks for updating, have an upvote – ADyson Mar 26 '19 at 11:01