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.

Mimetic

Answer 1

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.

Sunfish

keep in mind this works for MySQL, but other databases like sqlite don't support 'IF'. – Sackbut

Answer 2

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")

Cavorelievo

Answer 3

Here is a fairly efficient way to return the number of rows in a MySQL select in go:

rows, selerr := db.Query(sql, StartDate, EndDate) // Query
if selerr != nil { 
    fmt.Fprint(w, selerr); 
    return 
}
count := 0
for rows.Next() { 
    count += 1 
}

rows, _ := db.Query(sql, StartDate, EndDate) // Query again - no error 

Ruffner

i agree, this is good solution especially if we dont have the flexibility to modify the query from select * to select count(*) – Kalsomine

Answer 4

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 

}

Ruffner

That is exactly what you should NEVER do. Don't combine SQL statements yourself, always use prepared statements which are auto implemented in most of the SQL functions implemented in Go. e.g.: db.QueryRow("SELECT id, description FROM status WHERE id = ?", user.StatusID) – Phosphorate

Joining the statement string yourself is highly vulnerable to SQL Injections! – Phosphorate

There is an orthodoxy developing in this area that is becoming quite shrill. If the manipulations involved in assembling an SQL statement are entirely within the program, there is no more chance of injection than there is of the programmer using an erroneous/malicious value via a prepared statement. In my experience, some prepared statements do not work with complex SDQ statement assembly. Yes, if untrusted input is involved, not only use prepared statements but even then do not depend on them entirely. If trusted input is involved, then use your head. – Ruffner

Answer 5

The application language doesn't make a different. Use count(*):

select count(*) as cnt
from userLog
where u_name = ? and u_pass = ?;

Then read the value that the query returns.

Sphenogram

I already used that ... it returning address ... No of rows are : &{0xc82001a060 0x6b6d0 0xc82000e260 false [] <nil> 0xc8200141b0} – Mimetic

Have you read the documentation on how to read results from a query? go-database-sql.org/retrieving.html – Sphenogram

Answer 6

Just to add some insight into this subject, have you thought like having a general recipe that allows you to express any select statement into select count(*), that might be useful in pagination affairs and hopefully on what someone is looking for:

package main

import (
    "fmt"
    "regexp"
)

const sample = `select a,b,c
from <table>
where <conditions>`

func main() {
    var re = regexp.MustCompile(`(select)\b.[\s\S]*(from[\s\S]*)`)
    s := re.ReplaceAllString(sample, "$1 count(*)\n$2")
    fmt.Println(sample + "\n")
    fmt.Println(s)
}

https://play.golang.org/p/29Iiv1Ta-0_D

Mange