Really need help detecting error

pedroteixeira07

Hi guys,

I really need your help detecting an error.. this code runs fine without giving any exceptions, If tried query by query in the phpAdmin and also works fine, but when I run it from my Android device the orientation is inserted in the database, the position is inserted in the database, the new orientation id is set to the Service_Pic .. the only thing that doesn't works.. is setting the position id to the Service_Pic and I have no ideia why.. it works in phpadmin and it works with the orientation that has the same structure.. anyway I'll show you the code to make it clear:

<?php 
mysql_connect("localhost", "root","root") or die(mysql_error()) ; 
mysql_select_db("db") or die(mysql_error()) ; 

// this values are retrieved from Android 
$id = $_POST['id'];
$latitude = $_POST['latitude'];
$longitude = $_POST['longitude'];
$orientation = $_POST['orientation'];

// Insert new information 
mysql_query("INSERT IGNORE INTO Orientation ( Orientation_ID, GPSImgDirection, GPSImgDirectionRef, Entries) VALUES ( '' , $orientation, '', 1) ");
$query1 = "SELECT Orientation_ID FROM Orientation WHERE GPSImgDirection=$orientation";	
$result1=mysql_query($query1);
		if (!$result1) {
    	echo 'Cant find orintation' . mysql_error();
		exit;
	    }$row1=mysql_fetch_row($result1);

mysql_query("INSERT IGNORE INTO Position ( Position_ID, GPSLatitude, GPSLongitude, Entries, GPSLatitudeRef, GPSLongitudeRef) VALUES (  '' , $latitude, $longitude , 1 , '', '') ");
$query2 = "SELECT Position_ID FROM Position WHERE GPSLatitude=$latitude AND GPSLongitude=$longitude";
$result2=mysql_query($query2);
		if (!$result2) {
    	echo 'Cant find position' . mysql_error();
		exit;
	    }$row2=mysql_fetch_row($result2);


	mysql_query("UPDATE Service_Pic SET Position_ID = $row2[0] WHERE ServicePic_ID=$id");

mysql_query("UPDATE Service_Pic SET Orientation_ID = $row1[0] WHERE ServicePic_ID=$id");


exit;										 
?>

I really hope it's just a silly mistake to get on with this.. thank you in advance

NogDog

I might streamline that a bit by getting rid of a couple of the queries and instead using mysql_insert_id(), plus the 2 updates can be done in one query. Then add some debug info -- at least for now:

<?php
mysql_connect("localhost", "root", "root") or die(mysql_error());
mysql_select_db("db") or die(mysql_error());
// this values are retrieved from Android
$id = $_POST['id'];
$latitude = $_POST['latitude'];
$longitude = $_POST['longitude'];
$orientation = $_POST['orientation'];
// Insert new information
$sql = "
INSERT IGNORE INTO Orientation (
   Orientation_ID,
   GPSImgDirection,
   GPSImgDirectionRef,
   Entries
) VALUES ( '' , $orientation, '', 1)
";
$result = mysql_query($sql) or
      die("Unable to insert orientation: " . mysql_error() . "<br />\n$sql");
$row1 = mysql_insert_id($result1); // just use insert_id function
$sql = "
INSERT IGNORE INTO Position (
   Position_ID,
   GPSLatitude,
   GPSLongitude,
   Entries,
   GPSLatitudeRef,
   GPSLongitudeRef
) VALUES (
   '' ,
   $latitude,
   $longitude ,
   1 ,
   '',
   ''
) ";
$result2 = mysql_query($sql) or
      die("Unable to insert position: " . mysql_error() . "<br />\n$sql");
$row2 = mysql_insert_id($result2);
$sql = "
UPDATE Service_Pic SET
   Position_ID = $row2,
   Orientation_ID = $row1
WHERE ServicePic_ID=$id
";
$result3 = mysql_query($sql) or
      die("Unable to update pic: " . mysql_error() . "<br />\n$sql");
exit;
?>

bradgrafelman

Just to add on to NogDog's post...

You should be using [man]mysql_insert_id/man instead of doing an additional SELECT statement because a race condition could cause you to SELECT the wrong ID. In other words, if two INSERT's occurred around the same time, the overhead of PHP executing the second query afterwards could cause you to select the ID of a row INSERT'ed after the one you're trying to retrieve.

Also note that user-supplied data should never be placed directly into a SQL query string, otherwise your code will be vulnerable to SQL injection attacks and/or just plain SQL errors. Instead, it must first be sanitized with a function such as [man]mysql_real_escape_string/man (for string data).