JS/PHP/MySQL Success but not Inserted

Question

I have a array containing objects and I want to store these objects into my MySQL DB. At the beginning it worked quite fine, but suddenly it stopped working even though it did not make any changes to the code or the DB.

The array of object looks as follows: var geocoded = [{zip: 1234, place: "XY", country: "XY", lat: "123.123", lng: "123.123"}, ...];

I use the following JS code to iterate over the array and post each object to the DB. kiter is the iterator I use and is defined as geocoded.length - 1

function postPlaces(data, kiter) {

  if (kiter >= 0) {
    $.post("api/placessave.php", 
      data[kiter],
      function(data, status){
        kiter--;
        postPlaces(geocoded, kiter);
        console.log(data + '.............' + status);
      }
    );
  } else {
        //statusUpdate(id);
  }
}

placessave.php looks as follows:

<?php
 define('HOST','localhost');
 define('USERNAME', 'root');
 define('PASSWORD','*****');
 define('DB','****');

 $con = mysqli_connect(HOST,USERNAME,PASSWORD,DB);

 $zip = $_POST['zip'];
 $place = $_POST['place'];
 $country = $_POST['country'];
 $lat = $_POST['lat'];
 $lng = $_POST['lng'];

 $sql = "insert ignore into places (zip, place, country, lat, lng) values ($zip, '$place', '$country', '$lat', '$lng')";

 if(mysqli_query($con, $sql)){
 echo "success";
 }

 mysqli_close($con);
?>

I use INSERT IGNORE because duplicates may exist but an update is not needed. The interesting part is, that everything works quite nice I also get a Success on every query but nothing is stored to the DB.

what part of your code is failing - the sql or the ajax/javascript? — Professor Abronsius, Aug 25 '16 at 12:43
You're open to SQL injection attacks and, in many countries, zip/postcode is **not** numeric so `values ($zip, '$place',` will cause a problem (no quotes around `$zip`). — CD001, Aug 25 '16 at 12:46
did you test it without `IGNORE` keyword?? just test it, if you are really getting success msg — devpro, Aug 25 '16 at 12:47
have you tried echoing the sql an running it directly at db? — Jeff, Aug 25 '16 at 12:47
^ +1 about the zipcode. Even here in the U.S. where it _appears_ to be a numeric value, there are places that start with 0, making it far better to treat it as a string. — Patrick Q, Aug 25 '16 at 12:48
or also check what r u getting in `print_r($_POST);` before query. — devpro, Aug 25 '16 at 12:48
and have you tried to fetch a possible mysql-warning? http://php.net/manual/en/mysqli.get-warnings.php — Jeff, Aug 25 '16 at 12:49
and dont know why are u using `mysql_error()`? along with mysqli_* — devpro, Aug 25 '16 at 12:51
and here is a long discussion about "insert Ignore" that might help: http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update — Jeff, Aug 25 '16 at 12:55
Anyone else find that *Thanks to Pekka for pointing this out.* comment a bit odd - almost like the code's been borrowed from another SO answer verbatim : http://stackoverflow.com/questions/4996106/php-and-mysql-not-inserting — CD001, Aug 25 '16 at 12:56
Does not work without IGNORE either; I have only numeric zip codes, but this is a good hint thanks; I will now try to fetch possible mysql-warnings — eltomaco, Aug 25 '16 at 12:58
@CD001 thats exactly where I got this piece from, tried to find some help here on stackoverflow... removed it again from my code ;-) — eltomaco, Aug 25 '16 at 12:59
;) you probably want to keep the `else { ... }` but instead of `die(mysql_error())` you'd want `die(mysqli_error())` - if the query fails then you'll get an error message explaining why; `IGNORE` will suppress that though so you don't want it there for the test... actually, you don't really want it at all ideally. — CD001, Aug 25 '16 at 13:03
run manually in phpmyadmin `Insert ignore into places (zip, place, country, lat, lng) values (1234, 'XY', 'XY', '123.123', '123.123');` — devpro, Aug 25 '16 at 13:07
Put echo before $sql and send the query it echoed here @eltomaco — Sathvik Chinnu, Aug 25 '16 at 13:40
And there may be another issue like the data is being ignored due to duplicate. Remove `IGNORE` and try it once — Sathvik Chinnu, Aug 25 '16 at 13:43
@SathvikCheela the problem was due to a `'` in a place name. — eltomaco, Aug 25 '16 at 13:45
Okay, Have to able to figure it out, I mean your question have been solved or not — Sathvik Chinnu, Aug 25 '16 at 13:47

score 0 · Answer 1 · answered Aug 25 '16 at 12:46

0

Insert Query you have to change like this. You have Missed Quotes around the values

Replace

$sql = "insert ignore into places (zip, place, country, lat, lng) values ($zip, '$place', '$country', '$lat', '$lng')";

With

$sql = "insert ignore into places (zip, place, country, lat, lng) values ('".$zip."', '".$place."', '".$country."', '".$lat."', '".$lng."')";

answered Aug 25 '16 at 12:46

Naresh Kumar P

4,127
2
16
33

Use `IGNORE` as suggested and check on to the post values are coming to this page after submission of the form. Ensure that you have placed all the elements inside the **
** attribute.
– Naresh Kumar P Aug 25 '16 at 12:49
1

With the exception of the fact you've now put `'` marks around `$zip` this makes absolutely no difference : http://php.net/manual/en/language.types.string.php#language.types.string.syntax.double – CD001 Aug 25 '16 at 12:51

score 0 · Answer 2 · answered Aug 25 '16 at 13:25

0

I found the solution myself. The problem was in the data I wanted to store. I found that there was a place name which contained a '. Therefore the query did not work. After removing this, everything works fine.

answered Aug 25 '16 at 13:25

eltomaco

91
2
9

score 0 · Answer 3 · answered Aug 25 '16 at 13:31

0

just change ($zip, in the query to ('$zip',

zip is probably string and you are not passing it as string in the query. Moreover, this code is vulnerable to SQL injection. Please read about it to avoid insecurities.

answered Aug 25 '16 at 13:31

Danyal Sandeelo

12,196
10
47
78

`$zip` is a numeric, so no need of quotes – Sathvik Chinnu Aug 25 '16 at 13:41

JS/PHP/MySQL Success but not Inserted

3 Answers3