473,320 Members | 1,817 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 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 2118
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: TheKeith | last post by:
I'm just learning php and set up a sample mysql db to practice with. I have the following script and cannot for the life of me figure out why it is printing each field of the row twice? I checked...
6
by: dev | last post by:
how create a temp table as a copy of a existing table and then update1 field and insert the hole temp table back in the existing table? please any help? if i have 10 fields in 1 record and...
5
by: Bob Bedford | last post by:
Does exist a next prev function in PHP/Mysql. I've a website with returns lot of records (say about 200). The user may browse trough the list or returned records, but he has to click the record...
11
by: grumfish | last post by:
I'm trying to add a row to a MySQL table using insert. Here is the code: connection = MySQLdb.connect(host="localhost", user="root", passwd="pw", db="japanese") cursor = connection.cursor()...
2
by: Shashikant Kore | last post by:
Hi, I am using MySQL for a table which will have 100M+ records, avg length of records being 130 bytes. When the number of records reach approx. 25M (and the file size close to 4GB), the rate of...
7
by: William Gill | last post by:
I have been trying to pass parameters as indicated in the api. when I use: sql= 'select * from %s where cusid = %s ' % name,recID) Cursor.execute(sql) it works fine, but when I try : sql=...
2
by: moller | last post by:
Im looking in to the possibility of moving from mySQL to an access database. My reasons are: (1) Database is single user. (2) Database local on users PC. (3) Database has only 8 tables where 4...
3
by: laredotornado | last post by:
Hi, I have two columns, both MySQL 4 DATETIME types ... TABLE1.depart_day TABLE2.depart_day and both are indexed. The problem is, all of TABLE1's dates have a time of midnight (e.g....
4
by: NancyJ | last post by:
Currently we have a database with a main table containing 3 million records - we want to increase that to 10 million but thats not a possibility at the moment. Nearly all 3 million records are...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.