(mysql) select and indices

[mike-nospam@darrettenterprises.com]

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

[Chris Hope]

Re: (mysql) select and indices

mike-nospam@darrette nterprises.com wrote:
[color=blue]
> 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?[/color]

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/

[jerry gitomer]

Re: (mysql) select and indices

mike-nospam@darrette nterprises.com wrote:[color=blue]
> 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
>[/color]
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

[Gordon Burditt]

Re: (mysql) select and indices

>Trying to write a php script where the user will enter the row number[color=blue]
>(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.
>
>Unfortunatel y, MySQL barfs on this type of instruction. Doesn't seem
>to want to let me access Index values...
>
>Any suggestions?[/color]

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

[NC]

Re: (mysql) select and indices

mike-nos...@darrette nterprises.com wrote:[color=blue]
>
> 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.[/color]

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...
[color=blue]
> Any suggestions?[/color]

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

[mike-nospam@darrettenterprises.com]

Re: (mysql) select and indices


Chris Hope wrote:[color=blue]
> mike-nospam@darrette nterprises.com wrote:
>[color=green]
> > Trying to write a php script where the user will enter the row[/color][/color]
number[color=blue][color=green]
> > (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[/color][/color]
seem[color=blue][color=green]
> > to want to let me access Index values...
> >
> > Any suggestions?[/color]
>
> 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/[/color]


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

[Michael Fesser]

Re: (mysql) select and indices

.oO(Chris Hope)
[color=blue]
>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"[/color]

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

Micha

[Lewis Shadoff]

Re: (mysql) select and indices

mike-nospam@darrette nterprises.com wrote:[color=blue]
> 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
>[/color]
If Index is a primary key you can use _rowid in SELECT statements to
reference it.

Lewis Shadoff