[FIXED] How to get values from a variable of type *sql.Rows in the view?

Issue

In posts table, there are attributes like title, content. I can get data of type *sql.Rows and pass them to the view by

 posts, err := db.Query("SELECT id, title FROM posts WHERE id = 1")

and

  err = tpl.ExecuteTemplate(w, "index.gohtml", posts)

but I couldn't display the title value in the view. Here is my code.

index.go

package main

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

func index(w http.ResponseWriter, r *http.Request) {
  db, err := sql.Open("mysql", "root:****@/database")
  if err != nil {
    panic(err.Error())
  }
  defer db.Close()

  posts, err := db.Query("SELECT id, title FROM posts WHERE id = 1")
  var id int
  var title string
  for posts.Next() {
    err = posts.Scan(&id, &title)
    if err != nil {
      panic(err.Error())
    }
  }

  fmt.Println(posts)

  defer posts.Close()

  err = tpl.ExecuteTemplate(w, "index.gohtml", posts)
  if err != nil {
    log.Println(err)
  }
}

index.gohtml

  <h1>Awesome Posts</h1>

  {{$posts := .}}
  <p>{{$posts}}</p>

Solution

There are a few mistakes in your code and I think you have misunderstood how to extract data using the sql package.

As Flimzy said in the comments, you should pass a proper context struct which contains your ID and Title values somewhere.

If you check the docs for sql.Rows you will see how to extract the data at each of the rows from your query...and in fact you already know how to get the rows and column values - by using the Next() and Scan() methods. But this should not be done by code in the HTML template, it should store the results in some variable which is passed to the template.

Quick Answer

A quick answer to your question would be to change how you pass the values into the template and amend the template. Seeing as you declare id and title variables you should pass them to the template:

err = tpl.ExecuteTemplate(w, "index.gohtml", map[string]interface{}{"ID": id,"Title": title})
if err != nil {
  log.Println(err)
}


<h1>Awesome Posts</h1>
<p>{{.ID}} - {{.Title}}</p>

Having a Post model

A better solution would be to have a struct which holds all of the properties of the post, and use this to Scan into.

type Post struct{
  ID int
  Title string
}

...
var p Post
...
_ = rows.Scan(&p)

However, there is another problem with the way you are storing the results of the query. You are using db.Query to return a single row - This is an assumption as you have WHERE ID=1. If you expect only one post to be returned then use the QueryRow method: (N.B. you can chain on the Scan method for simplicity)

var p Post
// error handling removed for brevity
_ = db.QueryRow("SELECT id, title FROM posts WHERE id = 1").Scan(&p)
_ = tpl.ExecuteTemplate(w, "index.gohtml", p)

If, however, you are expecting to retrieve multiple posts (and you were just adding the where clause for simplicity), then you need to Scan into a Post struct, and append into a slice of Posts.

rows, _ := db.Query("SELECT id, title FROM posts")
defer rows.Close()
var posts []Post
for rows.Next() {
  var p Post
  _ = posts.Scan(&id, &p) // you should handle error here.
  posts = append(posts, p)
}
if err = tpl.ExecuteTemplate(w, "index.gohtml", posts); err!=nil{
  log.Println(err)
}

Other considerations

You should not be creating a connection to the DB within the HTTP handler. One way is to have a global variable which holds the connection. A struct with embedded connection can work and/or it is possible to abstract away the connection into a package as well.

/db/db.go

package db

import (
    "database/sql"
    // MYSQL driver
    _ "github.com/go-sql-driver/mysql"
)

var db *sql.DB

// Open handles the opening of the DB
func Open(connstr string) (err error) {
    db, err = sql.Open("mysql", connstr)
        if err != nil {
            return err
        }
    return nil
}

// Close handles the closing of the DB
func Close() error {
    return db.Close()
}

/db/posts.go

package db

// Post model
type Post struct {
    ID    uint
    Title string
    Body  string
}

const (
    getPosts = `SELECT id, title, body FROM posts`
    getAPost = `SELECT id, title, body FROM posts WHERE id=?`
)

// GetPosts will return all posts from the DB
func GetPosts() ([]Post, error) {
    rows, err := db.Query(getPosts)
    if err != nil {
        return nil, err
    }
    var posts []Post
    for rows.Next() {
        var p Post
        if err := rows.Scan(&p.ID, &p.Title, &p.Body); err != nil {
            return nil, err
        }
        posts = append(posts, p)
    }

    return posts, nil
}

// GetPost will return single post identified by ID from the DB
func GetPost(id uint) (Post, error) {
    var p Post
    if err := db.QueryRow(getAPost, id).Scan(&p.ID, &p.Title, &p.Body); err != nil {
        return p, err
    }
    return p, nil
}

main.go

import (
    "chilledoj/sopost/db" // this is the absolute path to the db folder 
    "html/template"
    "log"
    "net/http"
    "strconv"
    "flag"

    "github.com/gorilla/mux"
)

var dbconn string

func init() {
    flag.StringVar(&dbconn, "dbconn", "", "MYSQL DB Connection string")
    flag.Parse()
}

func main() {

    if dbconn == "" {
        log.Fatal("DB Connection string not set")
    }

    if err := db.Open(dbconn); err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    r := mux.NewRouter()    

    r.HandleFunc("/", indexHandler())
    r.HandleFunc("/posts", postsHandler())
    r.HandleFunc("/posts/{id}", postHandler())

    if err := http.ListenAndServe(":8080", r); err != nil {
        log.Panic(err)
    }
}

var indexHandler = func() http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        if r.URL.Path != "/" {
            http.NotFound(w, r)
            return
        }
        w.WriteHeader(http.StatusOK)
        w.Write([]byte(`<h1>Welcome</h1><a href="/posts">Posts</a>`))
    }
}

var postsHandler = func() http.HandlerFunc {
    tmpl, err := template.New("posts").Parse(`<h1>Awesome Posts</h1>
  <ul>{{range .}}
  <li><a href="/posts/{{.ID}}">{{.Title}}</a></li>
  {{end}}</ul>
  <hr/>
  <a href="/">Home</a>`)
    if err != nil {
        log.Panic(err)
    }
    return func(w http.ResponseWriter, r *http.Request) {
        posts, err := db.GetPosts()
        if err != nil {
            w.WriteHeader(http.StatusInternalServerError)
            w.Write([]byte(err.Error()))
            return
        }
        err = tmpl.Execute(w, posts)
        if err != nil {
            log.Printf("There was a template Error.\n%v\n", err)
        }
    }
}

var postHandler = func() http.HandlerFunc {
    tmpl, err := template.New("posts").Parse(`<h1>Awesome Posts</h1>
  <h2>{{.Title}}</h2>
  <p>{{.Body}}</p>
  <hr/>
  <a href="/">Home</a>
  <a href="/posts">Posts</a>`)
    if err != nil {
        log.Panic(err)
    }
    return func(w http.ResponseWriter, r *http.Request) {
        vars := mux.Vars(r)
        id, err := strconv.ParseInt(vars["id"], 10, 32)
        if err != nil {
            http.NotFound(w, r)
            return
        }
        post, err := db.GetPost(uint(id))
        if err != nil {
            w.WriteHeader(http.StatusInternalServerError)
            w.Write([]byte(err.Error()))
            return
        }
        tmpl.Execute(w, post)
    }
}

Run the above using

go run main.go -dbconn [dbuser]:[dbpass]@/[dbname]?parseTime=true

Another way is to use dependency injection and have a function take in a db connection but return an http.HandlerFunc. e.g.

var indexHandler = function (db *sql.DB) http.HandlerFunc{
  return function(w http.ResponseWriter, r *http.Request){
    // now have access to db
  }
}

http.HandleFunc("/posts", indexHandler())

Answered By - Oliver Jackman

Answer Checked By - Robin (PHPFixing Admin)