PureBasic Forums - English

Posted: **Sun Jun 13, 2021 9:52 pm**

How do I get the rowID of all records found using Count(*) FROM member WHERE lastname = '" + lname + "'")

Posted: **Sun Jun 13, 2021 10:22 pm**

select [ColumnName, ...] from [table] where [filters]

Posted: **Sun Jun 13, 2021 11:19 pm**

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:

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.

Posted: **Sun Jun 13, 2021 11:24 pm**

without "count"
the created table must be have a rowID as primary key ...

Posted: **Mon Jun 14, 2021 9:16 am**

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

Posted: **Tue Jun 15, 2021 12:10 am**

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

Cheers!

Posted: **Tue Jun 15, 2021 5:25 pm**

Thank you again captain_skank. That works fine.

PureBasic Forums - English

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

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

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

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

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

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

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

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