retrieve data by index on mysql database

Question

I would like to retrieve data from a given index. Here's the sql code of the database:

 CREATE TABLE `test` (
 `index` int(11) NOT NULL AUTO_INCREMENT,
 `user` text NOT NULL,
 PRIMARY KEY (`index`),
 UNIQUE KEY `index` (`index`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

I can retrieve data from user like this:

SELECT * FROM test WHERE user='bertrand'

but when I try with index like this:

'index'=1

it gives me nothing ! I don't understand what I'm doing wrong, any help would be appreciated, thanks.

it gives me this error when i remove quotes: #1064 - 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 LIMIT 0, 30' at line 1 — user2216280, Oct 26 '13 at 19:30
@user2216280. If you feel an answer answered your question, don't forget to accept it and vote up the answers that pointed you in the right direction. — Filipe Silva, Nov 01 '13 at 16:33

score 2 · Answer 1 · answered Oct 26 '13 at 19:29

2

Just remove quotes. Now it returns nothing because 'index' doesn't equal 1. If you wrap a value it quotes, it will be considered as a string value.

SELECT * FROM test WHERE index=1

EDIT: Just noticed index is a reserved keyword. Also you have to rename that column.

answered Oct 26 '13 at 19:29

Aycan Yaşıt

2,106
4
34
40

renaming it works like a charm !! thanks a lot guys – user2216280 Oct 26 '13 at 19:36

score 0 · Answer 2 · answered Oct 26 '13 at 20:29

0

You can use index in your query, but as it is a reserved keyword, you have to wrap it in backticks, not singlequotes:

SELECT * FROM test WHERE `index`=1

answered Oct 26 '13 at 20:29

Filipe Silva

21,189
5
53
68

retrieve data by index on mysql database

2 Answers2