SQL / C++ Tutorial using sqlwrapped

8. Reading data from multiple tables (JOIN)
PreviousNextIndex

First of all, the other two tables will have to be populated with some
information. The resource table will contain descriptions of stuff a player
can own, and the playerresource table will show how much of a resource a
certain player owns.

Execute the follow sql statements to add some data into the tables. Player#1
and Player#2 has already been added by the above examples.

insert into resource(name) values('Gold');
insert into resource(name) values('Silver');
insert into resource(name) values('Coal');
insert into playerresource values(1,1,1000);
insert into playerresource values(1,2,400);
insert into playerresource values(1,3,4000);
insert into playerresource values(2,1,1500);
insert into playerresource values(2,2,800);
insert into playerresource values(2,3,5);
populate.sql

This will give player#1 1000 gold, 400 silver, and 4000 pieces of coal. Player#2
will get 1500 gold, 800 silver, and only 5 pieces of coal.

example

int main()
{
	StderrLog log;
	Database db("localhost", "root", "", "tutorialdb", &log); // MySQL specific
	if (!db.Connected())
	{
		printf("Database not connected - exiting\n");
		exit(-1);
	}
	Query q(db);
	q.get_result(
		"select player.name,resource.name,x.amount from playerresource as x inner join player on "
		"x.player=player.num inner join resource on x.resource=resource.num");
	while (q.fetch_row())
	{
		std::string player_name = q.getstr();
		std::string resource_name = q.getstr();
		long amount = q.getval();
		printf("%s owns %ld piece%s of %s.\n",
			player_name.c_str(),
			amount,
			(amount == 1) ? "" : "s",
			resource_name.c_str());
	}
	q.free_result();
}
example6_mysql.cppexample6_sqlite3.cpp


PreviousNext