How to check if the DB already have the data or insert new data

Question

What I want is that php check if the client IP address is the same one which in the DB if it already exists, if not to insert new data. well, it works if the client isn't already inserted in the database, but if he already exists php is skipping the update and trying to insert it again in the database............ I don't know whats wrong with it and couldn't figure out. Here is my code:

<?php
$corruser = $_SESSION['user_name'];
$client_ip = $_SERVER['REMOTE_ADDR'];
require_once 'connections/dbc.php';
if (!$conn) {
  echo "Error connecting the database";
  exit();
} else{
  $GUI = "SELECT * FROM `customers` WHERE user_name='$corruser'";
  $GUI_response = mysqli_query($conn, $GUI);
  if (!$row = mysqli_fetch_assoc($GUI_response)) {
    echo "Error while query the database";
    exit();
  } else{
  $customer_id = $row['customer_id'];
  $check = "SELECT * FROM `users-ipdb` WHERE customer_id='$customer_id' AND user_name='$user_name' ";
  $check_response = mysqli_query($conn,$check);
  $check_result = mysqli_fetch_array($check_response, MYSQLI_NUM);
  if ($check_result[0] > 1) {
    $update_ip = "UPDATE `users-ipdb` SET `client_ip`='$client_ip' WHERE customer_id='$customer_id' AND user_name='$corruser' ";
    $update_ip_result = mysqli_query($conn, $update_ip);
    if (!$update_ip_result) {
      echo "ERROR UPDATING DATA BASE";
      exit();
    }
  } else{
  $insert_new = "INSERT INTO `users-ipdb`(`customer_id`, `user_name`,`client_ip`) VALUES ('$customer_id','$corruser','$client_ip')";
  $insert_new_result= mysqli_query($conn, $insert_new);
  if (!$insert_new_result) {
    echo "Error inserting new data in the database";
    exit();
  }
}

}
}
?>

You are not actually checking if the IP exists anywhere in this code — Simon K, Jul 11 '18 at 19:46
Look into the concept of an "upsert". With MySQL, it's as easy as `ON DUPLICATE KEY UPDATE`. — ceejayoz, Jul 11 '18 at 19:46
@WebCode.ie well I'm checking if the user is completely inserted in the database because there is no way in my DB structure to have the user without the IP so its automatic — Assad Rajab, Jul 11 '18 at 19:50
Change your `$check` to this: `$check = "SELECT * FROM users-ipdb WHERE customer_id='$customer_id' AND user_name='$corruser' AND client_ip='$client_ip' ";` which users the correct username (undefined otherwise) and adds a where clause on the IP — Simon K, Jul 11 '18 at 19:55

score 1 · Accepted Answer · answered Jul 11 '18 at 19:51

1

I think you made an error with this code :

  $check = "SELECT * FROM `users-ipdb` WHERE customer_id='$customer_id' AND user_name='$user_name' ";

$user_name variable doesn't exist, you should replace it with $corruser

That's why the code never goes into the UPDATE

answered Jul 11 '18 at 19:51

PierreN

968
4
11

HAHAHAHA lool Thank you very much. :) – Assad Rajab Jul 12 '18 at 16:37
happy the have helped you ! – PierreN Jul 12 '18 at 16:40

score 0 · Answer 2 · answered Jul 11 '18 at 20:15

First, make sure that your condition does work as expected. If customer_id is not a number the following line:

if ($check_result[0] > 1) {

can be possibly evaluated as if(0 > 1) let you read this:

Comparing String to Integer gives strange results.

The other comments mention "UPSERTS" which are explained here https://mariadb.com/kb/en/library/insert-on-duplicate-key-update/

The basic idea is that you can do

INSERT INTO `users-ipdb`(`customer_id`, `user_name`,`client_ip`) 
VALUES ('$customer_id','$corruser','$client_ip')" 
ON DUPLICATE KEY UPDATE client_ip='$client_ip';

and you get rid of the all the php logic. For this to work properly customer_id and user_name must be both part of the PRIMARY KEY.

If you need to query multiple tables, you can use joins - if you use ON DUPLICATE KEY UPDATE you don't need them, but still a good thing to know - https://mariadb.com/kb/en/library/join-syntax/

Last, but not least - it is a good habit to escape any value which may come from other sources. Maybe it is not your case, but some people tend to create usernames like Joe';DROP TABLE mysql.user;SELECT ' and it will destroy your database, because your query will become

SELECT * FROM `users-ipdb` WHERE customer_id='$customer_id' AND user_name='Joe';DROP TABLE mysql.user;SELECT ''

So be careful.

Well, first of all, Thank you very much for you awesome explain, and thank you for warning me about the value escape, but the $customer_id is taken from other DB as well as the $user_name and in the signup code I've escaped them already :) the only one which may be a danger is the $client_ip. But very much other ways for the explanation. — Assad Rajab, Jul 12 '18 at 16:28

How to check if the DB already have the data or insert new data

2 Answers2