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

I think it's telling you that you don't have a value there.

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

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' )

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);
}

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)
?> 

Select allOpen 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')";
                                 

}

Select allOpen in new window

it says Error: Query was empty any thoughts ?

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.

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.

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);

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

Select allOpen 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

Select allOpen 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)
?> 

Select allOpen in new window

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

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

Select allOpen in new window

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.

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

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

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

Select allOpen 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]')";

Select allOpen in new window

and it worked

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?

that is correct

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?

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

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

Select allOpen 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)
?> 

Select allOpen in new window

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

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`

Select allOpen in new window

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

thank you ... it finally worked ... took me a day to get it exactly but thanks you where right

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

Cheers

Thank you