Page 1 of 1

PHP/SQL query returning error.

Posted: Tue Oct 03, 2006 12:26 pm
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";
	}

Posted: Tue Oct 03, 2006 12:29 pm
by Burrito
desc is a reserved word in MySQL.

try enclosing it in backticks (`) (the same key as your tilde key).

Posted: Tue Oct 03, 2006 12:52 pm
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.

Posted: Tue Oct 03, 2006 1:36 pm
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.

Posted: Tue Oct 03, 2006 2:25 pm
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)?

Posted: Wed Oct 04, 2006 2:28 am
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.

Posted: Wed Oct 04, 2006 9:27 am
by trent2800
volka wrote: btw: why CategoryID but Desc (and not Description)?
I am selectively lazy. :)