Solved

Get the row number of a record that contains name

Posted on 2011-09-22
12
406 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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.

739 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