update mysql using array

Question

update mysql using array

Asked 10 years, 2 months ago

Viewed 1,489 times

0

I’m having trouble inserting an array into the database... I tried this script but it only inserts the last id of the array;

<?php
$_conexao = mysqli_connect("localhost", "root", "", "vbt_posteds");
$id_ind = $_POST["inds"];

$id = "57";

$dados = count($id_ind);

for($i=0; $i<$dados; $i++){

$idfnc = $id_ind[$i];

$sql = mysqli_query($_conexao,"UPDATE dados_inds SET id_inds='$idfnc'
                    WHERE id_user='$id'");              
}

echo "editado com sucesso";

?>

i want to update a relational table with 4 lines with the ids

<table>
<tr>
<td>id_user</td><td>id_inds</td>
</tr>
<tr>
<td>57</td><td>8</td>
</tr>
<tr>
<td>57</td><td>4</td>
</tr>
<tr>
<td>57</td><td>5</td>
</tr>
<tr>
<td>57</td><td>2</td>
</tr>
<table>

@Bacco I think it would be better if he used a for($id_ind as $idfnc) right what you think ?

– Otto

2014/09/25 at 14:10
I didn’t see this when I edited no other edition appeared

– Otto

2014/09/25 at 14:11
User14579 you are doing several updates on id 57 with your logic. Obviously only the last one will be visible, because every update the previous one is overwritten. INSERT is for new records, UPDATE is for updating an existing one.

– Bacco

2014/09/25 at 14:12
2

@Otto then, as the author did not explain what he wants to update or insert and in which column, it is difficult to guess. Anyway, the query has a problem (no for or foreach). Of course, the foreach gets cleaner code, but it’s better to focus on the logic of the query itself now, otherwise it can complicate even more :)

– Bacco

2014/09/25 at 14:16
1

user14579 would be legal [Edit] the question and explain what has in the variables and the operating logic, as it increases the chance of an answer that solves your problem.

– Bacco

2014/09/25 at 14:18
@Baccon, it may be that his variables are changed too query UPDATE dados_inds SET id_inds='$id' WHERE id_user='$idfnc', but we won’t know until he edits the question and explains the logic better.

– KaduAmaral

2014/09/25 at 14:34

Show 1 more comment

1 answer

Browser other questions tagged php mysql array loop sql-update

You are not signed in. Login or sign up in order to post.

by marcusagm • **4,828** points · Answer 1 · 2014-09-25T15:26:54+00:00

If you are trying to update data from a relational table you need a composite key or a primary key to identify which record will be updated.

Below indicates how the records in your relationship table with a primary key would look.

 id | id_user | id_inds
 1  | 57      | 2
 2  | 57      | 4
 3  | 57      | 5
 4  | 57      | 8

The id field can be auto-increment.

To update a record you would use the field id as a reference.

Getting this way like this:

$_conexao = mysqli_connect( 'localhost', 'root', '', 'vbt_posteds' );

$all_ids = $_POST[ 'ids' ];
$all_id_ind = $_POST[ 'inds' ];

foreach( $all_ids as $key => $id ) {
    $id_ind = $all_id_ind[ $key ];

    $query = "UPDATE `dados_inds` SET `id_inds` = '$id_ind' WHERE id = '$id'";
    $sql = mysqli_query( $_conexao, $query );              
}

echo "editado com sucesso";

Note that I have not used the field id_user because it will not be updated and does not serve as a reference to identify the record I must update.

If you cannot add this primary field to your relational table, I recommend deleting all records related to a user and then inserting the new relationship.

$_conexao = mysqli_connect( 'localhost', 'root', '', 'vbt_posteds' );

$id_user = 57;
$all_id_ind = $_POST[ 'inds' ];

// Excluo todos os relacionamentos do usuário
$query = "DELETE FROM `dados_inds` WHERE `id_user` = '$id_user'";
$sql = mysqli_query( $_conexao, $query );     

foreach( $all_id_ind as $id_ind ) {
    $query = "INSERT INTO `dados_inds` ( `id_inds`, `id_user` ) VALUES ( '$id_ind', 'id_user' )";
    $sql = mysqli_query( $_conexao, $query );              
}

echo "editado com sucesso";