MySQL Tutorial: Selecting Single and Multiple Rows

16 October 2021

MySQL Series Index

Connecting to MySQL and creating a Database
Creating a Table and Inserting Rows
Selecting Single Row and Multiple rows
Prepared statements - WIP
Updating rows - WIP
Deleting rows - WIP

Welcome to tutorial no. 3 in our MySQL tutorial series. In the previous tutorial, we discussed creating a table and inserting rows into the table. In this tutorial, we will learn how to select a single row as well as multiple rows from a table.

Selecting a single row

The product table we created in the previous tutorial has three rows. Running select * from product; on the table returns the following rows.

In this section of the tutorial, we will write code to select a single row from this table. We will write a function that will return the product price when the product name is given as the input. The query to do this is provided below.

select product_price from product where product_name = "iphone";  

The above query will return one row and will return the product_price column from that row. The above query will return 950

Now that we have the query ready, let's go ahead and code it.

The first step is to create a prepared statement. Prepared statements are used to parametrize a SQL query so that the same query can be run with different arguments efficiently. It also prevents sql injection.

The template for the prepared statement is provided below.

select product_price from product where product_name = ?  

We have replaced iphone with ? to create the prepared statement template.

The next step is to create the prepared statement. This is done using the PrepareContext method.

func selectPrice(db *sql.DB, productName string) (int, error) {  
    query := `select product_price from product where product_name = ?`
    ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancelfunc()
    stmt, err := db.PrepareContext(ctx, query)
    if err != nil {
        log.Printf("Error %s when preparing SQL statement", err)
        return 0, err
    }
    defer stmt.Close()
    ...
}

We pass a context with a 5 second timeout to the PrepareContext method in line no. 5. This method returns a prepared statement. We defer closing the statement in line no. 10.

The next step is to execute this prepared statement. This is done using the QueryRowContext method.

var price int  
row := stmt.QueryRowContext(ctx, productName)  
if err := row.Scan(&price); err != nil {  
    return 0, err
}

The QueryRowContext method is called on the prepared statement stmt. This method takes a context and a variadic list of arguments as parameter. In our case, we have only one argument in the query which is the product name and we pass this as the argument to this method.

The QueryRowContext returns a single row. We then need to call Scan on the row to copy the columns returned by the query to the pointer passed to the Scan method. Here we pass an integer pointer in line no. 3 of the above code snippet to which the price will be stored. If the query returns more than one row, the Scan method will return only the first row and there will be no error. If there are no rows returned, Scan will return an error. We will discuss error handling in the next section.

The complete function is provided below.

func selectPrice(db *sql.DB, productName string) (int, error) {  
    log.Printf("Getting product price")
    query := `select product_price from product where product_name = ?`
    ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancelfunc()
    stmt, err := db.PrepareContext(ctx, query)
    if err != nil {
        log.Printf("Error %s when preparing SQL statement", err)
        return 0, err
    }
    defer stmt.Close()
    var price int
    row := stmt.QueryRowContext(ctx, productName)
    if err := row.Scan(&price); err != nil {
        return 0, err
    }
    return price, nil
}

Error handling

The Scan method used in line no. 14 of the above function will return error ErrNoRows when no rows are returned by the query. Let's call our selectPrice function from main where will handle errors.

func main() {  
...
    productName := "iphone"
    price, err := selectPrice(db, productName)
    switch {
    case err == sql.ErrNoRows:
        log.Printf("Product %s not found in DB", productName)
    case err != nil:
        log.Printf("Encountered err %s when fetching price from DB", err)
    default:
        log.Printf("Price of %s is %d", productName, price)
    }
}

We pass product name as iphone to selectPrice and then switch case on the error. If the error is of type ErrNoRows we print a message saying that the product is not found. In case of any other error, we print the error message. And in the default case we print the result.

This program will print

Price of iphone is 950  

Selecting multiple rows

In this section, we will learn how to select multiple rows from a table. We will write a function that will take minimum price and maximum price as parameters and return the name and price of the products that match the query.

The query that does that is provided below.

select product_name, product_price from product where product_price >= 900 && product_price <= 1000;  

The above query will return the name and price of all products whose prices fall between 900 and 1000.

Most of the steps for selecting multiple rows are similar to selecting a single row which we discussed in the previous section. I have provided the complete function to select multiple rows below.

func selectProductsByPrice(db *sql.DB, minPrice int, maxPrice int) ([]product, error) {  
    log.Printf("Getting products by price")
    query := `select product_name, product_price from product where product_price >= ? && product_price <= ?;`
    ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancelfunc()
    stmt, err := db.PrepareContext(ctx, query)
    if err != nil {
        log.Printf("Error %s when preparing SQL statement", err)
        return []product{}, err
    }
    defer stmt.Close()
    rows, err := stmt.QueryContext(ctx, minPrice, maxPrice)
    if err != nil {
        return []product{}, err
    }
    defer rows.Close() 
    var products = []product{}
    for rows.Next() {
        var prd product
        if err := rows.Scan(&prd.name, &prd.price); err != nil {
            return []product{}, err
        }
        products = append(products, prd)
    }
    if err := rows.Err(); err != nil {
        return []product{}, err
    }
    return products, nil
}

Similar to the previous section, we first created a prepared statement in line no. 6 of the above function. After that we call QueryContext method on the prepared statement stmt in line no. 12. This method will return the list of rows selected by the query. We defer closing the rows in line no. 16.

After getting the row list, we call Next method on rows in line no. 18. This prepares the result for reading using the Scan method. If there is any error when calling Next(), it will return false and the for loop will terminate. In case of error, Err() method on rows will return a non nil value and we need to call that to find out if there was any error when Next() was called. This is done in line no. 25.

If there is no error when fetching the rows, rows is closed automatically by the Next() method call in line no. 18. If there is any error, it is necessary to call Close explicitly. Since the call to Close() is idempotent, it's ok to call it twice i.e once during the successfully Next() call and again during defer in line no. 16. Hence we have deferred the rows close in line no. 16.

In line no. 19 we create prd of type product to store the result.

In case you don't remember, product struct has the following fields.

type product struct {  
    name  string
    price int
}

In line no. 20, the result columns are copied to the name and price fields of the prd struct and in line no. 23, prd is appended to the products slice.

products is returned in line no. 28.

The next step is to call this function from main.

func main() {  
...
    minPrice := 900
    maxPrice := 1000
    products, err := selectProductsByPrice(db, minPrice, maxPrice)
    if err != nil {
        log.Printf("Error %s when selecting product by price", err)
        return
    }
    for _, product := range products {
        log.Printf("Name: %s Price: %d", product.name, product.price)
    }

We pass 900 and 1000 as the minimum and maximum price to the selectProductsByPrice function in line no. 5.

If there is any error, we return after printing the error.

If there is no error, the name and price of the products matching the query are printed in line no. 11.

This program will print,

2021/10/16 21:10:52 Getting products by price  
2021/10/16 21:10:52 Name: iphone Price: 950  
2021/10/16 21:10:52 Name: Galaxy Price: 990  

This brings us to the end of this tutorial. Please share your valuable feedback in the comments.

The entire code is available at https://github.com/golangbot/mysqltutorial/blob/master/select/main.go

If you would like to advertise on this website, hire me, or if you have any other development requirements, please email to naveen[at]golangbot[dot]com.

Like my tutorials? Please show your support by donating. Your donations will help me create more awesome tutorials.

Previous tutorial - Creating a Table and Inserting Rows

Naveen Ramanathan

Naveen Ramanathan is a software engineer with interests in Go, Docker, Kubernetes, Swift, Python, and Web Assembly. If you would like to hire him, please mail to naveen[at]golangbot[dot]com.

Share this post