We will start by invoking the mysql_init function to initialize a MYSQL object by the name conn. We will then pass the MYSQL object conn to the mysql_real_connect function that we invoked to establish a connection to the MySQL server running at the specified host. Several other parameters will also be passed to the mysql_real_connection function, including a valid user ID, password, host details, and the database with which we want to work. The mysql_real_connect function will establish a connection to the MySQL server running at the specified host and will declare a MYSQL object conn as the connection handler. This means thar conn can connect to the MySQL server and the commerce database wherever it is used.
The program will terminate after displaying an error message if some error occurs while establishing a connection to the MySQL server engine or to the ecommerce database. If the connection to the MySQL database engine is established successfully, you will be prompted to enter the email address of the user whose record you want to delete.
We will first display the information of the user and thereafter will seek permission from the user as to whether they really want to delete that row or not. So, we will create an SQL SELECT statement that will search the row from the users table that matches the email address that was 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.
Again, the program will terminate after displaying an error message if the SQL query does not execute successfully or some other error occurs. If the query executes successfully, then the resulting row(s) (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 resultset.
We will invoke the mysql_num_rows function to ensure that there is at least one row in the resultset. If there is no row in the resultset, that means no row was found in the users table that matches the given email address. In that case, the program will terminate after informing that no row was found in the users table with the given email address. If there is even a single row in the resultset, we will invoke the mysql _fetch_row function on the resultset, which will extract one row from the resultset and assign it to the array row.
The users table contains the following three columns:
- email_address varchar(30)
- password varchar(30)
- address_of_delivery text
The array row will contain information of the accessed row, where the subscripts row[0], row[1], and row[2] will contain the data of the columns email_ address, password, and address_of_delivery, respectively. The current information of the user will be displayed by displaying the current email address, password, and address of delivery that's assigned to the subscripts row[0], row[1], and row[2]. Then, we will invoke the mysql_free_result function to free up the memory that is allocated to the resultset.
At this stage, the user will be asked to confirm whether they really want to delete the shown record. The user is supposed to enter yes, all in lowercase, to delete the record. If the user enters yes, an SQL DELETE statement will be created that will delete the row from the users table that matches the specified email address. The mysql_query function will be invoked and the SQL DELETE statement will be passed to it, along with the connection handler conn.
If any error occurs in executing the SQL DELETE query, again an error message will be displayed and the program will terminate. If the SQL DELETE statement executes successfully, a message informing that the user account with the specified mail address has been deleted successfully is displayed. Finally, 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 MySQL 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 giving 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 | coffee | 444, Sky Valley, Toronto, Canada
|
| 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)
From the preceding output, we can see 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 deleteuser.c program, as shown here:
$ gcc deleteuser.c -o deleteuser -I/usr/local/include/mysql -L/usr/local/lib/mysql -lmysqlclient
If you get no errors or warnings, that means the deleteuser.c program has compiled into an executable file, deleteuser.exe. Let's run this executable file:
$ ./deleteuser
Enter email address of the user to delete: harwanibintu@gmail.com
No user found with this email address
Now, let's run the program again with a valid email address:
$ ./deleteuser
Enter email address of the user to delete: bmharwani@yahoo.com
The details of the user with this email address are as follows:
Email Address: bmharwani@yahoo.com
Password: coffee
Address of delivery: 444, Sky Valley, Toronto, Canada
Are you sure you want to delete this record yes/no: yes
The user with the given email address is successfully deleted from the users table
So, the row of the user with the email address bmharwani@yahoo.com will be deleted from the users table. To confirm that the row has been deleted from the users database table too, switch to the Terminal window where the MySQL command line is running and issue the following SQL SELECT command:
MySQL [ecommerce]> select * from users;
+---------------------+----------+------------------------------------+
| email_address | password | address_of_delivery
|
+---------------------+----------+------------------------------------+
| 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
+---------------------+----------+------------------------------------+
Voila! We can see that now there are only two rows left in the users table, confirming that one row has been deleted from the users table.