Solved

Get the row number of a record that contains name

Posted on 2011-09-22
12
400 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
  • 5
  • 4
  • 3
12 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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 82

Expert Comment

by:Dave Baldwin
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
Hey, Dave.  I am thinking our author is really asking about an AUTO_INCREMENT index column.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 82

Accepted Solution

by:
Dave Baldwin earned 50 total points
Comment Utility
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
Comment Utility
Thanks, man.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
You're welcome.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
Thanks ... I got the SitePoint book
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

763 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now