You have an error in your SQL syntax AAAARRRGGGGGHHHHHH

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
devilgrendall
Forum Newbie
Posts: 17
Joined: Fri Sep 10, 2004 10:29 am
Location: Warwickshire
Contact:

You have an error in your SQL syntax AAAARRRGGGGGHHHHHH

Post 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:
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Any ideas because I'm bloody lost!
Any code we can see?
devilgrendall
Forum Newbie
Posts: 17
Joined: Fri Sep 10, 2004 10:29 am
Location: Warwickshire
Contact:

Post 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]
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

print out

Post 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";
devilgrendall
Forum Newbie
Posts: 17
Joined: Fri Sep 10, 2004 10:29 am
Location: Warwickshire
Contact:

Post 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
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post 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.
devilgrendall
Forum Newbie
Posts: 17
Joined: Fri Sep 10, 2004 10:29 am
Location: Warwickshire
Contact:

Post 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:
Post Reply