Get the row number of a record that contains name

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 ...  
Hans Inge HagenOwner and programmerAsked:
Who is Participating?
 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
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
 
Ray PaseurCommented:
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
 
Dave BaldwinFixer of ProblemsCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Ray PaseurCommented:
Hey, Dave.  I am thinking our author is really asking about an AUTO_INCREMENT index column.
0
 
Dave BaldwinFixer of ProblemsCommented:
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
 
Ray PaseurCommented:
Not predictably, indeed.  And if it works at first it won't work for long!

:-)

All the best, ~Ray
0
 
Hans Inge HagenOwner and programmerAuthor Commented:
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
 
Hans Inge HagenOwner and programmerAuthor Commented:
Thanks, man.
0
 
Dave BaldwinFixer of ProblemsCommented:
You're welcome.
0
 
Ray PaseurCommented:
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
 
Hans Inge HagenOwner and programmerAuthor Commented:
Thanks ... I got the SitePoint book
0
 
Ray PaseurCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.