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.
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.
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.
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")
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
}
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
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(*)
$2")
fmt.Println(sample + "
")
fmt.Println(s)
}