[Coderelli]

I'm trying to insert several variables into a table, and all of them succeed except one (Case). The difference with this variable is that it's a 6-digit number while the others are varchar. I've tried changing table's Case column to varchar and int with no luck. It's the first column in the table so I've tried making it the key and without the table having a key. Here's the code ... the code to insert the "Case" is near the bottom. This is probably something small but I've been messing with it for several hours and a fresh look will probably find it.

The error is: "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 'Case) VALUES ('744375')' at line 1"

<?php
mysql_connect ('localhost', 'tet', 'pw'); mysql_select_db ('xcelfilter');

//1. define search term
$searchterm = "Word";

//2. clear sorted table
mysql_query('TRUNCATE TABLE sorted');

//3. for each, if search term is in the description column, put line into sorted table.
$searchquery = "SELECT * FROM csv WHERE Description LIKE '%$searchterm%'";
$searchresult = mysql_query($searchquery) or die(mysql_error());
$row = mysql_fetch_row($searchresult);
if (mysql_num_rows($searchresult) == 0) 
	{echo "no rows found";}
	else {

while ($row = mysql_fetch_array($searchresult)) {
$case = $row['Case']; $case = mysql_real_escape_string($case); $case = stripslashes($case);
$detail = $row['Detail']; $detail = mysql_real_escape_string($detail); $detail = stripslashes($detail);
$summary = $row['Summary']; $summary = mysql_real_escape_string($summary); $summary = stripslashes($summary);
$a_description = $row['Description']; $a_description = mysql_real_escape_string($a_description); $a_description = stripslashes($a_description);
$name = $row['Name']; $name = mysql_real_escape_string($name); $name = stripslashes($name);

// not working
$query = "INSERT INTO sorted (Case)
	 VALUES ('$case')";

// works
//$query = "INSERT INTO sorted (Detail)
//	 VALUES ('$detail')";

// works
//$query = "INSERT INTO sorted (Summary)
//	 VALUES ('$summary')";

// works
//$query = "INSERT INTO sorted (Description)
//	 VALUES ('$a_description')";

// works
//$query = "INSERT INTO sorted (Name)
//	 VALUES ('$name')";

// the whole thing .. not working because it has the "Case" in it. 
//$query = "INSERT INTO sorted ('Case', 'Detail', 'Summary', 'Description', 'Name')
//	 VALUES ($case, $detail, $summary, $a_description, $name ) ";

mysql_query($query) or die(mysql_error());  
		  } // end while
		} // end else
?>

[JackOfAllTrades]

Field names, if they are to be quoted in the query, need to be backticked, not single-quoted. And integer values should not be quoted in the values.

INSERT INTO table (`field1`, `field2`) VALUES ('string_value', integer_value);

And WTF is this?

$detail = mysql_real_escape_string($detail);
$detail = stripslashes($detail);

Lose the stripslashes calls.

Think about moving away from the deprecated mysql_* functions and on to mysqli_ or PDO and using prepared statements.

[Coderelli]

Thanks, JackOfAllTrades.

This modified code isn't working either:

$query = "INSERT INTO sorted ('Case')
	 VALUES ($case)";

I wasn't sure about the quotes and have tried them every which way but it's good to know how to do it and that integers shouldn't be quoted. I also changed the column type to INT from varchar while testing this and neither way worked. I also removed the stripslashes.

I also tried:

$query = "INSERT INTO sorted (Case)
	 VALUES ($case)";

[andrewsw]

Case is a reserved word and definitely needs to be enclosed in back-ticks (for MySql); you are still using apostrophes.

Also, if $case is a string that it needs to be enclosed in apostrophes.

Did you make the changes you mentioned one-by-one? It is a pain if you make several (unrelated) changes at once, as you never discover the culprit.

You should re-read JackOfAllTrades 's post.

[Coderelli]

Hi, Andrewsw. One problem was that Case, as you pointed out, is a reserved word and I didn't know this. I changed Case to Location both in the script and in the table and it works now. Thanks so much because without your help I wouldn't have figured that out.

The other problem was what JackOfAllTrades pointed out, that integers shouldn't be quoted. Thanks a ton to both of you.