Inserting duplicate record in PHP while

Asked

Viewed 101 times

0

I am deploying a system, where the customer, after buying an amount of credits, is inserted into his account in the database.

But I’m having a credit duplication problem in my PHP script.

<?php
header('Access-Control-Allow-Origin: *');

include '../../init.php';

$sql = "SELECT *, compras.id_usuario AS id_user_compra, compras.id AS id_compra FROM compras LEFT JOIN creditos ON creditos.id_usuario = compras.id_usuario WHERE compras.entregue = 'nao' AND compras.payment_status = 'Completed'";
$query = $mysqli->query($sql);

while ($ln = $query->fetch_array()){
    $id_usuario = $ln['id_user_compra'];
    $id_compra = $ln['id_compra'];
    $creditos = preg_replace("/[^0-9\s]/", "", $ln['item_name']);

    $sql_up = "UPDATE compras SET entregue = 'sim' WHERE id = '$id_compra'";
    $query_up = $mysqli->query($sql_up);

    $sql_ins = "INSERT INTO creditos (id_usuario, credito, debito) VALUES ('$id_usuario', '$creditos', '0')";
    $query_ins = $mysqli->query($sql_ins);
}

?>

In my consultation above, he checks the purchases made, with certain conditions, but still, sometimes, it inserts a purchase 2 or 3x in the credits table.

Images to illustrate:

Shopping table:

Tabela compras

Credit table:

Tabela creditos

How can I fix this problem?

Thanks in advance!

php

  • Your select must be returning more than one record.

  • Yes, he returns, because sometimes he has 2 or 3 purchases.

  • So you’re making one insert within the while that traverses the records. If there is more than one, it will be inserted more than once.

  • Wow, I ended up solving. I gave a LEFT JOIN in another table without needing... it was like this: SELECT * FROM compras WHERE compras.entregue = 'nao' AND compras.payment_status = 'Completed' Thank you!

1 answer

0

In your select you add GROUP BY IDTABELA Then you won’t bring back duplicate records. Example:

"SELECT *, compras.id_usuario AS id_user_compra, compras.id AS id_compra FROM compras LEFT JOIN creditos ON creditos.id_usuario = compras.id_usuario WHERE compras.entregue = 'nao' AND compras.payment_status = 'Completed' GROUP BY **IDTABELA**

Browser other questions tagged php

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