4 Replies - 454 Views - Last Post: 27 March 2013 - 03:59 PM Rate Topic: -----

#1 Coderelli  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 27-March 13

Unable to insert digits into table

Posted 27 March 2013 - 03:13 PM

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
?>



Is This A Good Question/Topic? 0
  • +

Replies To: Unable to insert digits into table

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6111
  • View blog
  • Posts: 23,672
  • Joined: 23-August 08

Re: Unable to insert digits into table

Posted 27 March 2013 - 03:19 PM

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.
Was This Post Helpful? 1
  • +
  • -

#3 Coderelli  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 27-March 13

Re: Unable to insert digits into table

Posted 27 March 2013 - 03:30 PM

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)";


Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3838
  • View blog
  • Posts: 13,596
  • Joined: 12-December 12

Re: Unable to insert digits into table

Posted 27 March 2013 - 03:41 PM

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.
Was This Post Helpful? 1
  • +
  • -

#5 Coderelli  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 27-March 13

Re: Unable to insert digits into table

Posted 27 March 2013 - 03:59 PM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1