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
363 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 82

Expert Comment

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

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
 
LVL 108

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 30

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 108

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 108

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 108

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 30

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 30

Expert Comment

by:Marco Gasi
ID: 37707671
array(0) { }? Well, then you are not receiving anything by your user phone...
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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 30

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 30

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 30

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 30

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 30

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 30

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now