SQL total row

[faustf]

hi guy   i want  a  total row  of  one  table , i try  to use  this  command  but not  go   

_Connetti_DB_Mysql() ; apro db 
        $rsCustomers_MY = $objConnMY.Execute("SELECT COUNT (id_address) FROM ps_address") ; trovo il totale in ps address
        With $rsCustomers_MY
            While Not .EOF
                ConsoleWrite(.Fields("id_address").Value & @LF)
                ;$ps_Country_code_array[$b] = (.Fields("id_address").Value)
                $b += 1
                .MoveNext
            WEnd
            .Close
        EndWith

        _Close_DB_MYSQL() ;  chiudo db

some one can help me?   thankz 

 

[jchd]

The name of the column you want is undefined since there is no column alias to "COUNT (id_address)", so when you ask the value of the field id_address the ODBC driver doesn't know what you want.

You can do either give the column an alias name using the optional AS clause:

_Connetti_DB_Mysql() ; apro db 
        $rsCustomers_MY = $objConnMY.Execute("SELECT COUNT(*) as cnt FROM ps_address") ; trovo il totale in ps address
        With $rsCustomers_MY
            While Not .EOF
                ConsoleWrite(.Fields("cnt").Value & @LF)
                ;$ps_Country_code_array[$b] = (.Fields("cnt").Value)
                $b += 1
                .MoveNext
            WEnd
            .Close
        EndWith

        _Close_DB_MYSQL() ;  chiudo db

or address the field by index:

_Connetti_DB_Mysql() ; apro db 
        $rsCustomers_MY = $objConnMY.Execute("SELECT COUNT(*) FROM ps_address") ; trovo il totale in ps address
        With $rsCustomers_MY
            While Not .EOF
                ConsoleWrite(.Fields(1).Value & @LF)
                ;$ps_Country_code_array[$b] = (.Fields(1).Value)
                $b += 1
                .MoveNext
            WEnd
            .Close
        EndWith

        _Close_DB_MYSQL() ;  chiudo db

BTW are you sure you want to close the connection right after obtaining the total number of rows?

[faustf]

yes  i    think is much better if  after  select  or  work , with db  you  close it , or  not?? 

[jchd]

In the general use case, you init the engine, open one or more connection(s), perform the work and only close the connection(s) just before terminating.

Needlessly opening a new connection or reopening a connection previously closed is a waste of cycles, since the engine needs to re-parse the whole database schema everytime. It doesn't show much with DBs which contain just a couple tables and little around (few triggers, etc.) but it's very noticeable with DBs having hundreds or thousands of tables and a very complex schema.