0

I'm having trouble inserting values to a mysql database using a php script

<?php

$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $db) or die (mysqli_error($conn)); mysqli_select_db($conn, $db) or die (mysqli_error($conn));

$token = null; $lastWaterDate = null; $lastWaterAmount = null; $zipCode = null; $waterToDate = null; $addKey = null;

if(isset ($_POST["token"])){
    $token = $_POST["token"]; 
} 
if(isset ($_POST["lastWaterDate"])){
    $lastWaterDate = $_POST["lastWaterDate"]; 
} 
if(isset ($_POST["lastWaterAmount"])){
    $lastWaterAmount = $_POST["lastWaterAmount"]; 
} 
if(isset ($_POST["zipCode"])){
    $zipCode = $_POST["zipCode"]; 
} 
if(isset ($_POST["waterToDate"])){
    $waterToDate = $_POST["waterToDate"]; 
} 
if(isset ($_POST["addKey"])){
    $addKey =$_POST["addKey"]; 
}

if($addKey == -1){
    $sql = "INSERT INTO UserInfo (userToken) VALUES ('$token')"; 
}

elseif($addKey == 0){
    $sql = "INSERT INTO UserInfo (userToken, lastWaterDate, lastWaterAmount, zipCode, waterToDate) VALUES ('$token','$lastWaterDate','$lastWaterAmount','$zipCode','$waterToDate') ON DUPLICATE KEY UPDATE lastWaterDate=VALUES(lastWaterDate), lastWaterAmount=VALUES(lastWaterAmount), waterToDate=VALUES(waterToDate)"; 
}

else{
    $sql = "INSERT INTO UserInfo (userToken, lastWaterDate, lastWaterAmount, zipCode, waterToDate) VALUES ('$token','$lastWaterDate','$lastWaterAmount','$zipCode','$waterToDate') ON DUPLICATE KEY UPDATE lastWaterDate=VALUES(lastWaterDate), lastWaterAmount=VALUES(lastWaterAmount), zipCode=VALUES(zipCode), waterToDate=VALUES(waterToDate)"; 
}

$res = mysqli_query($conn, $sql) or die(mysqli_error($conn));

if($res){
    echo "Successfully updated db"; 
} 
else{
    echo "Failed to update db"; 
}

mysqli_close($conn);

?>

When addKey is = -1 the insertion works fine but when it isn't nothing seems to happen in the database and I'm having trouble figuring out why.

I've tried with both a new key and a duplicate key and yet nothing changes at all in the db. Thanks a lot

mooooo098
  • 1
  • What happens if you `echo $sql` and then execute that query by hand? – Barmar Jun 03 '17 at 02:03
  • Do you get the message `Successfully updated db`? – Barmar Jun 03 '17 at 02:03
  • 1
    You'll never get the message `Failed to update db`, because when it fails it goes to `die(mysqli_error($conn)`. – Barmar Jun 03 '17 at 02:04
  • You should learn to use prepared statements instead of substituting variables, to prevent SQL injection. – Barmar Jun 03 '17 at 02:05
  • You said that when addKey = -1 "the insertion works fine". Does that mean that a record inserted into the database? But then you follow that with "nothing seems to happen in the database". What exactly is the issue? – Ravi Gehlot Jun 03 '17 at 02:40
  • @RaviGehlot basically when addKey = -1 the database gets updated with whatever I'm trying to insert but if for example it = 0, then nothing is inserted or updated and the database stays unchanged – mooooo098 Jun 03 '17 at 02:44

2 Answers2

0

When addkey is not -1, do you maybe want to UPDATE your table, instead of INSERT INTO ?

Check this out for more information about the differences of UPDATE AND INSERT INTO: What are differences between INSERT and UPDATE in MySQL?

Also, could you provide some more information about your database and about the errors you get ?(If you get any)

Your problem, most likely has something to do with the way you implement your $sql query. Focus on that part of your script

Soutzikevich
  • 991
  • 3
  • 13
  • 29
  • I tried '$sql = "UPDATE UserInfo SET lastWaterDate = $lastWaterDate, lastWaterAmount = $lastWaterAmount, zipCode = $zipCode, waterToDate = $waterToDate WHERE userToken = $token";' but that also didn't help – mooooo098 Jun 03 '17 at 02:19
0

The issue might be that you are receiving a string zero "0" from the form and not an integer. Your conditional is expecting an integer 0 when the form is submitted. You need to force that to an int and then use $_POST on your conditionals. Please always escape post variables. 

<?php
$conn = mysqli_connect('localhost', 'xxx', 'xxx', 'xxx') or die (mysqli_error($conn));

mysqli_select_db($conn, 'test') or die (mysqli_error($conn));

$_POST['token'] = 'abca';
$_POST['lastWaterDate'] = '2017-12-12';
$_POST['lastWaterAmount'] = '$100';
$_POST['zipCode'] = '01010';
$_POST['waterToDate'] = '';

// Let's ensure that the value is an INT
$_POST['addKey'] = (int)'0';

if(isset($_POST["token"])){
    $token = mysqli_real_escape_string($conn, $_POST["token"]);
}

if(isset($_POST["lastWaterDate"])){
    $lastWaterDate = mysqli_real_escape_string($conn, $_POST["lastWaterDate"]);
}

if(isset($_POST["lastWaterAmount"])){
    $lastWaterAmount = mysqli_real_escape_string($conn, $_POST["lastWaterAmount"]);
}

if(isset($_POST["zipCode"])){
    $zipCode = mysqli_real_escape_string($conn, $_POST["zipCode"]);
}

if(isset($_POST["waterToDate"])){
    $waterToDate = mysqli_real_escape_string($conn, $_POST["waterToDate"]);
}

if(isset($_POST["addKey"])){
    $addKey = mysqli_real_escape_string($conn, $_POST["addKey"]);
}

if($_POST['addKey'] == -1){
    $sql = "INSERT INTO UserInfo (userToken) VALUES ('$token')";
} elseif($_POST['addKey'] == 0) {
   $sql = "INSERT INTO UserInfo (userToken, lastWaterDate, lastWaterAmount, zipCode, waterToDate) VALUES ('$token','$lastWaterDate','$lastWaterAmount','$zipCode','$waterToDate') ON DUPLICATE KEY UPDATE lastWaterDate=VALUES(lastWaterDate), lastWaterAmount=VALUES(lastWaterAmount), waterToDate=VALUES(waterToDate)";
}else{
    $sql = "INSERT INTO UserInfo (userToken, lastWaterDate, lastWaterAmount, zipCode, waterToDate) VALUES ('$token','$lastWaterDate','$lastWaterAmount','$zipCode','$waterToDate') ON DUPLICATE KEY UPDATE lastWaterDate=VALUES(lastWaterDate), lastWaterAmount=VALUES(lastWaterAmount), zipCode=VALUES(zipCode), waterToDate=VALUES(waterToDate)";
}

$res = mysqli_query($conn, $sql);

if($res){
    echo "Successfully updated db";
}
else{
    echo "Failed to update db";
}
Ravi Gehlot
  • 1,099
  • 11
  • 17