Page 1 of 1

You have an error in your SQL syntax AAAARRRGGGGGHHHHHH

Posted: Fri Sep 10, 2004 10:32 am
by devilgrendall
Hi,

I was wondering if anyone could help on this. I’ve made a php upload page for a mysql database. I got it working once uploading text info and jpgs but then the second time I tried it I got the error message below. The php version on the hosting is PHP Version 4.3.8
“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 '' at line 1”

Any ideas because I'm bloody lost!

Thanks


Jay
:roll:

Posted: Fri Sep 10, 2004 10:35 am
by Weirdan
Any ideas because I'm bloody lost!
Any code we can see?

Posted: Fri Sep 10, 2004 10:41 am
by devilgrendall
feyd | Please use

Code: Select all

tags when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


bit long sorry.

jay

Code: Select all

<?php
//************* Stops errors going to html screen
	error_reporting(0);
//**********************************************************	
	 $image1 = "";
	 $image2 = "";
	 $image3 = "";
	 $image4 = "";
	 $image5 = "";
	 $image6 = "";
	 $image7 = "";
	 $image8 = "";
	 $image9 = "";
	 $image10 = "";
	
// *****************************   File Upload logic *******************************

	function uploadpics($refnum)
{

		global $image1;
		global $image2;
		global $image3;
		global $image4;
		global $image5;
		global $image6;
		global $image7;
		global $image8;
		global $image9;
		global $image10;
		

		$validatefile = 0;
		
		$uploaddir = "temp/";
//		$refnum = 400;


		$invalidfile = 0;

		if ($invalidfile == 0){

			$indexcount = 1;

			for ($indexcount = 1; $indexcount <= 10; $indexcount++){

				//get the source file and open an index
				$sourcefile = "image" . $indexcount;
				// find the last "." in the file name
				$dotpos = strrpos($_FILES[$sourcefile]['name'],".");
				// extract the file extension
				$fileext = strtolower(substr($_FILES[$sourcefile]['name'],$dotpos + 1));
				$name = $refnum . "_pic" . $indexcount . "." . $fileext;
				//is the file extension NULL, if so no file has been selected
				if ($fileext <> ""){
					//set the file name in the data base

					if ($indexcount == 1){$image1 = $name;}
					if ($indexcount == 2){$image2 = $name;}
					if ($indexcount == 3){$image3 = $name;}
					if ($indexcount == 4){$image4 = $name;}
					if ($indexcount == 5){$image5 = $name;}
					if ($indexcount == 6){$image6 = $name;}
					if ($indexcount == 7){$image7 = $name;}
					if ($indexcount == {$image8 = $name;}
					if ($indexcount == 9){$image9 = $name;}
					if ($indexcount == 10){$image10 = $name;}
					
					//move the file to dev/temp
					
					if (move_uploaded_file($_FILES[$sourcefile]['tmp_name'], $uploaddir . $name)) {
					} else {
			    		print "File upload failure!  Debugging info:\n";
				    	print_r($_FILES);
					}
			
				}
			}
		}
}

// **************************** start of pic compression logic *************************

		for ($indexcount = 1; $indexcount <= 10; $indexcount++){
			$filedir = "temp/";
			$file = $refnum . "_pic" . $indexcount . ".jpg";
			$filesource = $filedir . $file;

			if (is_file($filedir . $file)) {
				$imsource = imagecreatefromjpeg($filedir . $file);
				$xsource = imagesx($imsource);
				$ysource = imagesy($imsource);

				if (strpos($file,"pic1") == true){

					$yratio = $ysource / $xsource;
					$xratio = $xsource / $ysource;

					//create the thumbnail file
					$imdestination = imagecreatetruecolor(100,100 * $yratio);
					imagecopyresized($imdestination,$imsource,0,0,0,0,100,100 * $yratio,$xsource,$ysource);
					imagejpeg($imdestination,"images/builingthumbs/" . $file,95);
					
				
					//create the larger file for details
					$imdestination = imagecreatetruecolor(420,420 * $yratio);
					imagecopyresized($imdestination,$imsource,0,0,0,0,420,420 * $yratio,$xsource,$ysource);
					imagejpeg($imdestination,"images/buildings/" . $file,70);
				}else{				

				$yratio = $ysource / $xsource;
				$xratio = $xsource / $ysource;
				
				//create the larger file for more pics
				$imdestination = imagecreatetruecolor(500,500 * $yratio);
				imagecopyresized($imdestination,$imsource,0,0,0,0,500,500 * $yratio,$xsource,$ysource);
				imagejpeg($imdestination,"images/buildings/" . $file,70);
				}
				unlink("temp/" . $file);
			}
		
		}

// DEFINE THE DATABASE SOURCE

if ($_POST[addbuilding]) {

			$host =  "localhost";
			$user = "********";
			$password = "********";
			$dbname = "********";
			$tablename = "********";

// validate the input fields
			$validate =  0;
			$messstatus="";

//insert a building into the database			
				$link = mysql_connect ($host, $user, $password);

//If all validations are OK then add to the database
			if ($validate == 0) {

			
				
				$sql = "INSERT INTO " . $tablename . " (date) values ('1')";
				if (mysql_db_query($dbname, $sql, $link)) {
					//added to db
					//get the reference auto inc			
					$refnum = mysql_insert_id();
				}else{
					$messstatus = "not inserted " . mysql_error();
					//failed to add to db
				}
				
					//upload any pictures
				uploadpics($refnum);
				
				
				$today = getdate();
				$datestring = $today['year'] . "-" . $today['mon'] . "-" . $today['mday'];
				
				$link = mysql_connect ($host, $user, $password);
				$sql = "UPDATE ". $tablename; 
				$sql = $sql . " SET buildingname='" . trim($_POST[buildingname]) . "', address='" . trim($_POST[address]) . "'";
				$sql = $sql . ", postcode='" . trim($_POST[postcode]) . "', city='" . trim($_POST[city]) . "'";
				$sql = $sql . ", county='" . trim($_POST[county]) . "', region='" . trim($_POST[region]) . "'";
				$sql = $sql . ", council='" . trim($_POST[council]) . "', country='" . trim($_POST[country]) . "'";
				$sql = $sql . ", status='" . trim($_POST[status]) . "', map='" . trim($_POST[map]) . "'";
				$sql = $sql . ", buildinguse='" . trim($_POST[buildinguse]) . "', style='" . trim($_POST[style]) . "'";
				$sql = $sql . ", architect='" . trim($_POST[architect]) . "', developer='" . trim($_POST[developer]) . "'";
				$sql = $sql . ", othercontractors='" . trim($_POST[othercontractors]) . "', height='" . trim($_POST[height]) . "'";
				$sql = $sql . ", roofheight='" . trim($_POST[roofheight]) . "', spireheight='" . trim($_POST[spireheight]) . "'";
				$sql = $sql . ", floors='" . trim($_POST[floors]) . "', topfloorheight='" . trim($_POST[topfloorheight]) . "'";
				$sql = $sql . ", occupied='" . trim($_POST[occupied]) . "', sqmetres='" . trim($_POST[sqmetres]) . "'";
				$sql = $sql . ", basement='" . trim($_POST[basement]) . "', plant='" . trim($_POST[plant]) . "'";
				$sql = $sql . ", constructioncost='" . trim($_POST[constructioncost]) . "', landcost='" . trim($_POST[landcost]) . "'";
				
				$sql = $sql . ", copy1='" . trim($_POST[copy1]) . "', otherinfo='" . trim($_POST[otherinfo]) . "'";
				$sql = $sql . ", copy3='" . trim($_POST[copy3]) . "', copy2='" . trim($_POST[copy2]) . "'";
				$sql = $sql . ", copy5='" . trim($_POST[copy5]) . "', copy4='" . trim($_POST[copy4]) . "'";
				$sql = $sql . ", copy6='" . trim($_POST[copy6]) . "', copy7='" . trim($_POST[copy7]) . "'";
				$sql = $sql . ", copy8='" . trim($_POST[copy8]) . "', copy9='" . trim($_POST[copy9]) . "'";
				$sql = $sql . ", copy10='" . trim($_POST[copy10]) . "'";
			
			
				$sql = $sql . ", image10='" . trim($_POST[image10]) . "', image9='" . trim($_POST[image9]) . "'";
				$sql = $sql . ", image8='" . trim($_POST[image8]) . "', image7='" . trim($_POST[image7]) . "'";
				$sql = $sql . ", image6='" . trim($_POST[image6]) . "', image5='" . trim($_POST[image5]) . "'";
				$sql = $sql . ", image4='" . trim($_POST[image4]) . "', image3='" . trim($_POST[image3]) . "'";
				$sql = $sql . ", image2='" . trim($_POST[image2]) . "', image1='" . trim($_POST[image1]) . "'";
				
				$sql = $sql . ", title10='" . trim($_POST[title10]) . "', title9='" . trim($_POST[title9]) . "'";
				$sql = $sql . ", title8='" . trim($_POST[title8]) . "', title7='" . trim($_POST[title7]) . "'";
				$sql = $sql . ", title6='" . trim($_POST[title6]) . "', title5='" . trim($_POST[title5]) . "'";
				$sql = $sql . ", title4='" . trim($_POST[title4]) . "', title3='" . trim($_POST[title3]) . "'";
				$sql = $sql . ", title2='" . trim($_POST[title2]) . "', title1='" . trim($_POST[title1]) . "'";
				
				
				$sql = $sql . ", insertdate='" . $datestring . "', date = '1'";
				
				$sql = $sql . " WHERE keyindex=" . $refnum ;

				if (mysql_db_query($dbname, $sql, $link)) {
					$messstatus = "Building added to database";
					//print $sql;
				}else{
					$status = $status . "Failed to add building " . $refnum . mysql_error();
					print mysql_error();
				}

				mysql_close($link);
			  }}
?>

feyd | Please use

Code: Select all

tags when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

print out

Posted: Fri Sep 10, 2004 10:48 am
by phpScott
try printing out your the query you are submitting as it might be easier to see syntax or logic errors.

print "query is $query";

Posted: Fri Sep 10, 2004 11:35 am
by devilgrendall
hi i ran the query and i got

Duplicate entry '' for key 1

just going to have to go over it again, godamnit

thanks for the pointer

jay

Posted: Fri Sep 10, 2004 12:42 pm
by patrikG
"Duplicate entry '' for key 1 " means that a column in your db-table is declared as UNIQUE and you're trying to insert a non-unique value (i.e. '' - nothing).

Check the query first (print_r) to find which column it is, then check your code to make sure there will be a unique value for that column in the insert statement.

Posted: Fri Sep 10, 2004 3:07 pm
by devilgrendall
hi,

i found the problem. I hadnt auto incremented my field, teach me to create the database in the wee hours of the morning. Although now have a problem with the file compression, but im sure i can sort that.

Thanks for all your quick responses. This a great forum, no doubt i'll be back again.

Cheers people.


jay :wink: