Hi,
I have two tables in a db called "univ". The tables are "student" and
"result_meaning".
Using the C API I want to run a query that SELECT's from "student" using the
field "studnum" as
the selection criteria.
In the "student" table their is a field called "code".
I want to then look up the equivelant "code" in the "result_meaning" table,
so that I can print the field
"dbox" with the results of the SELECT on "student".
ie. Using the data in the tables below, and a student number of "31165222" I
would want to extract the following
fields:
fname
sname
studnum
subject
result
code
dbox(from "result_meaning").
I have looked around for help, and have put together the code at the end of
this email, which does not work. I get a
"out of sequence" error.
Where am I going wrong? Is there a simple way to SELECT data from 2
different tables using C? Help, I'm pretty
stuffed here!
Tables
--
--
student
---
+---+--+--+-++--+
| fname | sname| studnum | subject | result | code |
+---+--+--+-++--+
| Evan | Domski | 31165222 | ENG301 | 80 | P|
| Evan | James| 31165223 | AHS301 | 75 | P|
| Frank | Hardy| 31155111 | CRM301 | 80 | P|
| Joe | Hardy| 31155112 | FOR301 | 80 | P|
| Joe | Hardy| 31155112 | CRM301 | 80 | P|
| Frank | Hardy| 31155111 | FOR301 | 85 | PD |
+---+--+--+-++--+
result_meaning
--
+--+--+--+
| code | meaning | dbox |
+--+--+--+
| P| Pass | 1000 |
| F| Fail | 1001 |
| PD | Pass with Distinctio | 1002 |
+--+--+--+
selecttest.c
#include
#include
#include "mysql.h"
MYSQL my_connection;
MYSQL_RES *res_ptr;
MYSQL_RES *res_ptr_code;
MYSQL_ROW sqlrow;
MYSQL_ROW sqlrow_code;
void display_row();
int main(int argc, char *argv[]) {
int res;
int res_code;
int stud_num;
char query[96]="SELECT * FROM student WHERE studnum=";
char query_code[96]="SELECT * FROM result_meaning WHERE code=";
/* Getting some test data! */
printf("Enter your student number: ");
scanf("%i", &stud_num);
/*-*/
mysql_init(&my_connection);
/**/
/* "host", "user", "password", "database" */
/**/
if (mysql_real_connect(&my_connection, "localhost", "root", "", "univ", 0,
NULL, 0))
{
printf("Connection success\n");
/* -*/
/* Table access */
sprintf(&query[36], "%d", stud_num);
res = mysql_query(&my_connection, query);
if (res)
{
printf("SELECT error: %s\n", mysql_error(&my_connection));
}
else
{
res_ptr = mysql_use_result(&my_connection);
if (res_ptr)
{
while ((sqlrow = mysql_fetch_row(res_ptr)))
{
/*
-*/
/* Table access
*/
sprintf(&query_code[36], "%d", sqlrow[5]);
res_code = mysql_query(&my_connection, query_code);
if (res_code)
{
printf("SELECT error: %s\n", mysql_error(&my_connection));
}
else
{
res_ptr_code = mysql_use_result(&my_connection);
if (res_ptr_code)
{
while ((sqlrow_code = mysql_fetch_row(res_ptr_code)))
{
printf("Fetched data from
result_meaning...\n");
}
if (mysql_errno(&my_connection))
{
printf("Retrive error: %s\n",
mysql_error(&my_connection));
}
}
mysql_free_result(res_ptr_code);
}
/* End table access
*/
/*
-*/
display_row();
}
if (mysql_errno(&my_connection))
{
printf("Retrieve error: %s\n", mysql_error(&my_connection));
}
}
mysql_free_result(res_ptr);
}
/* open if else */
}
else
{
printf("Connection failed\n");
if (mysql_errno(&my_connection))
{
printf("Conne
