In this program, we first ask the user to enter the email address they want to update. Then, we search the users table to see if there is any row with the matching email address. If we find it, we display the current information of the user; that is, the current email address, password, and address of delivery. Thereafter, we ask the user to enter a new password and new address of delivery. The new password and address of deliver will replace the old password and address of delivery, thereby updating the users table.
We will start by invoking the mysql_init function to initialize a MYSQL object by the name conn. Then, we will 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 the connection to the MySQL server running at the specified host and will declare the MYSQL object conn as the connection handler. This means that conn can connect to the MySQL server and the ecommerce database wherever it is used.
The program will terminate after displaying an error message if some error occurs while establishing the 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 update.
As we mentioned earlier, we will first display the current information of the user. So, we will create an SQL SELECT statement and we will search the row in the users table that matches the email address that's 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 then 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, this means that no row was found in the users table that matches the given email address. In this 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 the 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 is displayed by displaying the information assigned to the aforementioned subscripts. 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 enter the new password and the new address of delivery. We will prepare an SQL UPDATE statement that contains the information of the newly entered password and address of delivery. The mysql_query function will be invoked and the SQL UPDATE statement will be passed to it, along with the connection handler conn.
If any error occurs in executing the SQL UPDATE query, again, an error message will be displayed and the program will terminate. If the SQL UPDATE statement executes successfully, a message informing that the user's information has been updated successfully will be 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 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)
We can see from the preceding output 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 updateuser.c program, as shown here:
$ gcc updateuser.c -o updateuser -I/usr/local/include/mysql -L/usr/local/lib/mysql -lmysqlclient
If you get no errors or warnings, that means the updateuser.c program has compiled into an executable file, updateuser.exe. Let's run this executable file:
$ ./updateuser
Enter email address of the user to update: harwanibintu@gmail.com
No user found with this email address
Let's run the program again and enter an email address that already exists:
$ ./updateuser
Enter email address of the user to update: 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
Enter new password: coffee
Enter new address of delivery: 444, Sky Valley, Toronto, Canada
The information of user is updated successfully in users table
So, we have updated the row of the user with the email address, bmharwani@yahoo.com. To confirm that the row has been updated in 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]> 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
+---------------------+----------+------------------------------------+
Voila! We can see that the row of the users table with the email address bmharwani@yahoo.com has been updated and is showing the new information.
Now, let's move on to the next recipe!