You need only one SELECT statement, a join:
SELECT s.fname, s.sname, s.studnum, s.subject, s.result, s.code, r.dbox
FROM student s, result_meaning r
WHERE s.code = r.code
AND s.studnum = <value>
The results returned by mysql_use_result() and mysql_fetch_row() will then be the
values of the columns you need, in the order in which they appear in the SELECT.
The out-of-sequence errror is caused by the fact that you are attempting to execute a
SELECT before all the results of a previous SELECT have been fetched from the server.
This will not be an issue when you change your code to utilize just one SELECT.
--Greg Johnson
-----Original Message-----
From: Evan James Dembskey [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 02, 2001 5:09 AM
To: Mysql
Subject: Extracting data from 2 tables.
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 <stdlib.h>
#include <stdio.h>
#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 <student> 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 <result_meaning> 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 <result_meaning> 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("Connection error %d: %s\n",
mysql_errno(&my_connection),
mysql_error(&my_connection));
}
}
/* end open if else */
return EXIT_SUCCESS;
} /* main */
void display_row()
{
printf("Names: %s\n", sqlrow[0]);
printf("Surname: %s\n", sqlrow[1]);
printf("Student Number: %s\n", sqlrow[2]);
printf("Subject: %s\n", sqlrow[3]);
printf("Result: %s\n", sqlrow[4]);
printf("Result Dbox: %s\n", sqlrow_code[2]);
printf("Code: %s\n", sqlrow[5]);
}
Regards,
Evan
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
