Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Fetching rows in a MySQL database table using MySQL C API and C++

Tags:

c++

c

mysql

api

I'm confused when trying to fetch table rows in mysql using C++ with MySQL C API.

I can do it easily in PHP, just because C++ is a strongly-typed language so that we also need to take care of the dirty process..

This is how I done it in PHP

$data = array();
$i = 0;
$query = mysql_query("SELECT * FROM `my_table`");
while($fetch = mysql_fetch_array($query))
{
  $data[$i] = $fetch['columntobefetched'];
  $i++;
}

But how to do the same in C++ with MySQL API?

Here's my code so far....with a confusing dead end...x__x

   MYSQL *sqlhnd = mysql_init(NULL);
    mysql_real_connect(sqlhnd, "server", "user", "pass", "database", port, NULL, 0);

    mysql_query(sqlhnd, "SELECT * FROM `my_table`");
    MYSQL_RES *confres = mysql_store_result(sqlhnd);
    int totalrows = mysql_num_rows(confres);
    int numfields = mysql_num_fields(confres);
    MYSQL_FIELD *mfield;

    while((row = mysql_fetch_row(confres)))
    {
        for(i = 0; i < numfields; i++)
        {
            while(mfield = mysql_fetch_field(confres))
            {
                mfield->//??? I'm dead
            }
        }
    }

Basically I wanted to get a value from a field in the database table and store it to a variable..

Any kind of help would be appreciated :)

Thanks

like image 430
Yana D. Nugraha Avatar asked Jul 28 '09 14:07

Yana D. Nugraha


Video Answer


2 Answers

In the MySQL C API, mysql_fetch_row returns a MYSQL_ROW object, which is essentially an array of values in the current row.

So, your code should be something like:

mysql_query(sqlhnd, "SELECT * FROM `my_table`");
MYSQL_RES *confres = mysql_store_result(sqlhnd);
int totalrows = mysql_num_rows(confres);
int numfields = mysql_num_fields(confres);
MYSQL_FIELD *mfield;

while((row = mysql_fetch_row(confres)))
{
    for(i = 0; i < numfields; i++)
    {
        char *val = row[i];
        // do something with val...
    }
}

Better yet, don't do a "SELECT * FROM mytable" in a program. It would be much better to name the fields you expect, so that you can be sure of the order of the fields returned.

like image 75
Avi Avatar answered Sep 22 '22 16:09

Avi


If you are using C++ why not using MySQL++? This is some sample code:

mysqlpp::Connection dbconnection;
dbconnection.connect("database", "server", "user", "pass");

mysqlpp::Query prepared_query = dbconnection.query( "SELECT * FROM `my_table`" );

mysqlpp::StoreQueryResult r = prepared_query.store();

int field = r.field_num("columntobefetched");

for(mysqlpp::StoreQueryResult::iterator i = r.begin(); i!=r.end();i++)
{
      std::cout << i->at(field) << std::endl;
      std::cout << (*i)["columntobefetched"] << std::endl; // this will be slower
}
like image 38
Arpegius Avatar answered Sep 23 '22 16:09

Arpegius