How to Fetch Particular row value of field

ts7300 · 10-05-2009, 02:24 AM

Below is MYSQL using C code and hotter_alert is field and I want to fetch particular row [4]=2 value of hotter_alert.If I am printing row[0] its printing all the row value of field hotter_alert.
mysql> select hotter_alert from location;
+--------------+
| hotter_alert |
+--------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 2 |
+--------------+
5 rows in set (0.00 sec)

Code:

#include <stdio.h>
#include <stdlib.h>
#include "mysql.h"


MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;

void exiterr(int exitcode)
{
    fprintf( stderr, "%s\n", mysql_error(conn) );
    exit( exitcode );
}

int main()
{
    uint i = 0;
    char * server = "10.64.21.63";
   char *user = "root";
   char * password = "passwd";
   //char *password = "rrr777"; /*set me first */
   char *database = "location";

   conn = mysql_init(NULL);
   /* Connect to database */
   if (!mysql_real_connect(conn, server,
         user, password, database, 0, 0, 0))
        exiterr(1);

    if (mysql_select_db(conn,"location"))
        exiterr(2);

    if (mysql_query(conn,"SELECT hotter_alert from location"))
        exiterr(3);

    if (!(res = mysql_store_result(conn)))
        exiterr(4);

    while((row = mysql_fetch_row(res))) {
       // for (i=0 ; i < mysql_num_fields(res); i++) 
{

             printf("%s \n", row[0]);
    }
}

    if (!mysql_eof(res))
        exiterr(5);

    mysql_free_result(res);
    mysql_close(conn);
}

output of above program even though I am printing row[0].Quite strange
root@yatin-desktop:/home/yatin/Desktop# ./final
0
0
0
0
2

kbp · 10-05-2009, 08:42 PM

Hi,

I don't use sql queries very often these days but you usually use a 'where' clause to refine the returned result, not based on the row number

eg.

Code:

select hotter_alert from location where location.address='123 Elm St'

+--------------+----------------+
| hotter_alert | address        |
+--------------+----------------+
| 0            | 666 Diablo Rs  |  
| 0            | 777 Neighbor T |
| 0            | 22 Acacia Ave  |
| 0            | 45 Main St     |
| 2            | 123 Elm St     |
+--------------+----------------+

Ref:
http://www.w3schools.com/sql/sql_where.asp

If you're really stuck on returning row 4 then change your code to something like:

Code:

for (i=0 ; i < mysql_num_fields(res); i++) 
{
     if (i=4) {
             printf("%s \n", row[i]);
     }
}

cheers,

kbp

ts7300 · 10-06-2009, 12:51 AM

Below is the MYSQL C api code and used while infinite loop to fetch the data from database continuously.But .exe file runs only for few minute after printing the value on terminal its print killed.Can somebody help me if am using while infinite loop since its failed to print infinite row value.
-------------------------
--
--
--
1
1
1
1
1
1
Killed
--------------------------

Code:

#include <stdio.h>
#include <stdlib.h>
#include "mysql.h"


MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;

void exiterr(int exitcode)
{
    fprintf( stderr, "%s\n", mysql_error(conn) );
    exit( exitcode );
}

int main()
{
    uint i = 0;
    char * server = "10.64.21.63";
   char *user = "root";
   char * password = "passwd";
   //char *password = "rrr777"; /*set me first */
   char *database = "location";

   conn = mysql_init(NULL);
   /* Connect to database */
   if (!mysql_real_connect(conn, server,
         user, password, database, 0, 0, 0))
        exiterr(1);

    if (mysql_select_db(conn,"location"))
        exiterr(2);
while (1)
{
    if (mysql_query(conn,"SELECT hotter_alert from location"))
        exiterr(3);

    if (!(res = mysql_store_result(conn)))
        exiterr(4);

    while((row = mysql_fetch_row(res))) {
       // for (i=0 ; i < mysql_num_fields(res); i++) 
{

             printf("%s \n", row[0]);
    }
}
}
    if (!mysql_eof(res))
        exiterr(5);

    mysql_free_result(res);
    mysql_close(conn);
}

ts7300 · 10-08-2009, 07:32 AM

Now I edited code .
view plainprint?

Code:

   1. while (1)  
   2. {  
   3.    /* send SQL query */  
   4.    if (mysql_query(conn, "select *from location where id=5")) {  
   5.       fprintf(stderr, "%s\n", mysql_error(conn));  
   6.       exit(1);  
   7.    }  
   8.    
   9.    res = mysql_use_result(conn);  
  10.    
  11.    /* output table name */  
  12.     while ((row = mysql_fetch_row(res)) != NULL)  
  13.    printf("%s \n", row[18]);

and when while loop is used again it ends with out of memory error.Please suggest me why such error is coming.
-------
5
5
5
5
5
5
5
5
5
5
5
5
Out of memory (Needed 8164 bytes)

ts7300 · 10-09-2009, 05:53 AM

Hi,
I compiled the code MYSQL C API along with C code and run ./a.out file.And I want this ./a.out to keep running continuous so that when I enter new value of particular field at myadmin php its value should be updated at MYSQL Client.So,is their any way I run ./a.out file continuous.