I found a duplicated row in my table
> image from my table
But in my code, I check if "unique_id" key exists, then update this row, else, create..
I am very noob with sql and I made some functions to make more easy my life... this is my "send_score.php" file
<?php
header('Content-Type: text/plain');
header("Access-Control-Allow-Origin: *");
include "../../functions.php";
$servername = "xxx";
$database = "xxx";
$username = "xxx";
$password = "xxx";
$tabla = "xxx";
$unique_key = $_POST['unique_key'];
$nick = $_POST['nick'];
$puntos = $_POST['puntos'];
$con = sql_connect($servername, $username, $password, $database);
if ( sql_check_row($con,$tabla,"unique_key","'".$unique_key."'") ) {
//if exists, replace
if ( sql_update_row($con,$tabla,"nick",$nick,"unique_key",$unique_key) && sql_update_row($con,$tabla,"sc",$puntos,"unique_key",$unique_key) ) {
echo "OK UPDATE";
exit;
}
else {
echo "BAD UPDATE";
exit;
}
}
else {
//if not exists, create
$arr_key = array("unique_key","nick","sc");
$arr_val = array("'".$unique_key."'","'".$nick."'",$puntos);
if ( sql_insert_row($con,$tabla,$arr_key,$arr_val) ) {
echo "OK INSERT";
exit;
}
else {
echo "BAD INSERT";
exit;
}
}
sql_close($con);
?>
and here is the functions that im using here
function sql_connect($servername, $username, $password, $database) {
return mysqli_connect($servername, $username, $password, $database);
}
function sql_close($con) {
return mysql_close($con);
}
function sql_check_row($con,$table,$key,$value) {
$sql = mysqli_query($con, "SELECT * FROM ".$table." WHERE ".$key." = ".$value);
if( mysqli_num_rows($sql) ) {
return true;
}
else {
return false;
}
}
function sql_update_row($con,$table,$key_set,$value_set,$key_where,$value_where) {
$sql = mysqli_query($con,"UPDATE ".$table." SET ".$key_set." = '".$value_set."' WHERE ".$key_where." = '".$value_where."';");
if ($sql) {
return true;
}
else {
return false;
}
}
function sql_insert_row($con,$table,$key_arr,$value_arr) {
$keys = "(";
for ( $i = 0; $i < sizeof($key_arr); $i++ ){
if ($i != 0) {
$keys .= ",";
}
$keys .= $key_arr[$i];
}
$keys .= ")";
$query = "INSERT INTO ".$table." ".$keys." VALUES (";
for ( $i = 0; $i < sizeof($value_arr); $i++ ){
if ($i != 0) {
$query .= ",";
}
$query .= $value_arr[$i];
}
$query .= ");";
$sql = mysqli_query($con,$query);
if ($sql) {
return true;
}
else {
return false;
}
}
According to me, it shouldn't create a new row because a row already exists with the same "unique_id" and in the image, both rows have exactly the same "unique_id"... why???
My Answer:
It seems like the issue might be with the way you are checking for the existence of the row in your `sql_check_row` function.
In your `sql_check_row` function, you are checking if `mysqli_num_rows($sql)` is greater than 0 to determine if the row exists. However, this might not be the most reliable way to check for the existence of a row, especially if there are duplicate rows in your table.
Instead, you can modify your `sql_check_row` function to fetch the row and check if it exists. Here's an updated version of the function:
php
function sql_check_row($con, $table, $key, $value) {
$sql = mysqli_query($con, "SELECT * FROM " . $table . " WHERE " . $key . " = " . $value);
$row = mysqli_fetch_assoc($sql);
if ($row) {
return true;
} else {
return false;
}
}
By fetching the row and checking if it exists, you can ensure that you are accurately determining if the row with the unique_id already exists in the table. This should help prevent the creation of duplicate rows.
Rate this post
3 of 5 based on 5623 votesComments