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.

score 10 · Accepted Answer · answered Oct 10 '15 at 21:55

10

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.

answered Oct 10 '15 at 21:55

Yellow

126
1
7

keep in mind this works for MySQL, but other databases like sqlite don't support 'IF'. – fafrd Apr 03 '19 at 01:27

score 6 · Answer 2 · answered Jun 11 '16 at 23:03

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

score 5 · Answer 3 · edited Sep 02 '19 at 15:43

5

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

edited Sep 02 '19 at 15:43

Nik

2,885
2
25
25

answered Feb 08 '17 at 21:50

user2099484

4,417
2
21
9

i agree, this is good solution especially if we dont have the flexibility to modify the query from select * to select count(*) – srini Nov 21 '18 at 20:23

score 4 · Answer 4 · answered Jul 03 '16 at 21:15

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

}

answered Jul 03 '16 at 21:15

user2099484

4,417
2
21
9

1

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)` – Martin Niederl Nov 30 '17 at 19:39
Joining the statement string yourself is highly vulnerable to **SQL Injections**! – Martin Niederl Nov 30 '17 at 20:54
8

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. – user2099484 Dec 02 '17 at 10:13

score 0 · Answer 5 · answered Oct 10 '15 at 15:21

0

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.

answered Oct 10 '15 at 15:21

Gordon Linoff

1,242,037
58
646
786

I already used that ... it returning address ... `No of rows are : &{0xc82001a060 0x6b6d0 0xc82000e260 false [] 0xc8200141b0}` – Sohail Shaikh Oct 10 '15 at 15:27
2

Have you read the documentation on how to read results from a query? http://go-database-sql.org/retrieving.html – Gordon Linoff Oct 10 '15 at 15:30

Victor · Answer 6 · 2019-04-27T17:06:04.833

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

How to get number of rows by select query using mysql

6 Answers6