Integer in queries

Error · #1 14-May-2007, 18:18

Whenever I try execute a query, such as

Code:

SELECT * FROM messages WHERE username='randomusername' AND index='8';

or any other query involving WHERE + an integer condition, MySQL says that there's a syntax error. Could someone explain what I'm doing wrong?

TurboPT · #2 14-May-2007, 19:24

Do you really have a column called index ?

That is also a reserved word in MySQL, and you CAN keep it, but you have to back-quote it when used in a query (and other places, see section 9.3 in the MySQL manual) as a field name:

Code:

SELECT * FROM messages WHERE username='randomusername' AND `index`='8';

See if that works, if not, please post the entire 'syntax error' message.

Error · #3 15-May-2007, 18:14

Thank you for the response
I do have a column named index, but the problem is not because of it being a keyword. Even with column with other column names, the database either returns a blank value, or

Code:

(Column name) not found in MySQL result index (num) in (file) on line (num)

when using mysql_result(). When trying to set an integer column in an UPDATE command, it just returns that there was an error in my syntax near

Code:

AND `index`='8'

The interesting thing is that I can read the column from an array after using mysql_fetch_array().

TurboPT · #4 15-May-2007, 19:37

Ok then, so post the complete UPDATE statement...

Your original post has a SELECT statement, which is a different operation.

Here is a sample interaction with a test table, containing a column named 'index':
(notice the SQL before and after the error messages)

Code:

begin:
mysql> SELECT * FROM filter;
+---------+-------+
| id      | index |
+---------+-------+
| 1.1.1.1 |     1 |
| 2.2.2.2 |     1 |
| 3.3.3.3 |     1 |
| 4.4.4.4 |     1 |
| 5.5.5.5 |     1 |
+---------+-------+
5 rows in set (0.00 sec)

un-quoted index:
mysql> UPDATE filter SET index=3;
ERROR 1064 (42000): 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 'index=3' at line 1

back-quoted index, with the same value:
mysql> UPDATE filter SET `index`=3;
Query OK, 5 rows affected (0.04 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> SELECT * FROM filter;
+---------+-------+
| id      | index |
+---------+-------+
| 1.1.1.1 |     3 |
| 2.2.2.2 |     3 |
| 3.3.3.3 |     3 |
| 4.4.4.4 |     3 |
| 5.5.5.5 |     3 |
+---------+-------+
5 rows in set (0.00 sec)

without back-quotes, new value:
mysql> UPDATE filter SET index=5;
ERROR 1064 (42000): 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 'index=5' at line 1

back-quotes added: (and keeping regular quotes around the value)
mysql> UPDATE filter SET `index`='5';
Query OK, 5 rows affected (0.05 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> SELECT * FROM filter;
+---------+-------+
| id      | index |
+---------+-------+
| 1.1.1.1 |     5 |
| 2.2.2.2 |     5 |
| 3.3.3.3 |     5 |
| 4.4.4.4 |     5 |
| 5.5.5.5 |     5 |
+---------+-------+
5 rows in set (0.00 sec)

Here's another example, with a WHERE clause:

Code:

mysql> SELECT * FROM filter;
+---------+-------+
| id      | index |
+---------+-------+
| 1.1.1.1 |     5 |
| 2.2.2.2 |     5 |
| 3.3.3.3 |     5 |
| 4.4.4.4 |     5 |
| 5.5.5.5 |     5 |
+---------+-------+
5 rows in set (0.00 sec)

mysql> UPDATE filter SET index=1 WHERE id='3.3.3.3';
ERROR 1064 (42000): 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 'index=1 where id='3.3.3.3'' at line 1

mysql> UPDATE filter SET `index`=1 WHERE id='3.3.3.3';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM filter;
+---------+-------+
| id      | index |
+---------+-------+
| 1.1.1.1 |     5 |
| 2.2.2.2 |     5 |
| 3.3.3.3 |     1 |
| 4.4.4.4 |     5 |
| 5.5.5.5 |     5 |
+---------+-------+
5 rows in set (0.00 sec)

Error · #5 16-May-2007, 17:01

Thank you. The only thing needed ended up being the back quotes. The other errors were caused by other things that I was able to fix.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Calculate Sum of each position of an integer	Dave101	C Programming Language	1	16-Feb-2007 01:06
Fortran problem...	Justin Fox	Miscellaneous Programming Forum	6	24-Oct-2006 15:30
Convert 'one hot' to integer	mikhail	C Programming Language	4	24-Sep-2006 07:44
how to make a 'INTEGER to BINARY' code?	justaHSstudent	C++ Forum	1	02-Oct-2005 14:22
Help with integer inputs	jrl134	C++ Forum	1	20-Feb-2005 00:36

Integer in queries

Re: Integer in queries

Re: Integer in queries

Re: Integer in queries

Re: Integer in queries