How to insert into mysql using foreach?

Question

Hello, I have a code where I take the data coming from a webservice and store in variables.

I would like to Inserir/Salvar that data in my database Mysql. However I get this data through a foreach(){...}

// Pegar os leads
    $leads = $client->leads->getLeads([]);

// Pegando os leads
    foreach($leads->contacts as $contacts)
    {
        // Salvando dados nas variáveis
            $nome    = $contacts->name;
            $id      = $contacts->id;
            $tags    = $contacts->tags;

        // Percorrendo os dados
            foreach($tags->tags as $objeto)
            {
                $tag  = $objeto->name;
            }   
    }

agree with the above code. How I could insert this data so it would look like this in the table [example ] :

[ID] [NOME] [TAGS]

[01] [JOSÉ] [tag1,tag2,tag3..]

For if I create a query like this :

$query = mysqli_query($conexao,"INSERT INTO leads(nome,tags) VALUES ('$nome','$tag')";

each foreach(){..} would have a tag different and overwrite the value of tag previous.

Answer 1

Instead of you going through the list of tags of each record, being an array, you can make them a unique object, in JSON format, and store them only at once for each record.

$leads = $client->leads->getLeads([]);

foreach($leads->contacts as $contacts)
{
    $nome    = $contacts->name;
    $id      = $contacts->id;
    $tags    = $contacts->tags;

    // Aqui você serializa o vetor de tags:
    $tags = json_encode($tags);

    // Agora execute a sua *query* de INSERT:
    $query = mysqli_query($conexao, "INSERT INTO leads(nome, tags) VALUES ('$nome', '$tags')");

}

This way, will be saved in the database tags in a format similar to [tag1, rag2, tag3...], with brackets. When retrieving the database data, simply run $tags = json_decode($tags) that you will possess the vector of tags again.

Answer 2

You can use the Implode

You can use the implode of php to merge tags and save to a field varchar.

$tags = implode(",", $contacts->tags);

If you need to Insert or Update can use a command INSERT ... ON DUPLICATE KEY UPDATE mysql. See an example in this matter of the OS (in English)

Answer 3

If $contacts->tags; for an array use implode:

$leads = $client->leads->getLeads([]);

foreach($leads->contacts as $contacts)
{
    $nome    = $contacts->name;
    $id      = $contacts->id;
    $tags    = implode(',', $contacts->tags);

    // Aqui você serializa o vetor de tags:
    $tags = json_encode($tags);

    // Agora execute a sua *query* de INSERT:
    $query = mysqli_query($conexao, "INSERT INTO leads(nome,tags) VALUES ('$nome','$tags')";

}

And when you’re reading from the bank explode, something like this:

explode(',', $row['tags']);