Let Join C++ MYSQL library

Question

I'm trying to access "category" table with LEFT JOIN. I need to retrieve the field "name" in this table.

This is my code:

void Product::read(MYSQL *connection)
{
MYSQL_RES *result;
MYSQL_ROW row;

if(mysql_query(connection, "SELECT * FROM product LEFT JOIN category ON product.category=category.category_id"))
    std::cout<<"Query failed!!"<<mysql_error(connection)<<std::endl;
else
    result=mysql_store_result(connection);

if(result->row_count>0)
{
    while(row=mysql_fetch_row(result))
    {

        std::cout<<"Name: "<<row[1]<<" Brand: "<<row[3]<<" Price: "<<row[4]<<" Category: "<<row[5]<<" Amount: "<<row[6]<<std::endl;
    }
}
mysql_free_result(result);
}

And this is the result of the query:

Name: Oneplus Nord 2 5G Brand: Oneplus Price: 299.99 Category: 1 Amount: 3
Name: Acer Swift 3 Brand: Acer Price: 899.99 Category: 2 Amount: 5
Name: Bose SoundLink Revolve Brand: Bose Price: 100.23 Category: 1 Amount: 3

How can I show the name of the category?

Answer 1

You can be more specific about what columns are you selecting and their order. Rather than * you can specify the table_name.column_name (or just column_name if you have no overlaps, or alias_name.column_name if you want to use aliases), so you could try something like:

SELECT product.name, product.brand, product.price, category.name, product.amount FROM product LEFT JOIN category ON product.category=category.category_id

I am assuming how columns are named.

This way the following code will be more predictable, because indices would be based on what you've written in select.

std::cout<<"Name: "<<row[1]<<" Brand: "<<row[3]<<" Price: "<<row[4]<<" Category: "<<row[5]<<" Amount: "<<row[6]<<std::endl;