We will start by invoking the mysql_init function to initialize a MYSQL object by the name conn. Thereafter, we will invoke the mysql_real_connect function and pass the MYSQL object conn to it along with the valid user ID, password, and the host details. The mysql_real_connect function will establish a connection to the MySQL server running at the specified host and will also connect to the supplied database, ecommerce. The MYSQL object conn will act as the connection handler for the rest of the program. Wherever a connection to the MySQL server and ecommerce database is required, referring to conn will suffice.
If any error occurs in establishing a connection to the MySQL database engine or the ecommerce database, an error message will be displayed and the program will terminate. If a connection to the MySQL database engine is established successfully, you will be prompted to enter the email address of the user whose details you want to search for.
We will create an SQL SELECT statement that will search the row in the users table that matches the email address entered by the user. Then, we will invoke the mysql_query function and pass the created SQL SELECT statement to it, along with the connection handler conn. If the SQL query does not execute or some error occurs, the program will terminate after displaying an error message. If the query is successful, then the resulting row(s) that satisfy the condition (that is, the row(s) that match the supplied email address) will be retrieved by invoking the mysql_use_result function and will be assigned to the result set, resultset.
We will then invoke the mysql _fetch_row function in a while loop that will extract one row at a time from the resultset; that is, the first row from the resultset will be accessed and assigned to the array row.
Recall that the users table contains the following columns:
- email_address varchar(30)
- password varchar(30)
- address_of_delivery text
Consequently, the array row will contain complete information of the accessed row, where the subscript row[0] will contain the data of the email_ address column, row[1] will contain the data of the column password, and row[2] will contain the data of the address_of_delivery column. The information of the entire row will be displayed by displaying the subscripts row[0], row[1], and row[2], respectively.
At the end, we will invoke the mysql_free_result function to free up the memory that was allocated to the resultset. Then, we will invoke the mysql_close function to close the opened connection handler conn.
Let's open the Cygwin Terminal. We will require two Terminal windows; on one window, we will run SQL commands and on the other, we will compile and run C. Open another Terminal window by pressing Alt+F2. In the first Terminal window, invoke the MySQL command line by using the following command:
$ mysql -u root -p -h 127.0.0.1
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.7.14-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
To work with our ecommerce database, we need to make it the current database. So, open the ecommerce database by using the following command:
MySQL [(none)]> use ecommerce;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
Now, ecommerce is our current database; that is, whatever SQL commands we will execute will be applied to the ecommerce database only. Let's use the following SQL SELECT command to see the existing rows in the users database table:
MySQL [ecommerce]> select * from users;
+---------------------+----------+------------------------------------+
| email_address | password | address_of_delivery |
+---------------------+----------+------------------------------------+
| bmharwani@yahoo.com | gold | 11 Hill View Street, New York, USA
| harwanibm@gmail.com | diamond | House No. xyz, Pqr Apartments, Uvw Lane, Mumbai, Maharashtra | | bintu@gmail.com | platinum | abc Sea View, Ocean Lane, Opposite Mt. Everest, London, UKg
+---------------------+----------+------------------------------------+
3 rows in set (0.00 sec)
The given output shows that there are three rows in the users table.
To compile the C program, switch to the second Terminal window. Let's use GCC to compile the searchuser.c program, as shown here:
$ gcc searchuser.c -o searchuser -I/usr/local/include/mysql -L/usr/local/lib/mysql -lmysqlclient
If you get no errors or warnings, that means the searchuser.c program has compiled into an executable file, searchuser.exe. Let's run this executable file:
$ ./searchuser
Enter email address to search: bmharwani@yahoo.com
The details of the user with this email address are as follows:
Email Address:bmharwani@yahoo.com
Password: gold
Address of delivery: 11 Hill View Street, New York, USA
Voila! We can see that complete information of the user with their email address, bmharwani@yahoo.com, is displayed on the screen.
Now, let's move on to the next recipe!