• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Calling a row

 
James Eman
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i have been looking on the web for a few hours and haven't found a clear answer to my problem. is there a mysql command i can use to get the data from a row and a column like Select row 6 column Usernames. is there a simple command i just have been other looking.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34071
331
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
James,
mysql has a limit clause you can use to get a certain row # within your query.


Do note that the order rows are stored in a table isn't guaranteed to be forever. It's more useful when doing paging because you have an order by clause in the query.


From: http://dev.mysql.com/doc/refman/5.5/en/select.html
With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
 
chris webster
Bartender
Posts: 2407
32
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As Jeanne says, relational databases do not make any guarantees about the order in which rows are stored in a table or returned by a query. If you want to fetch rows in a particular order, you specify that order using an "ORDER BY..." clause in your query. If you want to fetch a particular row from a table, you tell the database how to identify that row via the WHERE clause. And you tell the database which columns to return for each row by specifying them in the SELECT clause of your query.

And use the LIMIT clause, as Jeanne says, to fetch a certain number of ordered rows at a time.
 
James Eman
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I was looking for a way to check the username and password match is there a better way then to call each row one by one?
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64613
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why do you need to check each row? Let the database do the work for you.

select count(*) from users where username=? and password=?

If the count is 0, the record doesn't exist.
 
James Eman
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So it should look something like this?

 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34071
331
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Almost.

This won't compile as rs.next() returns a boolean to say whether there are any rows returned.


Since there will always be one row (and one column) returned for a count, you use
 
chris webster
Bartender
Posts: 2407
32
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne Boyarsky wrote:Almost.

This won't compile as rs.next() returns a boolean to say whether there are any rows returned.


Since there will always be one row (and one column) returned for a count, you use


If you are simply checking whether a row exists, and you don't actually want any data from the row, then you could just do "SELECT 1 FROM...". This will return a 1 as soon as a row is found, so it may be quicker than "SELECT COUNT(*)..." which always has to search the whole table (or index) to get the total count, even if there's only one matching row. If you get anything in your result set, you know a matching row was found. If the result set is empty, then you know there is no matching record.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic