How do I get the rowID of all records found using Count(*) WHERE…

Just starting out? Need help? Post your questions and find answers here.
User avatar
Columbo
Enthusiast
Enthusiast
Posts: 303
Joined: Wed Sep 10, 2014 7:17 am
Location: Ontario Canada
Contact:

How do I get the rowID of all records found using Count(*) WHERE…

Post by Columbo »

How do I get the rowID of all records found using Count(*) FROM member WHERE lastname = '" + lname + "'")
http://www.oldtimeradiotoday.com - Listen to or download classic old time radio broadcasts.
User avatar
mk-soft
Always Here
Always Here
Posts: 5335
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

Re: How do I get the rowID of all records found using Count(*) WHERE…

Post by mk-soft »

select [ColumnName, ...] from [table] where [filters]
My Projects ThreadToGUI / OOP-BaseClass / EventDesigner V3
PB v3.30 / v5.75 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace / OneDrive
User avatar
Columbo
Enthusiast
Enthusiast
Posts: 303
Joined: Wed Sep 10, 2014 7:17 am
Location: Ontario Canada
Contact:

Re: How do I get the rowID of all records found using Count(*) WHERE…

Post by Columbo »

select [ColumnName, ...] from [table] where [filters]
I'm not sure how I should code that to get the rowid of each record found.

I tried this:

Code: Select all

lname = "Zorro"
 DatabaseQuery(#dbaseID, "SELECT count(*) FROM member Where lastname = '"+ lname +"'") 
 NextDatabaseRow(#dbaseID)
 maxRec = Val(GetDatabaseString(#dbaseID, 0))
maxRec will give me the total number of records found that match lname but I need to get the rowid of each of these records that match.
http://www.oldtimeradiotoday.com - Listen to or download classic old time radio broadcasts.
User avatar
mk-soft
Always Here
Always Here
Posts: 5335
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

Re: How do I get the rowID of all records found using Count(*) WHERE…

Post by mk-soft »

without "count"
the created table must be have a rowID as primary key ...
My Projects ThreadToGUI / OOP-BaseClass / EventDesigner V3
PB v3.30 / v5.75 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace / OneDrive
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: How do I get the rowID of all records found using Count(*) WHERE…

Post by captain_skank »

I may have misunderstood your first post, but using COUNT() will not return the ID no's - only the total number of records returned.

E.G

Code: Select all

SELECT Count(*) FROM member WHERE lastname = '" + lname + "'"
would return a single number ( being the total number of records meeting the specified criteria )

I assume your table has a column named ID so the query would look something like

Code: Select all

SELECT ID FROM member WHERE lastname = '" + lname + "'"
So your code would be something like ( I added a counter to display total records as well ) :

Code: Select all

  ; N.B : assumes you have opened a database connection using 0 as it's ID.

  LVAR_sql = "SELECT ID FROM member WHERE lastname = '" + lname + "'"
  LVAR_count.i = 0

  If DatabaseQuery(0, LVAR_sql)
    
    While NextDatabaseRow(0)
      LVAR_data.s = GetDatabaseString(0, 0)
      debug LVAR_data
      LVAR_count + 1
    Wend
    
    debug "Total records = " + str(LVAR_count)
    
    FinishDatabaseQuery(0)
    
  Else
    LVAR_msg.s = "D A T A B A S E   E R R O R" + #CRLF$ + #CRLF$
    LVAR_msg + LVAR_sql + #CRLF$ + #CRLF$
    LVAR_msg + "This SQL failed"
    Debug LVAR_msg
  EndIf
User avatar
Columbo
Enthusiast
Enthusiast
Posts: 303
Joined: Wed Sep 10, 2014 7:17 am
Location: Ontario Canada
Contact:

Re: How do I get the rowID of all records found using Count(*) WHERE…

Post by Columbo »

Thank you very much. I'll give that a try.

Cheers!
http://www.oldtimeradiotoday.com - Listen to or download classic old time radio broadcasts.
User avatar
Columbo
Enthusiast
Enthusiast
Posts: 303
Joined: Wed Sep 10, 2014 7:17 am
Location: Ontario Canada
Contact:

Re: How do I get the rowID of all records found using Count(*) WHERE…

Post by Columbo »

Thank you again captain_skank. That works fine.
http://www.oldtimeradiotoday.com - Listen to or download classic old time radio broadcasts.
Post Reply