?
Solved

Get the row number of a record that contains name

Posted on 2011-09-22
12
Medium Priority
?
408 Views
Last Modified: 2012-05-12
I am trying to get the specific row number where name="bill"  with PHP and MySQL.

Basically, I am logging event by "user_id", "event_id" and "booking_id" in a log Table .... when the user_id = "1122" then it will have to get the row number. (if 122 is row 7 it will echo "7")

.. well, I am struggling with my programming here ...  
0
Comment
Question by:Hans Inge Hagen
[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
  • 3
12 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36583855
If you're not sure how to do that, consider getting a foundation in PHP and MySQL.  This is a really good book for that purpose.
http://www.sitepoint.com/books/phpmysql4/

Your query would probably look like this:
SELECT row_number FROM my_table WHERE name = 'bill'
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 36584028
I'm curious what you have to work with here.  While 'row number' may be a valid attribute in a spreadsheet, there is no intrinsic meaning for it in any SQL database table.  The only predictable order in an SQL table is when you use ORDER BY to define a column to sort the results on.  This is because SQL databases are not kept in a row order like spreadsheets.  When you DELETE and INSERT rows, unused space will be used for the new data, even if it is in the beginning of the table.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36584039
Hey, Dave.  I am thinking our author is really asking about an AUTO_INCREMENT index column.
0
Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 36584054
If he is then it won't be a problem.  I've had a couple of questions where people thought SQL worked like spreadsheets and that won't work.  At least not predictably.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36584057
Not predictably, indeed.  And if it works at first it won't work for long!

:-)

All the best, ~Ray
0
 

Author Comment

by:Hans Inge Hagen
ID: 36585155
Thanks.

Here is my thing:

1. A member books a fitnessclass in a table named event_log. (fields: event_id, book_id, user_id, reg_date)
2. In a sheets (php) with multiple bookings (booking_table) I need all available booking with count and members place in the line. The reg_date and the book_id in the event_log table will show where in the que the member are.
0
 
LVL 84

Accepted Solution

by:
Dave Baldwin earned 200 total points
ID: 36585213
Then you need to use ORDER BY in your SQL statement to get a predictable order.

"SELECT * FROM event_log ORDER BY book_id, reg_date"

Or if 'reg_date' is more important:

"SELECT * FROM event_log ORDER BY reg_date, book_id"

Then in your PHP code that displays the rows, you can just add a counter and put that at the beginning of each display row.
0
 

Author Closing Comment

by:Hans Inge Hagen
ID: 36585238
Thanks, man.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 36585249
You're welcome.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36586402
Original question: I am trying to get the specific row number where name="bill"
Actual question: I need all available booking with count and members place in the line.

Before you write any more code, please buy that book from SitePoint!
0
 

Author Comment

by:Hans Inge Hagen
ID: 36586456
Thanks ... I got the SitePoint book
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36586721
You'll love the SitePoint book.  It's been a permanent part of my professional library since its first printing.  I recall that I took about a month to work through all the examples, and it put me miles ahead in the race to become proficient in PHP!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

752 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