Inserting duplicate record in PHP while

0

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

But I'm having a credit doubling 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 query above, it checks purchases made under certain conditions, but even then sometimes , it inserts a 2 or 3x purchase into the credits table.

Images to illustrate:

Purchasing table:

Creditstable:

How can I fix this?

Thank you in advance!

    
asked by anonymous 04.06.2017 / 00:45

1 answer

0

In your select add GROUP BY IDTABLE there will be no 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**
    
04.06.2017 / 02:01