Solved

Get the row number of a record that contains name

Posted on 2011-09-22
12
407 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 110

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 83

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 110

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 83

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 110

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 83

Accepted Solution

by:
Dave Baldwin earned 50 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 83

Expert Comment

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

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 110

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

717 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