PHP UPDATE query doesn't work

Question

i would like to a fix a problem with my code, that regard an user profile system. During registration user set their personal information, including address. Address value is used by API google maps.UPDATE query doesnt work.Why? N.B: data type lat, lng are 'decimal(10,8)decimal(11,8)'

<?php
include("database.php");
session_start();


    $error = "";
    if(isset($_POST['submit']))
    {
      $username = mysql_real_escape_string($_POST['username']);
      $name = mysql_real_escape_string($_POST['name']);
      $surname = mysql_real_escape_string($_POST['surname']);
      $affiliation = mysql_real_escape_string($_POST['affiliation']);
      $department = mysql_real_escape_string($_POST['department']);
      $address = mysql_real_escape_string($_POST['address']);
      $position = mysql_real_escape_string($_POST['position']);
      $email = mysql_real_escape_string($_POST['email']);
      $web = mysql_real_escape_string($_POST['web']);
      $telephone = mysql_real_escape_string($_POST['telephone']);
      $mobile = mysql_real_escape_string($_POST['mobile']);
      $password = $_POST['password'];
      $passwordConfirm = $_POST['passwordConfirm'];
      $privacy = $_POST['privacy'];

      //validare i valori inseriti dall'utente
      if(!filter_var($email, FILTER_VALIDATE_EMAIL))
      {
        $error = "Inserisci una email valida ";
      }

      else if (strlen($password < 8)) {
        $error = "La password deve contenere almeni 8 caratteri";
      }

      else if ($password != $passwordConfirm)
      {
        $error = "Le password devono coincidere!";
      }

      else {
        $error = "Ti sei appena registrato su B";
      }


      $sql = "INSERT INTO users(username, name, surname, affiliation, department,address,position,email,web,telephone,mobile,password,privacy) VALUES('$username','$name','$surname','$affiliation','$department','$address','$position','$email','$web','$telephone','$mobile','$password','$privacy')";
      mysqli_query($database,$sql) or die(mysqli_error($database));

      if($address !=''){
        $request_url = "http://maps.googleapis.com/maps/api/geocode/xml?address=".$address."&sensor=true";
        $xml = simplexml_load_file($request_url) or die("url not loading");
        $status = $xml->status;
        if ($status=="OK"){
          $lat = $xml->result->geometry->location->lat;
          $lng = $xml->result->geometry->location->lng;

        }
        $sql1 = "UPDATE users SET lng='$lng', lat='$lat' WHERE username='$username'";
        mysqli_query($database,$sql1) or die(mysqli_error($database));
      }
    }
    ?>

what's the error message? [this might help](http://stackoverflow.com/questions/845021/how-to-get-useful-error-messages-in-php) — Jeff Puckett, May 27 '16 at 16:34
also, [Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – [Jay Blanchard](http://stackoverflow.com/users/1011527/jay-blanchard) — Jeff Puckett, May 27 '16 at 16:36
Different updates parts must be separated with `,` and not with `AND` — Neobugu, May 27 '16 at 16:38
`mysql_` != `mysqli_` make sure you use the write driver consistently. — chris85, May 27 '16 at 16:42
Using PHP 7 would solve any mixed API issues in this code instantly. — tadman, May 27 '16 at 17:22
Does this answer your question? [Why shouldn't I use mysql\_\* functions in PHP?](https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Dharman, Mar 22 '20 at 02:30

score 2 · Answer 1 · edited May 27 '16 at 16:37

2

instead of using AND, you need to separate with a comma ,

$sql1 = "UPDATE users SET lng='$lng', lat='$lat' WHERE username='$username'";

edited May 27 '16 at 16:37

Jeff Puckett

37,464
17
118
167

answered May 27 '16 at 16:35

jophab

5,356
14
41
60

try to add back-ticks , and everything is ok! :D +1 from me ;) – Aniruddha Chakraborty May 27 '16 at 16:42
@FrancescoRaffAnselmi , what do you mean by "one time work and the other time no" .. can you please explain ? – Aniruddha Chakraborty May 27 '16 at 17:20
Sorry. Only it works once. It seems that the UPDATE query functions just once and then crashes if you try a second filling in the form – Francesco Raff Anselmi May 27 '16 at 17:23

Aniruddha Chakraborty · Answer 2 · 2016-05-27T16:45:02.253

2

Do not use And and don't forget to add backticks :) and good to see that newbies are completely avoiding mysql_* :D

$sql1 = "UPDATE `users` SET `lng`='$lng', `lat`='$lat' WHERE `username`='$username'";

edited May 27 '16 at 16:45

answered May 27 '16 at 16:39

Aniruddha Chakraborty

1,849
1
20
32

1

Except for using `mysql_real_escape_string()` – Jay Blanchard May 27 '16 at 17:14

score 1 · Answer 3 · answered May 27 '16 at 16:37

1

Replace AND with comma in update statement

answered May 27 '16 at 16:37

Hooch

487
3
11

PHP UPDATE query doesn't work

3 Answers3