Solved

Get real row number of specific row in database table

Posted on 2014-02-20
15
656 Views
Last Modified: 2014-02-21
Hi,

Couldn't find much help on Google with this question.

I have a mysql table with many rows. Each row has an ID, but when rows get deleted and added, the ID doesn't match the 'real' row number anymore.

Is there a way to get a row's position in the table?

For example, row with id 101 is the 88th 'real' row in the table.

Hope i explained correctly...
How can this be achieved?
0
Comment
Question by:peps03
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +4
15 Comments
 
LVL 12

Expert Comment

by:Gary Dewrell
ID: 39873765
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39873776
This question has a question smell -- similar to a code smell -- that indicates there may be more to the story than we can see in the question.  Why do you want to do this?  Not the technical reason, but just the business reason.
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 39873798
I agree with ray here, the actual position in the database is usually irrelevant as the database can store rows however it wants and only sorts them if you ask it to. Are you asking a question like "how many orders were placed before this one?"
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:peps03
ID: 39873819
Thanks for your reactions.

@gdewrell, yes, i saw that page. Didn't get it working / don't understand it.
Could you show me a working example?

@Ray Paseur well, each row stands for a page. Say i have 20 pages (rows), then i would like to show content A on the first 5 pages, content B on the seconds 5 pages (rows) and so on..
so i thought: if i know the total number of rows, and the 'real' row number, i know whether to display content A/b/c or d.
0
 
LVL 42

Expert Comment

by:pcelba
ID: 39873820
Simply said SQL data engines do not need the row number for data access optimization.

The physical record order is not important in properly designed data and you should use ORDER BY clause or ROW_NUMBER (MS SQL specific) in SQL commands to achieve the output order requested.  

xBase architecture (dBase, FoxPro) started without SQL and the physical record order was more important.

So your options are:

1) MS Visual FoxPro or other xBase clone. Each record of the fixed size has given the position in a DBF file. You may reorder records physically in the way you need. Each record is also accessible by its unique record number. Records do not need IDs but it is highly recommended to have some because data are easir to maintain...

2) Create clustered index based on the ID column. This column ensures the physical record order in a table. But I would guess the clustered index is also MS SQL specific feature.

3) Always use ORDER BY YourIDcolumn  in all queries.  This is the way SQL engine was designed for.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39873892
Hmm... Wouldn't your PHP script prepare one page at a time?  If so, you might just keep the page number in the session array.  That would let you keep track on a client-by-client basis.

Tell us a little more about your advertising strategy, please.
0
 

Author Comment

by:peps03
ID: 39873982
Wouldn't your PHP script prepare one page at a time?
> yes, it will.

I'm looking for a method to display content A on page 1-5, content B on page 6-10, content C on page 11-15 and so on.

Number ranges can be larger, or in percentages.

How could i achieve that? I thought getting the 'real' row number would be the easiest.
> 20 rows in table, page has id X which has real row number Y so display content C
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39874215
Here's where I am confused.  Let's say you have something on "page 1" that I like, so I post a link to it on Facebook.  And lots of my friends visit "page 1" so they will see the "content A" displayed.  None of them will ever get to page 2, or page 6 or page 14, etc.

Is that OK with you?  Or would you like to have the first five visits to "page 1" show "content A" then the next five visits show "content B" etc?
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 39874324
ok, so suppose you have a bunch of rows which are added and deleted from time to time, and you want to know where the current row is in the list of rows. Start off with a very basic table:

CREATE TABLE t (
id int,
dateadded datetime )

if you want to know how many rows were added before the row with id=8

SELECT COUNT(*) FROM t WHERE dateadded<(SELECT dateadded where id=8)

if you want the total number of rows,

SELECT COUNT(*) FROM t
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39874418
SQL database tables do Not have a 'real row number'.  They are not organized like a spreadsheet with fixed rows.  If you delete a row, the next newly inserted row may take it's place.  The only 'guaranteed' order is by using an ORDER BY clause in your SELECT statement like this:

SELECT * FROM yourtable ORDER BY id

https://dev.mysql.com/doc/refman/5.0/en/select.html
0
 

Author Comment

by:peps03
ID: 39874584
@mankowitz thanks!
I'll try your method. I have a time added column in the table.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39875244
Can you please help us understand what your objective is here?  Is this an ad-rotator that is trying to distribute advertisements onto the pages?  If not, please explain your objective in plain, non-technical language -- nothing about "page numbers" or any of that -- just an explanation of the business goals for the web site.  I think if we understand the business goals we may be able to help you get a technical solution that will meet those goals.
0
 
LVL 33

Expert Comment

by:ste5an
ID: 39876236
As the others already wrote, your descriptions are pretty ambiguous. But maybe you are simply looking for the limit clause.
0
 

Author Closing Comment

by:peps03
ID: 39876923
@mankowitz thanks for the solution, it works great using the time column.

@Ray Paseur and others, yes, it is meant to show certain ads only on certain pages. Some adds need to be shown on 20% of the pages some on 60% and so on.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39877000
Ahh, now that we know this is an ad rotator, we have a better chance of helping.  Details like that greatly enhance your chances of getting a good answer.  Maybe you would want to think about posting a new question with all of the information.  This is a very well-understood process; it uses a data base because you not only have to serve the ads, you have to keep records of who saw them.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question