Solved

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

Posted on 2012-03-10
27
376 Views
Last Modified: 2012-03-14
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

0
Comment
Question by:dolamitejenkins
  • 11
  • 10
  • 4
  • +2
27 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 37706243
I think it's telling you that you don't have a value there.
0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 37706339
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
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 37706413
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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 109

Expert Comment

by:Ray Paseur
ID: 37707192
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
 

Author Comment

by:dolamitejenkins
ID: 37707363
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
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 37707526
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 37707530
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 37707536
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
 

Author Comment

by:dolamitejenkins
ID: 37707604
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 37707650
Where is the output from var_dump($_POST);?
0
 

Author Comment

by:dolamitejenkins
ID: 37707662
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
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 37707665
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
 
LVL 31

Expert Comment

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

Author Comment

by:dolamitejenkins
ID: 37707674
stand by ... gonna send some data and post the results
0
 

Author Comment

by:dolamitejenkins
ID: 37707678
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
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 37707688
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
 

Author Comment

by:dolamitejenkins
ID: 37707695
that is correct
0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 37707714
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
 

Author Comment

by:dolamitejenkins
ID: 37707739
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
 

Author Comment

by:dolamitejenkins
ID: 37707794
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
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 37708512
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
 

Author Comment

by:dolamitejenkins
ID: 37708524
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
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 37708530
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
 
LVL 31

Accepted Solution

by:
Marco Gasi earned 500 total points
ID: 37713254
Hi, dolamitejenkins: didn't it work?
0
 

Author Closing Comment

by:dolamitejenkins
ID: 37723488
thank you ... it finally worked ... took me a day to get it exactly but thanks you where right
0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 37723510
I'm happy to help you. Good luck with your project.

Cheers
0
 

Author Comment

by:dolamitejenkins
ID: 37723512
Thank you
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PHP strings vs array errors 13 33
Logic behind "best rated" calculation 11 31
Extracting content from meta tag PHP MYHTML DOM 3 22
Echo Multiple values from multiple records 19 45
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question