Thread: Need help pulling information from MySQL

  1. #1
    Registered User
    Join Date
    Dec 2014
    Location
    Kokomo, Indiana, United States
    Posts
    6

    Need help pulling information from MySQL

    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!

  2. #2
    and the hat of int overfl Salem's Avatar
    Join Date
    Aug 2001
    Location
    The edge of the known universe
    Posts
    37,539
    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.
    If you dance barefoot on the broken glass of undefined behaviour, you've got to expect the occasional cut.
    If at first you don't succeed, try writing your phone number on the exam paper.

  3. #3
    Registered User
    Join Date
    Dec 2014
    Location
    Kokomo, Indiana, United States
    Posts
    6
    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.

  4. #4
    Registered User
    Join Date
    Oct 2008
    Location
    TX
    Posts
    2,059
    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.

  5. #5
    Registered User
    Join Date
    Dec 2014
    Location
    Kokomo, Indiana, United States
    Posts
    6
    Quote Originally Posted by itCbitC View Post
    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!

  6. #6
    Registered User
    Join Date
    Dec 2014
    Location
    Kokomo, Indiana, United States
    Posts
    6
    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!

  7. #7
    Registered User
    Join Date
    Dec 2014
    Location
    Kokomo, Indiana, United States
    Posts
    6
    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.

  8. #8
    a_capitalist_story
    Join Date
    Dec 2007
    Posts
    2,671
    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?

  9. #9
    Registered User
    Join Date
    Dec 2014
    Location
    Kokomo, Indiana, United States
    Posts
    6

    Talking

    Quote Originally Posted by rags_to_riches View Post
    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!

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Replies: 7
    Last Post: 02-08-2012, 11:26 PM
  2. pulling 2 doubles from a string?
    By rodrigorules in forum C++ Programming
    Replies: 5
    Last Post: 03-20-2010, 05:14 AM
  3. Pulling my hair out!
    By paraplayer in forum C++ Programming
    Replies: 4
    Last Post: 09-14-2005, 10:18 PM
  4. Pulling an all-nighter
    By Maverik in forum A Brief History of Cprogramming.com
    Replies: 13
    Last Post: 04-24-2003, 10:50 AM
  5. pulling strings
    By ... in forum C++ Programming
    Replies: 4
    Last Post: 03-13-2002, 07:31 PM