How to write data from a form to a database by converting the fields to JSON?

Question

I have any form, example:

<form action="confi.php" method="post" enctype="multipart/form-data">
   <p><input type="text" name="nome" value="nome">
   <p><input type="text" name="email" value="email">
   <p><button type="submit">Submit</button>
</form>

I would like to record these fields (as a JSON) in a table.

confi.php

//minha conexão com o banco

$sql = "INSERT INTO `banco`.`tabela`(`json`) VALUES ('$json')";

My question is, how do I take and convert the fields/values filled in the form into a JSON file to write to the table this way?

@UPDATE

I managed to assemble the JSON with the json_encode as suggested.

But when the record arrives at the bank, the value is "Array".

$myArr = array("nome" => $_POST['nome']
        , "email" => $_POST['email']);
json_encode($myArr);

Insert in the database

$sql = "INSERT INTO `tabela`(`json`) VALUES ('$myArr');";

Table

How do I enter the actual json value?

Answer 1

Although answered, I will add this to the discussion:

JSON (Javascript Object Notation, because it has the format of a JS object) is not a file format, first. Yes, an open data standard format.

Later, storing data as JSON in Mysql would go against the intention of a relational database manager system (although, some call the Nosql databases "non-standard relational", which to me doesn’t sound good). Even so, the Mysql has support for JSON since version 5, as well as Postgresql (which, in this regard at least, has more functions). And SQL Server 2014+ also, what I’ve waited for so long.

Although you were better using Mongodb, Couchdb, Cassandra, Marklogic, others, you can still store the JSON data as string in the normalized relational database.

A simple code to store the $_POST in the database as JSON is as follows:

try {
    // Conexão com o banco de dados e seus atributos
    $conexao = new \PDO("mysql:host=localhost;dbname=banco_de_dados", 'usuario', 'senha');

    $conexao->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

    // Query com o placeholder :json         
    $query = 'INSERT INTO tabela (json) VALUES (:json)';

    // Usando prepared statements, porque é mais seguro e melhora a performance
    $insert = $conexao->prepare($query);

    // Diz pro MySQL que o parametro representado pelo placeholder :json na query é uma string, convertendo $_POST em uma string JSON
    $insert->bindParam(':json', json_encode($_POST, JSON_NUMERIC_CHECK), PDO::PARAM_STR);

    // Executa a query
    $insert->execute();

    // Verifica se a inclusão foi feita
    $linhas_afetadas = $insert->rowCount();

    // XXX Aqui você pode mudar o número de acordo com o número de linhas que voc~es espera serem afetadas
    if($linhas_afetadas == 1) {
        echo json_encode(array('message' => 'Dados inseridos com sucesso'));
    }


} catch (PDOException $ex) {
    echo json_encode(array('message' => $ex->getMessage);
}

The code has not been tested, but can be easily used, since it uses a well-known model with PDO (is, always use PDO or Mysqli).

Answer 2

Only use the function json_encode($_POST)

Documentation of json_enconde function

Recalling that, according to the documentation, the function jason_encode() does not change the array you pass to it, only returns the JSON string. That is to say:

$myArr = array("Um" => 1, "Dois" => 2, "Três" => 3);
$json = json_encode($myArr);

But if you want to store data in JSON, as commented in your question, maybe it’s best to use the MongoDB or other databases NoSQL