How to append sql result set to an array

Getting Help
1

My structure is below:

type Year struct {
    Yname string
}

type Data struct {
        Years   []*Year
}

And a Query:

selDB1, err := db.Query("SELECT DISTINCT YEAR(Rdate) as years FROM report_monthly  ORDER BY report_monthly_id DESC")
if err != nil {
    panic(err.Error())
}

for selDB1.Next() {
    var years string
    err = selDB1.Scan(&years)
    if err != nil {
        panic(err.Error())
    }

How to append SQL result set to an array (res)?
After I am executing a template:

tmpl.ExecuteTemplate(w, "IndexReport", res)

And in my Template I want to implement:

{{range .Years}}
YEAR IS: {{.Yname}}
{{end}}
2

As you would always append on a slice… s := append(s, years) in the loop.

1 Like
3

Hi in my case it’s a little complicated for me, I doing this code but getting errors: I am new to golang . Can you please help?

res := Data{}
selDB1, err := db.Query("SELECT DISTINCT YEAR(Rdate) as years FROM report_monthly  ORDER BY report_monthly_id DESC")
if err != nil {
    panic(err.Error())
}

for selDB1.Next() {
    var years string
    err = selDB1.Scan(&years)
    if err != nil {
        panic(err.Error())
    }

    f1 := Year{Yname: years}
    res = append(res, f1)
}
4

Which ones?

Also res is a struct, you can not append to it. You need to append to its Years field.

1 Like
5

Thanks NobbZ, When I run it I am getting error ( :
[root@abc report_m]# go run main.go
# command-line-arguments
./main.go:129:21: first argument to append must be slice; have struct { Yname string; Selected bool }
./main.go:133:17: undefined: f1
This is my code:
Can you please look at it and give your expert point, where I am doing wrong…

func ShowMonth(w http.ResponseWriter, r *http.Request) {
    db := dbConn()
    res := Year{}
    selDB1, err := db.Query("SELECT DISTINCT YEAR(Rdate) as years FROM report_monthly  ORDER BY report_monthly_id DESC")
    if err != nil {
        panic(err.Error())
    }

    for selDB1.Next() {
        var years string
        err = selDB1.Scan(&years)
        if err != nil {
            panic(err.Error())
        }

        f1 := Year{Yname: years}
        res = append(res, f1)
    }

    log.Println(f1)
  // tmpl3.ExecuteTemplate(w, "IndexReport", f1)
    defer db.Close()
}
6

I am not sure how those line numbers map to the code you gave us, so I have to guess.

129 is probably res = append(res, f1), and I already told you why that won’t work in my last post. The error message does tell you exactly the same.

133 is probably log.Println(f1), and when you reach that line you do not have f1 in scope, since its only defined in the last 2 lines of the loops body.

edit

What type do you want res to actually have? Is it meant to be of type Data or []Year?

If the first its res.Years = append(res.Years, &years), if the latter its res = append(res, years) but you need to res := make([]Year, 0) instead of res := Year{}.

1 Like
7 
type Month struct {
    Mname string
    Color string
    Id  int
}


type Year struct {
    Yname, Color string
    Selected bool
}

type Data struct {
        Years   []*Year
        Months  []*Month
}


var tmpl3 = template.Must(template.ParseGlob("report/*"))

func ShowMonth(w http.ResponseWriter, r *http.Request) {
    db := dbConn()
///////////////////////////////////////////////////////////////////////////
    if r.Method == "POST" {
       years := r.FormValue("years")
 
    selDB, err := db.Query("SELECT MONTHNAME(Rdate) as months, color, report_monthly_id as id FROM report_monthly WHERE YEAR(Rdate)=?", years )
    if err != nil {
        panic(err.Error())
    }
    resm := []Month{}
    for selDB.Next() {
        var months, color string
        var id int
        err = selDB.Scan(&months, &color, &id)
        if err != nil {
            panic(err.Error())
        }
        
        rep := Month{Mname: months, Color: color, Id: id}
        resm = append(resm, rep)


    }    
///////////////////////////////////////////////////////////////////////////
    

    selDB1, err := db.Query("SELECT DISTINCT YEAR(Rdate) as years, color FROM report_monthly  ORDER BY report_monthly_id DESC")
    if err != nil {
        panic(err.Error())
    }
    res := []Year{}
    for selDB1.Next() {
        var years, color string
        err = selDB1.Scan(&years, &color)
        if err != nil {
            panic(err.Error())
        }

        rep := Year{Yname: years, Color: color}
        res = append(res, rep)
    }

    // f2 := Month{Mname: "FEB", Color: "BLUE"}
    // f3 := Year{Yname: "2018", Selected: false}
   
    person := Data{Years:  []*Year{&res},
                   Months: []*Month{&resm}}

    // log.Println(resm)
    log.Println(person)
    tmpl3.ExecuteTemplate(w, "IndexReport", person)
    defer db.Close()
}
}
8

Sorry for the lack of information, above is the full code, as you see I have 2 queries and the result set of them are successfully added to res and resm arrays. Then I want to add them to a person struct and send it to IndexReport template. Thats what I want in a result…

The error I am getting is regarding person struct:

[root@abc report_m]# go run main.go 
# command-line-arguments
./main.go:132:36: cannot use &res (type *[]Year) as type *Year in array or slice literal
./main.go:133:37: cannot use &resm (type *[]Month) as type *Month in array or slice literal
[root@abc report_m]#

Here I need help,Thanks in advance.

9

Even what you have posted seems not to be the complete file, at least the package declaration and the imports are missing, therefore we have an unknown offset on the linenumbers.

I do think though, that the quoted error message is talking about those 2 lines:

    person := Data{Years:  []*Year{&res},
                   Months: []*Month{&resm}}

res is of type []Year, resm of type []Month.

[]*Year{&res} tries to create a slice of *Year with a single element &res which is of type *[]Year, this does not fit into each other.

It seems as if you need to rethink how you set up your types and intermediate representations and should especially try to make them match.

1 Like
10

Thanks @NobbZ , Following is the complete code. And beside the mentioned code everything works fine.
I just need to send two arrays of different structure to a template… It took me many days and I’m realy stuck in here…

11 
package main

import (
    "database/sql"
    "log"
    "net/http"
    "text/template"
    _ "github.com/go-sql-driver/mysql"
)

////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////

func dbConn() (db *sql.DB) {
    dbDriver := "mysql"
    dbUser := "root"
    dbPass := "1"
    dbName := "report_m"
    db, err := sql.Open(dbDriver, dbUser+":"+dbPass+"@/"+dbName)
    if err != nil {
        panic(err.Error())
    }
    return db
}

////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////

var tmpl1 = template.Must(template.ParseGlob("report/*"))

func Index(w http.ResponseWriter, r *http.Request) {
     tmpl1.ExecuteTemplate(w, "Index", nil)
}

////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////


//////////////////////////////////////////////////////////////////////////////////////////////
var tmpl2 = template.Must(template.ParseGlob("report/*"))

func IndexReport(w http.ResponseWriter, r *http.Request) {
     tmpl2.ExecuteTemplate(w, "IndexReport", nil)

}

////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////

type Month struct {
    Mname string
    Color string
    Id  int
}


type Year struct {
    Yname, Color string
    Selected bool
}

type Data struct {
        Years   []*Year
        Months  []*Month
}


var tmpl3 = template.Must(template.ParseGlob("report/*"))

func ShowMonth(w http.ResponseWriter, r *http.Request) {
    db := dbConn()
///////////////////////////////////////////////////////////////////////////
    if r.Method == "POST" {
       years := r.FormValue("years")
 
    selDB, err := db.Query("SELECT MONTHNAME(Rdate) as months, color, report_monthly_id as id FROM report_monthly WHERE YEAR(Rdate)=?", years )
    if err != nil {
        panic(err.Error())
    }
    resm := []Month{}
    for selDB.Next() {
        var months, color string
        var id int
        err = selDB.Scan(&months, &color, &id)
        if err != nil {
            panic(err.Error())
        }
        
        rep := Month{Mname: months, Color: color, Id: id}
        resm = append(resm, rep)


    }    
///////////////////////////////////////////////////////////////////////////
    

    selDB1, err := db.Query("SELECT DISTINCT YEAR(Rdate) as years, color FROM report_monthly  ORDER BY report_monthly_id DESC")
    if err != nil {
        panic(err.Error())
    }
    res := []Year{}
    for selDB1.Next() {
        var years, color string
        err = selDB1.Scan(&years, &color)
        if err != nil {
            panic(err.Error())
        }

        rep := Year{Yname: years, Color: color}
        res = append(res, rep)
    }
  
    // person := Data{Years:  []*Year{&res},
    //                Months: []*Month{&resm}}

    log.Println(resm)
    log.Println(res)
    tmpl3.ExecuteTemplate(w, "IndexReport", resm)
    defer db.Close()
}
}
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////

type Report struct {
    Report_monthly_id  int
    Rdate_numeration int
    Save  bool
    Submit bool
    Approve bool    
    Rdate string
    Client_id int
    Color string
}

var tmpl4 = template.Must(template.ParseGlob("report/*"))

func LoadReport(w http.ResponseWriter, r *http.Request) {
    db := dbConn()
    nId := r.URL.Query().Get("id")
    selDB, err := db.Query("SELECT color, report_monthly_id, save, submit, approve, rdate, client_id, rdate_numeration FROM report_monthly WHERE report_monthly_id=?", nId)
    if err != nil {
        panic(err.Error())
    }
    rep := Report{}
    res := []Report{}
    for selDB.Next() {
        var report_monthly_id, rdate_numeration, client_id int
        var save, submit, approve bool
        var rdate, color string
        err = selDB.Scan(&color, &report_monthly_id, &save, &submit, &approve, &rdate, &client_id, &rdate_numeration)
        if err != nil {
            panic(err.Error())
        }
        rep.Report_monthly_id = report_monthly_id
        rep.Save = save
        rep.Submit = submit
        rep.Approve = approve
        rep.Rdate = rdate
        rep.Client_id = client_id
        rep.Rdate_numeration = rdate_numeration
        rep.Color = color
        res = append(res, rep)
        }

    log.Println(rep)    
    tmpl4.ExecuteTemplate(w, "Index", res)
    defer db.Close()
}

////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////

func UpdateReport(w http.ResponseWriter, r *http.Request) {
    db := dbConn()
    if r.Method == "POST" {
        color := r.FormValue("color")
        id := r.FormValue("id")
        insForm, err := db.Prepare("UPDATE report_monthly SET color=? WHERE report_monthly_id=?")
        if err != nil {
            panic(err.Error())
        }
        insForm.Exec(color, id)
        log.Println("UPDATE: Color: " + color + " | Id: " + id)
    }
    defer db.Close()
    http.Redirect(w, r, "/", 301)
}

////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////

func main() {
    log.Println("Server started on: http://localhost:8080")

//REPORTS
    http.HandleFunc("/", Index)
    http.HandleFunc("/indexreport", IndexReport)
    http.HandleFunc("/showmonth", ShowMonth)
    http.HandleFunc("/loadreport", LoadReport)
    http.HandleFunc("/updatereport", UpdateReport)

//STATIC FILES
    http.Handle("/static/", http.StripPrefix("/static/", http.FileServer(http.Dir("static"))))

//LISTEN ON-SERVE:
    http.ListenAndServe(":8080", nil)


}

////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
12

Posting even more code wont change anything about my analysis, best thing it can do for you is to confirm my assumption about which lines were reported in the compiler error.

I will not even try to repair your code, for several reasons.

  1. I do not have access to your database
  2. I do not know how your database is set up
  3. Instead of trying to properly minimize your problem into something reproducable you throw more and more code at us

I explained what is wrong, I told you to rethink your types. You do not do it, you just ask for a ready made solution.

If you really want to wait for the latter, have fun, I won’t do it for you.

1 Like
13

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.