How to get number of rows by select query using mysql

Question

I'm new in golang. I want to create a login verification from MySQL db. I want a method like as in PHP mysqli_num_rows($res) == 1... I tried len(rows) or rows.Column() @fmt.Println("No of rows are :", rows) but it won't... The code which i tried ... (It is a dummy code)

rows, err := db.Query("select * from userLog where u_name = ? and u_pass = ?", uname, pswd)
if err != nil {
    log.Fatal(err)
}
fmt.Println("No of rows are :", rows)
defer rows.Close()

If you have another solution for login verification purpose then kindly suggest and explain it briefly Kindly help me out.

Answer 1

As mentioned, count(*) works fine and Scan makes it simpler still, e.g.:

func GetCount(schemadottablename string) int {
    var cnt int
    _ = db.QueryRow(`select count(*) from ` + schemadottablename).Scan(&cnt)
    return cnt 
}

You can, of course, use this with a where statement to "refine" the count, e.g.:

func GetCount(schemadottablename string, column string, value string) int {
    var cnt int
    _ = db.QueryRow(`select count(` + column + `) from ` + schemadottablename + ` where ` + column + `=?`, value).Scan(&cnt)
    return cnt 

}

Answer 2

As i understand it you need to check if user and password exist in database. If so you can do:

var isAuthenticated bool
err := db.QueryRow("SELECT IF(COUNT(*),'true','false') FROM userLog WHERE u_name = ? AND u_pass = ?", uname, pswd).Scan(&isAuthenticated)
if err != nil {
    log.Fatal(err)
} 

If database contains supplied user and password isAuthenticated will be set to true.

Answer 3

If you already executed a query db.Query("SELECT * FROM some_tbl") and have a rows iterator, then you can't extract the number of rows without iterating through it. As you see there nothing that returns the number of rows.

So the only thing you can do is to make a query to select the number of rows: db.Query("SELECT COUNT(*) FROM some_tbl")