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

dolamitejenkinsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
I think it's telling you that you don't have a value there.
0
Marco GasiFreelancerCommented:
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
0
Beverley PortlockCommented:
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' )
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Ray PaseurCommented:
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);
}
0
dolamitejenkinsAuthor 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 ?
0
Marco GasiFreelancerCommented:
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.
0
Ray PaseurCommented:
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.
0
Ray PaseurCommented:
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);
0
dolamitejenkinsAuthor 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

0
Ray PaseurCommented:
Where is the output from var_dump($_POST);?
0
dolamitejenkinsAuthor 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

0
Marco GasiFreelancerCommented:
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.
0
Marco GasiFreelancerCommented:
array(0) { }? Well, then you are not receiving anything by your user phone...
0
dolamitejenkinsAuthor Commented:
stand by ... gonna send some data and post the results
0
dolamitejenkinsAuthor 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
0
Marco GasiFreelancerCommented:
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?
0
dolamitejenkinsAuthor Commented:
that is correct
0
Marco GasiFreelancerCommented:
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?
0
dolamitejenkinsAuthor 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
0
dolamitejenkinsAuthor 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

0
Marco GasiFreelancerCommented:
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
0
dolamitejenkinsAuthor 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

0
Marco GasiFreelancerCommented:
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
0
Marco GasiFreelancerCommented:
Hi, dolamitejenkins: didn't it work?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dolamitejenkinsAuthor Commented:
thank you ... it finally worked ... took me a day to get it exactly but thanks you where right
0
Marco GasiFreelancerCommented:
I'm happy to help you. Good luck with your project.

Cheers
0
dolamitejenkinsAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.