471,337 Members | 1,053 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,337 software developers and data experts.

(mysql) select and indices

Trying to write a php script where the user will enter the row number
(index) of a record. Say, for example, user wants record 55:

[MySQL]: select * from MyTable where Index = "55"

where Index is an autoincrement value, and also an index.

Unfortunately, MySQL barfs on this type of instruction. Doesn't seem
to want to let me access Index values...

Any suggestions?

Thanks,

Mike Darrett

Jul 17 '05 #1
7 2011
mi*********@darrettenterprises.com wrote:
Trying to write a php script where the user will enter the row number
(index) of a record. Say, for example, user wants record 55:

[MySQL]: select * from MyTable where Index = "55"

where Index is an autoincrement value, and also an index.

Unfortunately, MySQL barfs on this type of instruction. Doesn't seem
to want to let me access Index values...

Any suggestions?


Index is a reserved word so you are better not to call your column
"Index". If you don't want to change the column name (although you
really should) then you need to add backticks around the column name
like so:

select * from MyTable where `Index` = "55"

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/
Jul 17 '05 #2
mi*********@darrettenterprises.com wrote:
Trying to write a php script where the user will enter the row number
(index) of a record. Say, for example, user wants record 55:

[MySQL]: select * from MyTable where Index = "55"

where Index is an autoincrement value, and also an index.

Unfortunately, MySQL barfs on this type of instruction. Doesn't seem
to want to let me access Index values...

Any suggestions?

Thanks,

Mike Darrett

Mike,

If you have a column named Index it is probably a reserved word
problem that will go away if you rename the column.

HTH
Jul 17 '05 #3
>Trying to write a php script where the user will enter the row number
(index) of a record. Say, for example, user wants record 55:

[MySQL]: select * from MyTable where Index = "55"

where Index is an autoincrement value, and also an index.

Unfortunately, MySQL barfs on this type of instruction. Doesn't seem
to want to let me access Index values...

Any suggestions?


Pick a column name that isn't a reserved word, or quote the
column name (with backquotes). This also applies to table
names especially if you insist on naming it `table`.

select * from `MyTable` where `Index` = "55";

For further examples look at the output of SHOW CREATE TABLE.

Gordon L. Burditt
Jul 17 '05 #4
NC
mike-nos...@darrettenterprises.com wrote:

Trying to write a php script where the user will enter
the row number (index) of a record. Say, for example,
user wants record 55:

[MySQL]: select * from MyTable where Index = "55"

where Index is an autoincrement value, and also an index.

Unfortunately, MySQL barfs on this type of instruction.
It is usually a sood idea to quote the exact error message.
"Barf" is very colorful, but unfortunately not technical
enough to see where the problem might be coming from...
Any suggestions?


Get rid of double quotes. In MySQL, numerical values
can be passed without enclosing, string values are
enclosed with single quotes. Also, 'INDEX' is a reserved
word in MySQL, so you should put in into backticks if
you have a field with that name. This should work:

SELECT * FROM MyTable WHERE `Index` = 55

Cheers,
NC

Jul 17 '05 #5

Chris Hope wrote:
mi*********@darrettenterprises.com wrote:
Trying to write a php script where the user will enter the row number (index) of a record. Say, for example, user wants record 55:

[MySQL]: select * from MyTable where Index = "55"

where Index is an autoincrement value, and also an index.

Unfortunately, MySQL barfs on this type of instruction. Doesn't seem to want to let me access Index values...

Any suggestions?


Index is a reserved word so you are better not to call your column
"Index". If you don't want to change the column name (although you
really should) then you need to add backticks around the column name
like so:

select * from MyTable where `Index` = "55"

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

Great! Thanks guys. Never would have guessed that it's a reserved
word, since MySQL allowed me to create the column... backticks work
great in this case.

Mike

Jul 17 '05 #6
.oO(Chris Hope)
Index is a reserved word so you are better not to call your column
"Index". If you don't want to change the column name (although you
really should) then you need to add backticks around the column name
like so:

select * from MyTable where `Index` = "55"


Don't quote numeric values.
Don't use SELECT * .

Micha
Jul 17 '05 #7
mi*********@darrettenterprises.com wrote:
Trying to write a php script where the user will enter the row number
(index) of a record. Say, for example, user wants record 55:

[MySQL]: select * from MyTable where Index = "55"

where Index is an autoincrement value, and also an index.

Unfortunately, MySQL barfs on this type of instruction. Doesn't seem
to want to let me access Index values...

Any suggestions?

Thanks,

Mike Darrett

If Index is a primary key you can use _rowid in SELECT statements to
reference it.

Lewis Shadoff
Jul 17 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by TheKeith | last post: by
5 posts views Thread by Bob Bedford | last post: by
11 posts views Thread by grumfish | last post: by
2 posts views Thread by Shashikant Kore | last post: by
7 posts views Thread by William Gill | last post: by
2 posts views Thread by moller | last post: by
3 posts views Thread by laredotornado | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.