Need help pulling information from MySQL

**Myles8908** · 12-05-2014

Alright I'm doing a basic inventory program. I have 5 options and its to view different video games (very simple).

I'm new to C coding, but everytime I try to run look up an item by UPC , after I type in the UPC it gives me a segementation fault and I can't find where the fault is coming from. Here is my setup for the MySQL database

DATABASE:

Code:

CREATE TABLE BARCODE_SCAN  
( 
    BARCODE_ID VARCHAR(6) NOT NULL, 
    GAME_TITLE VARCHAR(20) NOT NULL, 
    DEVELOPER VARCHAR(20) NOT NULL, 
    PRICE DECIMAL(4,2) NOT NULL, 
    STOCK_VALUE INT(20) NOT NULL, 
    PRIMARY KEY (BARCODE_ID) 
); 
 
INSERT INTO BARCODE_SCAN 
VALUES ('123456', 'Call of Duty', 'Activision', '59.99', '12'); 
INSERT INTO BARCODE_SCAN 
VALUES ('234567', 'Halo 5', 'Bungie', '59.99', '15'); 
INSERT INTO BARCODE_SCAN 
VALUES ('345678', 'World of Warcraft', 'Blizzard', '15.99', '5'); 
INSERT INTO BARCODE_SCAN 
VALUES ('456789', 'Mine Craft', 'Mojang', '19.99', '15'); 
INSERT INTO BARCODE_SCAN 
VALUES ('987654', 'Final Fantasy VIII', 'Square Soft', '19.99', '10'); 
INSERT INTO BARCODE_SCAN 
VALUES ('876543', 'GTA 5', 'Rockstar', '59.99', '1'); 
INSERT INTO BARCODE_SCAN 
VALUES ('765432', 'Diablo 3', 'Blizzard', '59.99', '15'); 
INSERT INTO BARCODE_SCAN 
VALUES ('654321', 'Dead Island', 'Tech land', '49.99', '69'); 
INSERT INTO BARCODE_SCAN 
VALUES ('123123', 'Resident Evil', 'Capcom', '39.99', '15'); 
INSERT INTO BARCODE_SCAN 
VALUES ('234234', 'Zelda', 'Nintendo', '49.99', '15');

Here is my code. Option 1 works fine, its option 2 that doesn't and I haven't even attempted option 3 or 4 (adding and taking away quantity amounts).
************************************************** *************************************

Code:

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


int main() 
{
    MYSQL *conn;
    MYSQL_ROW row;
    MYSQL_FIELD *field;
     MYSQL_RES *result;
    int input_scan;
    char *server = "localhost";
    char *user = "root";
    char *password = "1";     /* set me first */
    char *database = "PROJECT";
    char query[100];
    int i;
    int choice;


    do
    {
        system("clear");
        printf("Welcome to Jason Chea & Myles Hattabaugh Project\n\n");

        
        printf("Please make a choice:\n"
        "1) View Complete Inventory \n"
        "2) Item Description \n"
        "3) Add Item \n"
        "4) Remove Item \n"
        "5) Exit \n");
        scanf("%d", &choice);

        if (choice == 1)
        {
            conn = mysql_init(NULL);

            /* Connect to database */

            if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) 
            {
                fprintf(stderr, "%s\n", mysql_error(conn));
                exit(1);
            }

            /* send SQL query */
            if (mysql_query(conn, "SELECT GAME_TITLE FROM BARCODE_SCAN"))
            {
                fprintf(stderr, "%s\n", mysql_error(conn));
                exit(1);
            }
    
            result = mysql_use_result(conn);

            /* output table name */
            printf("Inventory:\n\n");

            while ((row = mysql_fetch_row(result)) != NULL)
            {
                printf("%s \n", row[0]);
            } 

            if(mysql_query(conn, "SELECT * FROM BARCODE_SCAN"))
            {
                mysql_error(conn);
            }
            result = mysql_store_result(conn);
            
            printf("\n\n");
    
              if (result == NULL) 
              {
                      mysql_error(conn);
              }
            exit(0);
        }

        else if (choice == 2)
        {
            printf("Enter or Scan a UPC Code:");
            scanf("%i", &input_scan);
        
            sprintf(query, "SELECT * FROM BARCODE_SCAN WHERE BARCODE_ID LIKE ('%i')", input_scan);
            mysql_query(conn,query);    
        {

        mysql_error(conn);
    }
    result = mysql_store_result(conn);
    
      if (result == NULL) 
      {
              mysql_error(conn);
      }

      int num_fields = mysql_num_fields(result);
 
      while ((row = mysql_fetch_row(result))) 
      { 
              for(i = 0; i < num_fields; i++) 
              {              
                  printf("%s ", row[i] ? row[i] : "NULL"); 
              } 
              printf("\n"); 
      } 
}

    else if (choice == 3)
    {
        printf("option not available yet");
    }
    else if (choice == 4)
    {
        printf("option not available yet");
    } 
    else 
    {
        printf("Invalid Choice");
    }
}
    while (choice !=5);
    /* close connection */
    mysql_free_result(result);
    mysql_close(conn);

exit(0);
}

Any tips would be greatly appreciated. Thanks!

**Salem** · 12-06-2014

The first step is to learn how to indent code.
Indent style - Wikipedia, the free encyclopedia

Code:

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


int main()
{
  MYSQL *conn;
  MYSQL_ROW row;
  MYSQL_FIELD *field;
  MYSQL_RES *result;
  int input_scan;
  char *server = "localhost";
  char *user = "root";
  char *password = "1";         /* set me first */
  char *database = "PROJECT";
  char query[100];
  int i;
  int choice;


  do {
    system("clear");
    printf("Welcome to Jason Chea & Myles Hattabaugh Project\n\n");
    printf("Please make a choice:\n"
           "1) View Complete Inventory \n"
           "2) Item Description \n"
           "3) Add Item \n" "4) Remove Item \n" "5) Exit \n");
    scanf("%d", &choice);

    if (choice == 1) {
      conn = mysql_init(NULL);

      /* Connect to database */

      if (!mysql_real_connect
          (conn, server, user, password, database, 0, NULL, 0)) {
        fprintf(stderr, "%s\n", mysql_error(conn));
        exit(1);
      }

      /* send SQL query */
      if (mysql_query(conn, "SELECT GAME_TITLE FROM BARCODE_SCAN")) {
        fprintf(stderr, "%s\n", mysql_error(conn));
        exit(1);
      }

      result = mysql_use_result(conn);

      /* output table name */
      printf("Inventory:\n\n");

      while ((row = mysql_fetch_row(result)) != NULL) {
        printf("%s \n", row[0]);
      }

      if (mysql_query(conn, "SELECT * FROM BARCODE_SCAN")) {
        mysql_error(conn);
      }
      result = mysql_store_result(conn);

      printf("\n\n");

      if (result == NULL) {
        mysql_error(conn);
      }
      exit(0);
    }
    else if (choice == 2) {
      printf("Enter or Scan a UPC Code:");
      scanf("%i", &input_scan);

      sprintf(query, "SELECT * FROM BARCODE_SCAN WHERE BARCODE_ID LIKE ('%i')",
              input_scan);
      mysql_query(conn, query);
      {
        mysql_error(conn);
      }
      result = mysql_store_result(conn);

      if (result == NULL) {
        mysql_error(conn);
      }

      int num_fields = mysql_num_fields(result);
      while ((row = mysql_fetch_row(result))) {
        for (i = 0; i < num_fields; i++) {
          printf("%s ", row[i] ? row[i] : "NULL");
        }
        printf("\n");
      }
    }
    else if (choice == 3) {
      printf("option not available yet");
    } else if (choice == 4) {
      printf("option not available yet");
    } else {
      printf("Invalid Choice");
    }
  }
  while (choice != 5);

  /* close connection */
  mysql_free_result(result);
  mysql_close(conn);

  exit(0);
}

Now, explain
1. What is the purpose of the exit call on line 68
2. How conn is initialised and connected to the database on line 76

Before you write choices 3 and 4, read this
A development process

At a minimum, the handler for each choice should be a separate function.
Whether they each manage their own connection internally, or they share a connection established by main, that is something for you to decide.

As a rough rule of thumb, if you can't see the opening and closing brace of a function on screen at the same time, then the function is probably too big.

**Myles8908** · 12-07-2014

Now, explain
1. What is the purpose of the exit call on line 68
2. How conn is initialised and connected to the database on line 76

Before you write choices 3 and 4, read this
A development process

At a minimum, the handler for each choice should be a separate function.
Whether they each manage their own connection internally, or they share a connection established by main, that is something for you to decide.

As a rough rule of thumb, if you can't see the opening and closing brace of a function on screen at the same time, then the function is probably too big.

My professor only like Allman style, which I know mine was still messed up but I will fix it.

1. I had it in a function orignally and apparently forgot to take out the exit call.
2. conn is initialized at the top MySQL conn as a global variable.

I tried to do all these in seperate functions but it messed up my connectivity to MySQL everytime even when calling the connectivity so for purposes of getting everything working first I put it all in main.

Option 1 works just needs a visual update (padding).

Option 2 - Segmentation fault (core dumped). Had it working before and now it gives me this error and I can't figure out how to fix it.

Thanks for the timely reply, any suggestions on fixing this error?

I took out the exit on line 68 as well.

**itCbitC** · 12-07-2014

For option 2, the MySQL database connection calls i.e. mysql_init() and mysql_real_connect() are missing?
Those calls should be present in option 2 also or you could factor both of them out into a common function.

**Myles8908** · 12-07-2014

Originally Posted by itCbitC

For option 2, the MySQL database connection calls i.e. mysql_init() and mysql_real_connect() are missing?
Those calls should be present in option 2 also or you could factor both of them out into a common function.

Wow, how did I miss that ? Too many hours looking at code! lol thank you it works now on option 2! Thanks guys for your help , on to option 3 and 4!

**Myles8908** · 12-09-2014

Code:

#include <mysql.h>

#include <stdio.h>

#include <stdlib.h>

#include <my_global.h>


int main() 

{

	MYSQL *conn;

	MYSQL_ROW row;

	MYSQL_FIELD *field;

 	MYSQL_RES *result;

	int input_scan;

	char *server = "localhost";

	char *user = "root";

	char *password = "1"; 	/* set me first */

	char *database = "PROJECT";

	char query[100];

	int i;

	int choice;

	int system_loop = 1;


	while (system_loop != 0)

	{


		do

		{

			system("clear");

			printf("Welcome to Jason Chea & Myles Hattabaugh Project\n\n");


			printf("Please make a choice:\n"

			"1) View Complete Inventory \n"

			"2) Item Description \n"

			"3) Add Game \n"

			"4) Remove Item \n"

			"5) Exit \n\n");

			printf("Please select a option:");

			scanf("%d", &choice);


			if (choice == 1)

			{

				conn = mysql_init(NULL);

			


				/* Connect to database */


				if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) 

				{

					fprintf(stderr, "%s\n", mysql_error(conn));

					exit(1);

				}


				/* send SQL query */

				if (mysql_query(conn, "SELECT * FROM BARCODE_SCAN"))

				{

					fprintf(stderr, "%s\n", mysql_error(conn));

					exit(1);

				}

	

				result = mysql_use_result(conn);


				/* output table name */

				int num_fields = mysql_num_fields(result);

				printf("\nInventory:\n\n");


				while ((row = mysql_fetch_row(result))) 

	  			{ 

	      				for(i = 0; i < num_fields; i++) 

					{

						if (i == 0)

						{

							while(field = mysql_fetch_field(result)) 

		     					{

		        					printf("%s" "   ||   ", field->name);

		     					}

		  					printf("\n----------------------------------------------------------------------------------\n");

						}              

		  				printf("%-18s", row[i] ? row[i] : "NULL"); 

	      				}

		  			printf("\n"); 

	  			} 


				if(mysql_query(conn, "SELECT * FROM BARCODE_SCAN"))

				{

					mysql_error(conn);

				}

				result = mysql_store_result(conn);

			

				printf("\n\n");

	

	  			if (result == NULL) 

	  			{

	      				mysql_error(conn);

	  			}

		

				exit(0);

			}


			else if (choice == 2)

			{

				conn = mysql_init(NULL);

				if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) 

				{

					fprintf(stderr, "%s\n", mysql_error(conn));

					exit(1);

				}

			

				printf("Enter or Scan a UPC Code:");

				scanf("%i", &input_scan);

		

				sprintf(query, "SELECT * FROM BARCODE_SCAN WHERE BARCODE_ID LIKE ('%i')", input_scan);

				mysql_query(conn,query);

	

				{

					mysql_error(conn);

				}


					result = mysql_store_result(conn);

	

	  			if (result == NULL) 

	  			{

	      				mysql_error(conn);

	  			}


	  			int num_fields = mysql_num_fields(result);

	 

	  			while ((row = mysql_fetch_row(result))) 

	  			{ 

	      				for(i = 0; i < num_fields; i++) 

					{

						if (i == 0)

						{

							while(field = mysql_fetch_field(result)) 

		     					{

		        					printf("%s" "   ||   ", field->name);

		     					}

		  					printf("\n----------------------------------------------------------------------------------\n");

						}              

		  				printf("%-18s", row[i] ? row[i] : "NULL"); 

	      				}

		  			printf("\n"); 

	  			} 

				exit(0);

			}


			else if (choice == 3)

			{

				char insertQuery [350];

				char UPC;

				char game_title;

				char developer;

				char price;

				char quantity;


				printf("Please enter game criteria:\n");

				printf("Enter UPC Code:");

				scanf("%s", &UPC);			


				printf("Enter Game Title:");

				scanf("%s", &game_title);


				printf("Enter Developer:");

				scanf("%s", &developer);


				printf("Enter Game Price:");

				scanf("%s", &price);


				printf("Enter Quantity on Hand:");

				scanf("%s", &quantity);


				conn = mysql_init(NULL);

				if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) 

				{

					fprintf(stderr, "%s\n", mysql_error(conn));

					exit(1);

				}


		       		sprintf(insertQuery, "INSERT INTO BARCODE_SCAN(BARCODE_ID, GAME_TITLE, PRICE, QUANTITY) VALUES (('%s', '%s', '%s', '%s', '%s')", &UPC, &game_title, &developer, &price, &quantity);


				mysql_query(conn,insertQuery);

					

				if(result)

			        {

			       		mysql_free_result(result);

			       		result = NULL;

		 		}


				

			}

			

			else if (choice == 4)

			{

				printf("option not available yet");

			}

	 

			else if (choice == 5)

			{

				exit(0);

			}


			else 

			{

				printf("Invalid Choice");

			}


		}


		while (choice !=5);

		{

			/* close connection */

			mysql_free_result(result);

			mysql_close(conn);

		}

		return;

	}

}

Okay I'm back. I fixed option two for the time being. I know my indentation may be off a bit (Allman's Style) because of all the elting and fixing I have been doing I have decided to run back through at the end to fix the indentation errors if any.

My questions is on Option 3. I was getting Bus Errors and now I have it compiling. I think the error is coming from the data types for the variables and the way I'm trying to grab them in my sprintf statement.

Like I said, no errors and it compiles fine. Just doesn't work properly. Was hoping to get a any suggestions on what may fix it. I think option 4 will work relatively similar just deleting a row from the table based on the Barcode ID. So I'm not worried about that right now.

Thanks!

**Myles8908** · 12-09-2014

Also on line 331 it has the other variables for the statement it just runs off the page.

Sorry for the double spacing as well. Wordpad did that.

**rags_to_riches** · 12-09-2014

Code:

char *server = "localhost";
char *user = "root";
char *password = "1";   /* set me first */
char *database = "PROJECT";

Those should all be const char *s.

Code:

char UPC;
char game_title;
char developer;
char price;
char quantity;

For most of your program you seem to know what a C-string is, and yet here you completely forgot. Those can only hold *one* character.

Code:

scanf("%s", &UPC);

scanf for C-strings should not use &s

Code:

sprintf(insertQuery, "INSERT INTO BARCODE_SCAN(BARCODE_ID, GAME_TITLE, PRICE, QUANTITY) VALUES (('%s', '%s', '%s', '%s', '%s')", &UPC, &game_title, &developer, &price, &quantity);

*printf should not use &s. You should also be using mysql_real_escape_string on these variables to prevent SQL injection.

Code:

mysql_query(conn,insertQuery);
if(result)

result??? Where did that come from and where did it get set?

**Myles8908** · 12-10-2014

Originally Posted by rags_to_riches

Code:

char *server = "localhost";
char *user = "root";
char *password = "1";   /* set me first */
char *database = "PROJECT";

Those should all be const char *s.

Code:

char UPC;
char game_title;
char developer;
char price;
char quantity;

For most of your program you seem to know what a C-string is, and yet here you completely forgot. Those can only hold *one* character.

Code:

scanf("%s", &UPC);

scanf for C-strings should not use &s

Code:

sprintf(insertQuery, "INSERT INTO BARCODE_SCAN(BARCODE_ID, GAME_TITLE, PRICE, QUANTITY) VALUES (('%s', '%s', '%s', '%s', '%s')", &UPC, &game_title, &developer, &price, &quantity);

*printf should not use &s. You should also be using mysql_real_escape_string on these variables to prevent SQL injection.

Code:

mysql_query(conn,insertQuery);
if(result)

result??? Where did that come from and where did it get set?

Thank you, I totally forgot that in C a string was one char and I needed to concatenate. I'm simultaneously working in C# and just had a brain fart. I got option 1,2,4 all to work. And 3 still some error but this helped I'm working on it. Thank you fro the timely responses. This forum really helped!

Thread: Need help pulling information from MySQL

Thread Tools

Search Thread

Display

Need help pulling information from MySQL

Similar Threads

Help writing a C code for pulling information out of files

pulling 2 doubles from a string?

Pulling my hair out!

Pulling an all-nighter

pulling strings