We help IT Professionals succeed at work.

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(Latitude='', Longitude ='')' at line 3

Dolamite Jenkins
on
I'm trying to get the syntax right for the insert or on duplicate key update


$sql="INSERT INTO gpscoordinates (UserName, Password1, Group1,Latitude,Longitude)
VALUES
('$_POST[User]','$_POST[Password1]','$_POST[Group1]','$_POST[Latitude]','$_POST[Longitude]') ON DUPLICATE KEY UPDATE (Latitude='$POST_[Latitude]', Longitude ='$_POST[Longitude]')";

Open in new window

Comment
Watch Question

Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
I think it's telling you that you don't have a value there.
Marco GasiFreelancer
CERTIFIED EXPERT
Top Expert 2010

Commented:
First: never, I say never insert in database $_POST (or $_GET, $_REQUEST) array elements. You always have to filter them to see if they hold legal values:

if (isset($_POST['submit'])){

$user = mysql_real_escape_string($_POST['User']);
$password = mysql_real_escape_string($_POST['Password1']);
$group = mysql_real_escape_string($_POST['Group1']);
$latitude = mysql_real_escape_string($_POST['Latitude']);
$longitude = mysql_real_escape_string($_POST['Longitude']);

}

Second, referring to $_POSR array elements you have to use quotes:

                    $_POST['User'] vs $_POST[User]

Third: you can now try to echo all values to see if they actually hold some legal string:

if (isset($_POST['submit'])){

$user = mysql_real_escape_string($_POST['User']);
$password = mysql_real_escape_string($_POST['Password1']);
$group = mysql_real_escape_string($_POST['Group1']);
$latitude = mysql_real_escape_string($_POST['Latitude']);
$longitude = mysql_real_escape_string($_POST['Longitude']);

echo "User is $user<br />";
echo "password is $password<br />";
echo "Group is $group<br />";
echo "latitude is $latitude<br />";
echo "longitude is $longitude<br />";

$sql="INSERT INTO gpscoordinates (UserName, Password1, Group1,Latitude,Longitude)
VALUES ('$user','$password','$group','$latitude','$longitude') ON DUPLICATE KEY UPDATE (Latitude='$latitude', Longitude ='$longitude')";
                                 

}

Cheers
CERTIFIED EXPERT

Commented:
Note what the others have said about mysql_real_escape_string, but another problem you have is substitution of array values does not work like simple strings. They have to be enclosed in braces { }, so assuming

$test = 'abc';
$_POST['latitude'] = 53.5;

Then

"INSERT INTO myTable ( abc, latitude ) VALUES ('$test', '$_POST['latitude']' ) "

would produce

INSERT INTO myTable ( abc, latitude ) VALUES ( 'abc', '' )


whereas

"INSERT INTO myTable ( abc, latitude ) VALUES ('$test', '{$_POST['latitude']}' ) "

would produce

INSERT INTO myTable ( abc, latitude ) VALUES ( 'abc', '53.5' )
Most Valuable Expert 2011
Top Expert 2016

Commented:
Here is a technique that will let you see the fully-resolved query in any case that the query failed.  Then it will be obvious what caused the error condition

$sql = "/* Whatever */ ";
$res = mysql_query($sql);
if (!$res)
{
    $msg = "FAIL: $sql <br/>" . mysql_error();
    die($msg);
}

Author

Commented:
What I am doing is getting gps updates for an android phone and storing them
 below is my code as it stands now and it works except it creates a new row every time

I don't have a "if (isset($_POST['submit']))" because the data is coming from a phone and not a web page
<?php
$dbhost = 'xxxx';
$dbuser = 'xxxx';
$dbpass = 'xxxxx';
$dbname='xxxxx';


$con = mysql_connect("$dbhost","$dbuser","$dbpass");
mysql_select_db($dbname);

if (!$con)

  {
  die('Could not connect: ' . mysql_error());
  }
$sql="INSERT INTO gpscoordinates (UserName, Password1, Group1,Latitude,Longitude)
VALUES
('$_POST[User]','$_POST[Password1]','$_POST[Group1]','$_POST[Latitude]','$_POST[Longitude]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

mysql_close($con)
?> 

Open in new window


When I add the code changes suggested It does not work

if (isset($_POST['submit'])){

$user = mysql_real_escape_string($_POST['User']);
$password = mysql_real_escape_string($_POST['Password1']);
$group = mysql_real_escape_string($_POST['Group1']);
$latitude = mysql_real_escape_string($_POST['Latitude']);
$longitude = mysql_real_escape_string($_POST['Longitude']);

echo "User is $user<br />";
echo "password is $password<br />";
echo "Group is $group<br />";
echo "latitude is $latitude<br />";
echo "longitude is $longitude<br />";

$sql="INSERT INTO gpscoordinates (UserName, Password1, Group1,Latitude,Longitude)
VALUES ('$user','$password','$group','$latitude','$longitude') ON DUPLICATE KEY UPDATE (Latitude='$latitude', Longitude ='$longitude')";
                                 

}

Open in new window


it says Error: Query was empty any thoughts ?
Marco GasiFreelancer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Try to replace

if (isset($_POST['submit'])){

with

if ($_POST['User'] != '') {

Let me ask a thing: usually you get some value using $_POST[User], without quotes? If so, you can then try to not use quotes in code I have suggested. Isay this because ifthe message is 'Error Query was empty', this means that a query is executed and since the query code is within the if (isset($_POST['submit'])), then $_POST['submit'] is set. So maybe the error is in quotes: perhaps Android posts without quotes...

Summarizing, you can try two things:

- replace if (isset($_POST['submit'])){ with if ($_POST['User'] != '') {
- replace

          $user = mysql_real_escape_string($_POST['User']);
          $password = mysql_real_escape_string($_POST['Password1']);
          $group = mysql_real_escape_string($_POST['Group1']);
          $latitude = mysql_real_escape_string($_POST['Latitude']);
          $longitude = mysql_real_escape_string($_POST['Longitude']);

   with

          $user = mysql_real_escape_string($_POST[User]);
          $password = mysql_real_escape_string($_POST[Password1]);
          $group = mysql_real_escape_string($_POST[Group1]);
          $latitude = mysql_real_escape_string($_POST[Latitude]);
          $longitude = mysql_real_escape_string($_POST[Longitude]);

Hope this helps.
Most Valuable Expert 2011
Top Expert 2016

Commented:
Any thoughts?  Yes.  

Did you connect to the data base server before you used MySQL_Real_Escape_String()?  Did your echo statements come out OK?  What are the key fields?  Where is the rest of the code in the new version?  Please give us a little more to go on, and we can probably help.
Most Valuable Expert 2011
Top Expert 2016

Commented:
Not sure I like marqusG's suggestion about omitting the quotes (although I usually do like his suggestions).  The quotes are there to tell PHP that you want to use an explicit string or variable, and not a defined constant.

Right at the top of your script, add these statements and post the output from it back here so we can see it.

error_reporting(E_ALL);
var_dump($_POST);

Author

Commented:
My code as it stands now
error message I am getting with quotes around User
array(0) { }
Notice: Undefined index: User in /home/content/15/6807515/html/loaddata.php on line 20

Notice: Undefined variable: sql in /home/content/15/6807515/html/loaddata.php on line 47
Error: Query was empty

Open in new window

error message without quotes
array(0) { }
Notice: Use of undefined constant User - assumed 'User' in /home/content/15/6807515/html/loaddata.php on line 20

Notice: Undefined index: User in /home/content/15/6807515/html/loaddata.php on line 20

Notice: Undefined variable: sql in /home/content/15/6807515/html/loaddata.php on line 47
Error: Query was empty

Open in new window


<?php
error_reporting(E_ALL);
var_dump($_POST);
$dbhost = 'xxxxx';
$dbuser = 'xxxx';
$dbpass = 'xxxx';
$dbname='xxxx';


$con = mysql_connect("$dbhost","$dbuser","$dbpass");
mysql_select_db($dbname);

if (!$con)

  {
  die('Could not connect: ' . mysql_error());
  }

if  ($_POST['User'] != ''){

  $user = mysql_real_escape_string($_POST[User]);
          $password = mysql_real_escape_string($_POST[Password1]);
          $group = mysql_real_escape_string($_POST[Group1]);
          $latitude = mysql_real_escape_string($_POST[Latitude]);
          $longitude = mysql_real_escape_string($_POST[Longitude]);

echo "User is $user<br />";
echo "password is $password<br />";
echo "Group is $group<br />";
echo "latitude is $latitude<br />";
echo "longitude is $longitude<br />";

$sql="INSERT INTO gpscoordinates (UserName, Password1, Group1,Latitude,Longitude)
VALUES ('$user','$password','$group','$latitude','$longitude') ON DUPLICATE KEY UPDATE (Latitude='$latitude', Longitude ='$longitude')";
                                 

}

             

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

mysql_close($con)
?> 

Open in new window

Most Valuable Expert 2011
Top Expert 2016

Commented:
Where is the output from var_dump($_POST);?

Author

Commented:
that was the entire screen ....

array(0) { }
Notice: Undefined index: User in /home/content/15/6807515/html/loaddata.php on line 20

Notice: Undefined variable: sql in /home/content/15/6807515/html/loaddata.php on line 47
Error: Query was empty

Open in new window

Marco GasiFreelancer
CERTIFIED EXPERT
Top Expert 2010

Commented:
From your output I see the idea to omit quotes was a bad idea: I only tried without believe in it. I think you have to show the var_dump($_POST} output... Before the var_dump command place this

echo "<pre>";

This will make var_dump output more readable.
Marco GasiFreelancer
CERTIFIED EXPERT
Top Expert 2010

Commented:
array(0) { }? Well, then you are not receiving anything by your user phone...

Author

Commented:
stand by ... gonna send some data and post the results

Author

Commented:
I just send data to the site and this is still the error

array(0) { }
Notice: Undefined index: User in /home/content/15/6807515/html/loaddata.php on line 20

Notice: Undefined variable: sql in /home/content/15/6807515/html/loaddata.php on line 47
Error: Query was empty

Open in new window


then I changed the code back to
$sql="INSERT INTO gpscoordinates (UserName, Password1, Group1,Latitude,Longitude)
VALUES
('$_POST[User]','$_POST[Password1]','$_POST[Group1]','$_POST[Latitude]','$_POST[Longitude]')";

Open in new window


and it worked
Marco GasiFreelancer
CERTIFIED EXPERT
Top Expert 2010

Commented:
When you say 'It worked' do you mean that var_dump($_POST) was empty but a new row has been added to the database with correct values?

Author

Commented:
that is correct
Marco GasiFreelancer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Don't know if you're still interesting in understand what happen (since now it works), but if you do, I think we have to investigate how exactly you send data from your phone to your site: it seems to use constant instead of variables or strings and it doesn't seem to use array $_POST as I exepected it did. Can you explain or describe how the data submission works?

Author

Commented:
it only sort of worked ... it still is not updating existing data it is creating a new row leaving my database with thousands of entries when I only need the most recent ...I need to get the on duplicate key working .... I will post my android code that sends

Author

Commented:
the 2 piece of code I just posted are what I am currently using it works EXCEPT it keeps generating new rows in my database when All I want it to do is update if the user is already there and create if they are not ....



my android code that sends to my php page

private void putServerData(String gotBread, String teat, String lockerroom, String spotX, String spotY) {
		String db_url = "http://www.mypage.com/loaddata.php";

		InputStream is = null;
		ArrayList<NameValuePair> request = new ArrayList<NameValuePair>();
		request.add(new BasicNameValuePair("User", gotBread));
		request.add(new BasicNameValuePair("Password1", teat));
		request.add(new BasicNameValuePair("Group1", lockerroom));
		request.add(new BasicNameValuePair("Latitude", spotX));
		request.add(new BasicNameValuePair("Longitude", spotY));

		try {
			HttpClient httpclient = new DefaultHttpClient();

			Log.d("log_tag", "Got this far " + httpclient);

			HttpPost httppost = new HttpPost(db_url);

			httppost.setEntity(new UrlEncodedFormEntity(request));
			HttpResponse response = httpclient.execute(httppost);
			HttpEntity entity = response.getEntity();
			is = entity.getContent();

			String s = convertStreamToString(is);
			Log.d("log_tag", "Result: " + s);
		} catch (Exception e) {
			e.printStackTrace();
			Log.d("log_tag", "Error: " + e.toString());
		}// catch
	}// send

Open in new window

my original php code

<?php

$dbhost = 'xxxxx';
$dbuser = 'xxxxx';
$dbpass = 'xxxxx';
$dbname='xxxxxx';


$con = mysql_connect("$dbhost","$dbuser","$dbpass");
mysql_select_db($dbname);

if (!$con)

  {
  die('Could not connect: ' . mysql_error());
  }


$sql="INSERT INTO gpscoordinates (UserName, Password1, Group1,Latitude,Longitude)
VALUES
('$_POST[User]','$_POST[Password1]','$_POST[Group1]','$_POST[Latitude]','$_POST[Longitude]')";             

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

mysql_close($con)
?> 

Open in new window

Marco GasiFreelancer
CERTIFIED EXPERT
Top Expert 2010

Commented:
A last request: can you post the structure of your table? In phpMyAdmin you can do an export and copy and paste here only the CREATE statement of the dump file. Since the problem is the ON DUPLICATE KEY UPDATE, maybe a look at your tabel structure can help

Author

Commented:
Table structure for table `gpscoordinates`
--

CREATE TABLE `gpscoordinates` (
  `UserName` varchar(50) NOT NULL,
  `Password1` varchar(50) NOT NULL,
  `Group1` varchar(50) NOT NULL,
  `Latitude` varchar(50) NOT NULL,
  `Longitude` varchar(50) NOT NULL,
  `dp_datetimes` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  FULLTEXT KEY `UserName` (`UserName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `gpscoordinates`

Open in new window

Marco GasiFreelancer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Well, maybe we have found the problem: as youcan read in MySql reference, If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed (http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html). In your tabel you have a FULLTEXT KEY, but is not a primary key nor a unique key. Try to define it as UNIQUE and see if it works.

Cheers
Freelancer
CERTIFIED EXPERT
Top Expert 2010
Commented:
Hi, dolamitejenkins: didn't it work?

Author

Commented:
thank you ... it finally worked ... took me a day to get it exactly but thanks you where right
Marco GasiFreelancer
CERTIFIED EXPERT
Top Expert 2010

Commented:
I'm happy to help you. Good luck with your project.

Cheers

Author

Commented:
Thank you