PHP/SQL query returning error.

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
trent2800
Forum Commoner
Posts: 48
Joined: Mon Oct 02, 2006 7:02 am

PHP/SQL query returning error.

Post by trent2800 »

Hello, I've been trying to wrap my brain around what exactly is the problem with this query. If anyone could take a quick peek at it and tell me what exactly is my problem with this, that would be great. Thanks.


The following code returns:

Query failed: 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 'Desc = 'This is a test hoodie, for testing.', Bodice = '1',Spin = '1', Length = ' at line 1 Whole Query: UPDATE CLOTHING SET Name = 'Test Hoodie', ArtistID = '2', CategoryID = '4',Desc = 'This is a test hoodie, for testing.', Bodice = '1',Spin = '1', Length = '1', Waist = '1', Inseam = '1', Chest = '1', Sold = '0', Price = '34.00' WHERE ProductID = '3'

Code: Select all

//I edited out the user/pass for security reasons, the connection is not the problem.

			$dbh=mysql_connect ("localhost", "user", "pass") or die ('I cannot connect to the database because: ' . mysql_error());
		
		mysql_select_db ('guttersg_ggoods');
		
//I'm sure that there is a better way to do this, suggestions welcome.

		$ProductID = mysql_real_escape_string($_POST['ProductID']);
		$Name = mysql_real_escape_string($_POST['Name']);
		$Price = mysql_real_escape_string($_POST['Price']);
		$Sold = mysql_real_escape_string($_POST['Sold']);
		$ArtistID = mysql_real_escape_string($_POST['ArtistID']);
		$CategoryID = mysql_real_escape_string($_POST['CategoryID']);
		$Desc = mysql_real_escape_string($_POST['Desc']);
		$Bodice = mysql_real_escape_string($_POST['Bodice']);
		$Spin = mysql_real_escape_string($_POST['Spin']);
		$Length = mysql_real_escape_string($_POST['Length']);
		$Waist = mysql_real_escape_string($_POST['Waist']);
		$Inseam = mysql_real_escape_string($_POST['Inseam']);
		$Chest = mysql_real_escape_string($_POST['Chest']);
		$front = $_FILES['front']['name'];
		$back = $_FILES['back']['name'];
		$detail1 = $_FILES['detail1']['name'];
		$detail2 = $_FILES['detail2']['name'];
		
//This is a seperate issue that I'm having with my upload script.  I'm just outputting this for debugging.

		if (!$front=='') {
			
			echo $_FILES['front']['name']."\n";
			echo $_FILES['front']['tmp_name']."\n";
			echo $_FILES['front']['type']."\n";
			echo $_FILES['front']['size']."\n";
		}
		
		if (!$back=='') {
			
			echo $_FILES['back']['name']."\n";
			echo $_FILES['back']['tmp_name']."\n";
			echo $_FILES['back']['type']."\n";
			echo $_FILES['back']['size']."\n";
		}
		
		if (!$image_detail1=='') {
			
			echo $_FILES['detail1']['name']."\n";
			echo $_FILES['detail1']['tmp_name']."\n";
			echo $_FILES['detail1']['type']."\n";
			echo $_FILES['detail1']['size']."\n";
		}
		
		if (!$detail2=='') {
			
			echo $_FILES['detail2']['name']."\n";
			echo $_FILES['detail2']['tmp_name']."\n";
			echo $_FILES['detail2']['type']."\n";
			echo $_FILES['detail2']['size']."\n";
		}
		
		
//This is the real problem, if for future reference, there's a more readable way of doing this query; please tell me.
		
		$query = "UPDATE CLOTHING SET Name = '".$Name."', ArtistID = '".$ArtistID."', CategoryID = '".$CategoryID."',Desc = '".$Desc."', Bodice = '".$Bodice."',Spin = '".$Spin."', Length = '".$Length."', Waist = '".$Waist."', Inseam = '".$Inseam."', Chest = '".$Chest."', Sold = '".$Sold."', Price = '".$Price."' WHERE ProductID = '" . $ProductID . "'";
		
		mysql_query($query) or die('Query failed: ' . mysql_error() . " Whole Query: " . $query);
		
		echo $name." has been updated!";
		echo "<a href=index.php?mode=clothing>Back</a>\n";
	}
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

desc is a reserved word in MySQL.

try enclosing it in backticks (`) (the same key as your tilde key).
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

If you're gonna use double quotes for strings, you might as well use their feature of automatic variable substitution, and make your query a lot more readable.
trent2800
Forum Commoner
Posts: 48
Joined: Mon Oct 02, 2006 7:02 am

Post by trent2800 »

Mordred wrote:If you're gonna use double quotes for strings, you might as well use their feature of automatic variable substitution, and make your query a lot more readable.
Your theories intrigue me, I would like to subscribe to your newsletter.

err - Do you have a link? I have no idea how that works. :)

ED (silly me, where are my manners) - Thanks a lot burrito, I'll test that just as soon as I get to work.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Code: Select all

$query = "UPDATE
		CLOTHING
	SET
		Name = '$Name',
		ArtistID = '$ArtistID',
		CategoryID = '$CategoryID',
		`Desc` = '$Desc',
		Bodice = '$Bodice',
		Spin = '$Spin',
		Length = '$Length',
		Waist = '$Waist',
		Inseam = '$Inseam',
		Chest = '$Chest',
		Sold = '$Sold',
		Price = '$Price'
	WHERE
		ProductID = '$ProductID'";
and the link: http://www.php.net/manual/en/language.t ... tax.double

btw: why CategoryID but Desc (and not Description)?
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

trent2800 wrote:Your theories intrigue me, I would like to subscribe to your newsletter.
Ah, all great ununderstood minds are doomed to be ridiculed, such is the price of genius... :)

Seriously, there is a difference, go read it up. See how much more readable volka's example is. Or, if you would go about speed, use single quotes, it will make a difference in a tight loop in a multi user app.
trent2800
Forum Commoner
Posts: 48
Joined: Mon Oct 02, 2006 7:02 am

Post by trent2800 »

volka wrote: btw: why CategoryID but Desc (and not Description)?
I am selectively lazy. :)
Post Reply