In this article by Surendra Mohan, the author of Node.js Essentials, we will cover the following concepts:
Reading and writing files using Node.js
MySQL database handling using Node.js
Working with data formats using Node.js
Let's get started!
(For more resources related to this topic, see here.)
Reading and writing files
The easiest and most convenient way of reading a file in a PHP application is by using the PHP file_get_contents() API function. Let's look into the following example PHP code snippet, wherein we intend to read a sample text file named sampleaf.txt that resides in the same directory as that of our PHP file (sampleaf.php):
<?php
$text = file_get_contents('sampleaf.txt');
print $text;
?>
In the preceding code snippet, if the source file, sampleaf.txt, exists or can be read, the content of this file is assigned to the $text variable (long string type); otherwise, it will result in a Boolean value as false.
All PHP API functions are blocking in nature, so is the file_get_contents() API function. Thus, the PHP code that is supposed to be executed after the file_get_contents() API function call gets blocked until the former code either executes successfully or completely fails. There is no callback mechanism available for this PHP API.
Let's convert the preceding PHP code snippet into its corresponding Node.js code. Because the readFileSync() API function in the fs module is the closest Node.js equivalent to that of the PHP file_get_contents() API function, let's use it. Our Node.js code equivalent looks something like the following code snippet (sampleaf.njs):
var fs = require('fs');
var text = false;
try {
text = fs.readFileSync(__dirname+'/'+'sampleaf.txt', 'utf8');
} catch (err) {
// No action
}
console.log(text);
Node.js functions come in both asynchronous as well as synchronous forms, asynchronous being the default. In our preceding Node.js code, we have appended the Sync term in our Node.js fs.readFile() API function, which is asynchronous in nature, and gets converted to its synchronous version once Sync gets appended to the end of it.
The asynchronous version is nonblocking in nature, and depends upon the callbacks to take care of the Node.js API function call results. On the other hand, the synchronous version is blocking in nature (same as of our PHP code), which results in blocking of the Node.js code that is supposed to be executed after the API function till it completely succeeds or fails.
If we look into the arguments passed with the Node.js fs.readFileSync() API function, we find the source file sampleaf.txt (prepended with the _dirname variable) that needs to be read, and utf8 stating the encoding we intend to use. The _dirname variable holds the directory name where our Node.js code file, sampleaf.njs, resides. The use of the _dirname variable instructs the Node.js fs.readFileSync() API function to locate the source file in the directory returned by this variable. If this variable is missing, our API function will try to find the source file in the directory where the Node.js server was started. By default, the second argument doesn't encode, which results in the function to return a raw buffer of bytes instead of a string. In order to enable the function to return a string, we pass the utf8 string (for UTF-8 encoding) as the second parameter to the function.
Because we are dealing with the synchronous version of the API function, we handled Node.js exceptions by using the Node.js try and catch keywords. In this case, if the try block code gets executed successfully, the catch block code will be ignored and never get executed; otherwise, the try block code will immediately stop executing, thereby helping the catch block code to get executed.
While replacing the PHP file_get_contents() API function with its corresponding Node.js API function, it is recommended to use the Node.js fs.readFile() API function instead of fs.readFileSync() due to the fact that synchronous API functions (in our case, fs.readFileSync()) are blocking in nature, whereas asynchronous API functions (in our case, fs.readFile()) are not. So, let's try converting the preceding PHP code snippet to its corresponding asynchronous Node.js code by using the fs.readFile() API function. We write the following Node.js code snippet and save it in a new file with the filename sampleafa.njs:
var fs = require('fs');
var text = false;
fs.readFile(__dirname+'/'+'sampleaf.txt', 'utf8', function(err, fo) {
if (!err) {
text = fo;
}
console.log(text);
});
Our preceding asynchronous Node.js fs.readFile() API function accepts a callback function as its third argument that can return both the data as well as error, whichever is applicable.
There is another way to read a file in Node.js. However, it doesn't match with any features available in PHP so far. We do so by creating a Node.js stream that will help us read the file. While the stream is read, events such as data, error, and close, are sent along with the stream. In such scenarios, we need to set up event handlers that would take care of such events.
The PHP file() API function
The file() API function helps us read content of a file and returns it as an indexed array. The content in the array are stored in such a way that each value of the array holds a single line of the file. If we want to print the first line of our source file (sampleaf.txt) in PHP, we write the following code snippet that includes the End Of Line (EOL) character sequence at the end of the line:
$x = file('sampleaf.txt');
print $x[0];
If we are using PHP5 version, we get an opportunity to include the second and optional parameter (the flags parameter) to our file() API function. The flags parameter provides us with three options that can be either used individually or can be combined together using the OR operator (|), and they are as follows:
FILE_IGNORE_NEW_LINES
FILE_SKIP_EMPTY_LINES
FILE_USE_INCLUDE_PATH
The FILE_IGNORE_NEW_LINES flag option is normally used, and it instructs the PHP file() API function to eradicate EOL characters from the end of each line.
Let's rework on our preceding PHP code snippet such that it prints the first line of the sampleaf.txt file, but eradicates the EOL character sequence at the end of each value in the array. So, our modified PHP code snippet will look like the following:
$x = file('sampleaf.txt', FILE_IGNORE_NEW_LINES);
print $x[0];
Now it's time to convert the preceding PHP code snippet into its corresponding Node.js code snippet. Converting the PHP file() API function is a bit complicated as compared to that of converting the PHP file_get_contents() API function. The following code demonstrates the converted Node.js code snippet corresponding to our PHP code snippet:
var fs = require('fs');
var FILE_IGNORE_NEW_LINES = 0x2;
var x = false;
var flag = FILE_IGNORE_NEW_LINES;
fs.readFile(__dirname+'/'+'sampleaf.txt', 'utf8', function(err, data) {
if (!err) {
x = data.replace(/rn?/g,'n');
x = x.split('n');
x.neol = x.length - 1;
if ((x.length > 0) && (x[x.length-1] === '')) {
x.splice(x.length-1, 1);
}
if ((flag & FILE_IGNORE_NEW_LINES) === 0) {
for (var i=0; i < x.neol; ++i) {
x[i] += 'n';
}
}
delete x.neol;
}
console.log(x[0]);
});
In the preceding Node.js code, the !err condition within the if statement is the real culprit that makes this code actually complicated.
Let's now walk through the preceding Node.js code snippet, especially the ones we have embedded in the if statement:
First of all, we converted EOL characters for the Linux, Windows, and Mac text files into the end-of-line character (n) for the operating system our Node.js server is current running, using the following code chunk:
x = data.replace(/rn?/g,'n');
Then, we converted the string to an array of lines that complies with the PHP file() API function standards, using the following line of code:
x = x.split('n');
Then, we handled the last line of the file by implementing the following code snippet:
x.neol = x.length - 1;
if ((x.length > 0) && (x[x.length-1] === '')) {
x.splice(x.length-1, 1);
}
Finally, in the following code snippet, we check whether FILE_IGNORE_NEW_LINES has been specified or not. If it hasn't been specified, the EOL character will be added to the end of the lines:
if ((flag & FILE_IGNORE_NEW_LINES) === 0) {
for (var i=0; i < x.neol; ++i) {
x[i] += 'n';
}
}
File handling APIs
The core set of file handling APIs in PHP and Node.js are shaped based on the C language file handling API functions. For instance, the PHP fopen() API function opens a file in different modes, such as read, write, and append. The Node.js open() API function is the equivalent to this PHP fopen() API function, and both of these API functions are shaped with the fopen() from the C language.
Let's consider the following PHP code snippet that opens a file for reading purposes and reads the first 500 bytes of content from the file:
$fo = fopen('sampleaf.txt', 'r');
$text = fread($fo, 500);
fclose($fo);
In case the file size is less than 500 bytes, our preceding PHP code snippet will read the entire file.
In Node.js, the Node.js fs.read() API function is used to read from the file and uses the buffer built-in module to hold an ordered collection of bytes. Likewise, the Node.js fs.close() API function is used to close the file once it is read as intended. In order to convert the preceding PHP code snippet, we write the following Node.js code snippet:
var fs = require('fs');
var Buffer = require('buffer').Buffer;
fs.open(__dirname+'/'+'sampleaf.txt', 'r', function(err, fo) {
var text = '';
var b = new Buffer(500);
fs.read(fo, b, 0, b.length, null, function(err, bytesRead, buf){
var bufs = buf.slice(0, bytesRead);
text += bufs.toString();
fs.close(fo, function() {
console.log(text);
});
});
});
In our Node.js code, besides the usual callback functions, we have used a couple of buffer variables, such as the b and bufs variables that adds some complexity to our code. The b variable holds the data that is read using the Node.js fs.read() API function. The bufs variable holds the actual bytes that are read, wherein the unused bytes of the b variable are sliced off. The buf argument is an alias of the b variable.
Both PHP and the Node.js maintain a file pointer that indicates the next bytes that should be read from the file. We can cross-check the end of the file using the PHP feof() API function. In order to implement the PHP feof() API function, we write the following PHP code snippet:
$fo = fopen('sampleaf.txt', 'r');
$text = '';
while (!feof($fo)) {
$text .= fread($fo, 500);
}
fclose($fo);
print $text;
Node.js doesn't have anything that is equivalent to the PHP feof() API function. Instead, we use the bytesRead argument that is passed to the callback function and is compared with the number of bytes requested in order to read the file. We land to the following Node.js code snippet when we convert our preceding and modified PHP code snippet:
var fs = require('fs');
var Buffer = require('buffer').Buffer;
fs.open(__dirname+'/'+'sampleaf.txt', 'r', function(err, fo) {
var text = '';
var b = new Buffer(500);
var fread = function() {
fs.read(fo, b, 0, b.length, null, function(err, bytesRead, buf) {
var eof = (bytesRead != b.length);
if (!eof) {
text += buf.toString();
fread();
} else {
if (bytesRead > 0) {
var bufs = buf.slice(0, bytesRead);
text += bufs.toString();
}
fs.close(fo, function() {
console.log(text);
});
}
});
};
fread();
})
Due to callbacks in Node.js, the fread function variable must be defined in such a way that it can be called if the file size is greater than the b buffer variable. The fread function variable is triggered continuously till the end of the file.
By the end of the file, the partially occupied buffer is proceeded, and then the Node.js fs.close() API function is triggered. We also use the linearity concept, where the Node.js console.log() API function call is embedded in the callback of the Node.js fs.close() API function.
MySQL access
In the previous section, we learned how to access the data from files using PHP code and exercised how to convert such PHP code to its corresponding Node.js code.
As an alternative to what we discussed earlier, we can even access the necessary data from our database, and write to it as a record or set of records. Because the database server can be accessed remotely, PHP and Node.js are capable enough to connect to the intended database, regardless of whether it is running on the same server or remote server. You must be aware that data in a database is arranged in rows and columns. This makes it easy to organize and store data such as usernames. On the other hand, it is quite complex to organize and store certain types of data, such as image files or any other media files.
In this section, we will use the MySQL database with PHP, and learn how to convert our PHP code that uses the MySQL database into its equivalent Node.js code based on different scenarios. The reason behind choosing the MySQL database for our exercise is that it is quite popular in the database and hosting market. Moreover, PHP applications have a special bond with MySQL database.
We assume that the MySQL server has already been installed, so that we can create and use the MySQL database with the PHP and Node.js code during our exercise. In order to access our database through a PHP or Node.js application, our web application server (where PHP or Node.js is running) needs some tweaking, so that necessary accesses are granted to the database. If you are running the Apache2 web server on a Linux environment, the phpx-myql extension needs to be installed, where x denotes the PHP version you are using. For instance, when using PHP 5.x, the required and related extension that needs to be installed would be php5-mysql. Likewise, the php4-mysql and php6-mysql extensions are necessary for PHP versions 4.x and 6.x, respectively. On the other hand, if you are using the Apache2 web server on a Windows environment, you need to install the PHP-to-MySQL extension during the Apache2 web server installation.
Database approaches
Node.js doesn't have a built-in module that can help a Node.js application access the MySQL database. However, we have a number of modules that are provided by the Node.js npm package and can be installed in order to achieve database access in variety of approaches.
Using the MySQL socket protocol
MySQL socket protocol is one of the easiest approaches that can be implemented in Node.js using the Node.js npm package. This npm package uses the built-in net module to open a network socket for the MySQL server to connect with the application and exchange packets in a format that is supported and expected by the MySQL server. The Node.js npm package bluffs and surpasses other MySQL drivers (shipped with the MySQL server installer), unaware of the fact that it is communicating to the Node.js driver instead of the default driver that has been built in C language.
There are a number of ways MySQL socket protocol can be implemented in Node.js. The most popular implementation is using the Node.js node-mysql npm package. In order to install this npm package, you can either retrieve it from its GitHub repository at http://github.com/felixge/node-mysql or run npm install mysql on the command line. An alternative to the Node.js implementation of this protocol is the Node.js mysql-native npm package. In order to install this package, you can either retrieve it from its GitHub repository at http://github.com/sidorares/nodejs-mysql-native, or run npm install mysql-native on the command line.
In order to play around with database records, the SQL language that needs to be applied constitutes of commands such as SELECT, INSERT, UPDATE, and DELETE along with other commands. However, Node.js stores data in the database as properties on a Node.js object.
Object-relational mapping (ORM or O/R mapping) is a set of planned actions to read and write objects (in our case, Node.js objects) to a SQL-based database (in our case, the MySQL database). This ORM is implemented on the top of other database approaches. Thus, the Node.js ORM npm package can use any other Node.js npm packages (for instance, node-mysql and mysql-native) to access and play around with the database. Normally, ORM npm packages use SQL statements during implementation; however, it provides a better and logical set of API functions to do the database access and data exchange job.
The following are a couple of Node.js npm modules that provide object-relational mapping support to Node.js:
The Node.js persistencejs npm module: This is an asynchronous JavaScript-based ORM library. We recommend you to refer its GitHub repository documentation at https://github.com/coresmart/persistencejs, in case you wish to learn about it.
The Node.js sequelize npm module : This is a JavaScript-based ORM library that provides access to databases such as MySQL, SQLite, PostgreSQL, and so on, by mapping database records to objects and vice versa. If you want to learn more about the sequelize library, we recommend that you refer to its documentation at http://sequelizejs.com/.
Normally, an object-relational mapping layer makes the Node.js world quite simple, convenient, and developer friendly.
Using the node-mysql Node.js npm package
In this section, we will learn how to implement the Node.js node-mysql npm package, which is the most popular way of accessing a MySQL database using Node.js.
In order to use the node-mysql package, we need to install it. This Node.js npm module can be installed in the same way as we install other Node.js npm packages. So, to install it, we run the following command:
npm install mysql
As soon as the node-mysql package gets installed, we need to make this package available for use by using the Node.js require() API function. To do so, we create a mysql variable to access the Node.js node-mysql module, as demonstrated in the following line of Node.js code:
var mysql = require('mysql');
Before you can use database records to read or write, it is mandatory to connect your PHP or Node.js application to this database. In order to connect our PHP application to our MySQL database, we use three sets of the PHP API function (mysql, mysqli, and PDO) that use the PDO_MySQL driver. Let's write the following PHP code snippet:
$sql_host = '192.168.0.100';
$sql_user = 'adminuser';
$sql_pass = 'password';
$conn = mysql_connect($sql_host, $sql_user, $sql_pass);
In the preceding code snippet, the $conn variable holds the database collection. In order to establish the database connection, we used the PHP mysql_connect() API function that accepts three arguments: database server as the IP address or DNS (in our case, the IP address is 192.168.0.100), database username (in our case, adminuser), and the password associated to the database user (in our case, password).
When working with the node-mysql Node.js npm package, the Node.js createClient() API function is used as the equivalent to the PHP mysql_connect() API function. Unlike the PHP API function, the Node.js API function accesses a Node.js object with the three properties as its parameters. Moreover, we want our Node.js code to load the mysql Node.js npm package. Thus, we use Node.js require() to achieve this. Let's write the following Node.js code snippet that is equivalent to our preceding PHP code snippet:
Var mysql = require('mysql');
var sql_host = '192.168.0.100';
var sql_user = 'adminuser';
var sql_pass = 'password';
var sql_conn = {host: sql_host, user: sql_user, password: sql_pass};
var conn = mysql.createClient(sql_conn);
We can even merge the last two statements (highlighted) in a single statement. Thus, we replace the highlighted statements with the following one:
var conn = mysql.createClient({host: sql_host, user: sql_user, password: sql_pass});
In the case of both the PHP $conn and Node.js conn variables, a meaningful value is assigned to these variables if the MySQL server is accessible; otherwise, they are assigned a false value.
Once the database is no longer needed, it needs to be disconnected from our PHP and Node.js code.
Using PHP, the MySQL connection variable (in our case, $conn) needs to be closed using the PHP mysql_close() API function by implementing the following PHP code statement:
$disconn = mysql_close($conn);
The PHP mysql_close() API function returns a Boolean value that indicates whether the connection has been closed successfully or failed.
In the case of Node.js, we use the destroy() method on the conn object in order to close the database connection using the following Node.js code statement:
conn.destroy();
Once our applications get connected to the desired MySQL database on the MySQL server, the database needs to be selected. In case of PHP, the PHP mysql_select_db() API function is used to do this job. The following PHP code snippet demonstrates how we select the desired database:
$sql_db = 'desiredDB';
$selectedDB = mysql_select_db($sql_db, $conn);
While converting the PHP code into its equivalent Node.js code, it should be refactored to explicitly pass the PHP $conn variable to all the mysql API functions. As soon as the database is selected, we use the PHP mysql_query() API function to play around with the data of the selected database.
In the case of Node.js, we use Node.js query() methods, which is equivalent to its corresponding PHP code statement. In order to select a database, the SQL USE command is used as demonstrated in the following code line:
USE desiredDB;
In the preceding statement, if we are using a single database, the semicolon (;) is optional. It is used as a separator in case you plan to use more than one database.
When working with Node.js, the USE desiredDB SQL command needs to be sent using the Node.js query() method. Let's write the following Node.js code snippet that selects the desiredDB database from our MySQL server via the Node.js conn variable:
var sql_db = 'desiredDB';
var sql_db_select = 'USE '+sql_db;
conn.query(sql_db_select, function(err) {
if (!err) {
// Selects the desired MySQL database, that is, desiredDB
} else {
// MySQL database selection error
}
});
We can even merge the highlighted statements in the preceding code snippet into one statement. Our Node.js code snippet will look something like the following once these statements get merged:
var sql_db = 'desiredDB';
conn.query('USE '+sql_db, function(err) {
if (!err) {
// Selects the desired MySQL database, that is, desiredDB
} else {
// MySQL database selection error
}
});
By now, we are able to connect our PHP and Node.js applications to the MySQL server and select the desired MySQL database to play around with. Our data in the selected database can be accessed (in terms of reading and writing) using popular SQL commands, such as CREATE TABLE, DROP TABLE, SELECT, INSERT, UPDATE, and DELETE.
Creating a table
Let's consider the CREATE TABLE SQL command. In PHP, the CREATE TABLE SQL command is triggered using the PHP myql_query() API function, as demonstrated in the following PHP code snippet:
$sql_prefix = 'desiredDB_';
$sql_cmd =
'CREATE TABLE `'.$sql_prefix.'users` (`id` int AUTO_INCREMENT KEY, `user` text)';
$tabCreated = mysql_query($sql_cmd, $conn);
In the preceding PHP code snippet, we created a table, desiredDB_users, which consists of two columns: id and user. The id column holds an integer value and possesses the SQL AUTO_INCREMENT and KEY options. These SQL options indicate that the MySQL server should set a unique value for each row that is associated to the id column, and that the user has no control over this value. The user column holds string values and is set with the value indicated by the requester when a new row is inserted into our MySQL database.
Let's write the following Node.js code snippet, which is equivalent to our preceding PHP code:
var sql_prefix = 'desiredDB_';
var sql_cmd =
'CREATE TABLE `'+sql_prefix+'users` (`id` int AUTO_INCREMENT KEY, `user` text)';
var tabCreated = false;
conn.query(sql_cmd, function(err, rows, fields) {
if (!e) {
tabCreated = true;
}
});
Here, the err parameter that is placed in our query() method's callback function indicates whether any error has been triggered or not.
Deleting a table
Let's now learn how to delete a table and attempt to delete the same table, users, we just created. This activity is quite similar to creating a table, which we recently discussed.
In PHP, we use the DROP TABLE SQL command to achieve our purpose as demonstrated in the following PHP code snippet:
$sql_prefix = 'desiredDB_';
$sql_cmd = 'DROP TABLE `'.$sql_prefix.'users`';
$tabDropped = mysql_query($sql_cmd, $conn);
Converting the preceding PHP code snippet into its corresponding Node.js code snippet, we follow the same basis as we discussed while creating the table. Our converted Node.js code snippet will look like the following code snippet:
var sql_prefix = 'desiredDB_';
var sql_cmd = 'DROP TABLE `'+sql_prefix+'users`';
var tabDropped = false;
conn.query(sql_cmd, function(err, rows, fields) {
if (!err) {
tabDropped = true;
}
});
Using a SELECT statement
Coming to the SQL SELECT statement, it is used to read data from the database tables and functions in a bit different way. In PHP, the PHP mysql_query() API function triggers the statement and returns a result object that is stored in the PHP $sql_result variable. In order to access the actual data, the PHP mysql_fetch_assoc() API function is implemented that runs in a loop in order to fetch the data from more than one row. We assume that we have retained our users table and all the records that we had deleted recently. Our PHP code snippet will look like the following one:
$sql_prefix = 'desiredDB_';
$sql_cmd = 'SELECT user FROM `'.$sql_prefix.'users`';
$sql_result = mysql_query($sql_cmd, $conn);
while ($row = mysql_fetch_assoc($sql_result)) {
$user = $row['user'];
print $user;
}
It is always good to extract the PHP $row variable into an array-free variable (in our case, $user), due to the fact that doing so eliminates complexity when converting PHP code to its corresponding Node.js code.
When converting the preceding PHP code snippet into a Node.js code snippet, we use the Node.js query() method to trigger the statement that returns the data as arguments to the callback function. The rows parameter to the callback function holds a two-dimensional array of data, that is, an indexed array of rows along with the array of values associated to each row. Our Node.js code snippet for the preceding PHP code snippet will look like the following one:
var sql_prefix = 'desiredDB_';
var sql_cmd = 'SELECT user FROM `'.$sql_prefix.'users`';
conn.query(sql_cmd, function(err, rows, fields) {
if (!err) {
for (var i=0; i < rows.length; ++i) {
var row = rows[i];
var user = row['user'];
console.log(user);
}
}
});
In the preceding Node.js code snippet, we could have defined row[i]['user'] so as to show that the Node.js rows variable is a two-dimensional array.
Using the UPDATE statement
Now, let's try out implementing the SQL UPDATE statement that is used to modify any data of a table. In PHP, we trigger the SQL UPDATE statement by using the PHP mysql_query() API function, as demonstrated in the following code snippet:
$sql_prefix = 'desiredDB_';
$sql_cmd =
'UPDATE `'.$sql_prefix.'users` SET `user`="mohan" WHERE `user`="surendra"';
$tabUpdated = mysql_query($sql_cmd, $conn);
if ($tabUpdated) {
$rows_updated = mysql_affected_rows($conn);
print 'Updated '.$rows_updated.' rows.';
}
Here, the PHP mysql_affected_rows() API function returns the number of rows that have been modified due to the SQL UPDATE statement. In Node.js, we use the same SQL UPDATE statement. Additionally, we use the affectedRows property of the row's object that holds the same value which is returned out of the PHP mysql_affected_rows() API function. Our Node.js converted code snippet will look like the following one:
var sql_prefix = 'desiredDB_';
var sql_cmd =
'UPDATE `'+sql_prefix+'users` SET `user`="mohan" WHERE `user`="surendra"';
conn.query(sql_cmd, function(err, rows, fields) {
if (!err) {
var rows_updated = rows.affectedRows;
console.log('Updated '+rows_updated+' rows.');
}
});
Using the INSERT statement
Now it is time to write the PHP code to insert data into a table, and then convert the code to its equivalent Node.js code. In order to insert data into a table, we use the SQL INSERT statement. In PHP, the SQL INSERT statement is triggered using the PHP mysql_query() API function, as demonstrated in the following PHP code snippet:
$sql_prefix = 'desiredDB_';
$sql_cmd =
'INSERT INTO `'.$sql_prefix.'users` (`id`, `user`) VALUES (0, "surmohan")';
$tabInserted = mysql_query($sql_cmd, $conn);
if ($tabInserted) {
$inserted_id = mysql_insert_id($conn);
print 'Successfully inserted row with id='.$inserted_id.'.';
}
Here, the PHP mysql_insert_id() API function returns the value of id that is associated to the newly inserted data.
In Node.js, we use the same SQL INSERT statement. Additionally, we use the insertId property of the row's object that holds the same value that is returned from the PHP mysql_insert_id() API function. The Node.js code snippet that is equivalent to the preceding PHP code snippet looks like the following one:
var sql_prefix = 'desiredDB_';
var sql_cmd =
'INSERT INTO `'+sql_prefix+'users` (`id`, `user`) VALUES (0, "surmohan")';
conn.query(sql_cmd, function(err, rows, fields) {
if (!err) {
var inserted_id = rows.insertId;
console.log(''Successfully inserted row with id='+inserted_id+'.');
}
});
Using the DELETE statement
Finally, we have reached our last activity of this section, that is, use of the SQL DELETE statement.
Like the SQL statements we discussed earlier in this section, the SQL DELETE statement is also triggered using the PHP mysql_query() API function as demonstrated in the following PHP code snippet:
$sql_prefix = 'desiredDB_';
$sql_cmd = 'DELETE FROM `'.$sql_prefix.'users` WHERE `user`="surmohan"';
$tabDeleted = mysql_query($sql_cmd, $conn);
if ($tabDeleted) {
$rows_deleted = mysql_affected_rows($conn);
print 'Successfully deleted '.$rows_deleted.' rows.';
}
In Node.js, we use the same SQL DELETE statement. We also use the affectedRows property that serves us in the same way as discussed while dealing with the SQL UPDATE statement. The equivalent Node.js code snippet will look like the following one:
var sql_prefix = 'desiredDB_';
var sql_cmd = 'DELETE FROM `'+sql_prefix+'users` WHERE `user`="surmohan"';
conn.query(sql_cmd, function(err, rows, fields) {
if (!err) {
var rows_deleted = rows.affectedRows;
console.log('Successfully deleted '+rows_deleted +' rows.');
}
});
Read more
United States
Great Britain
India
Germany
France
Canada
Russia
Spain
Brazil
Australia
Singapore
Canary Islands
Hungary
Ukraine
Luxembourg
Estonia
Lithuania
South Korea
Turkey
Switzerland
Colombia
Taiwan
Chile
Norway
Ecuador
Indonesia
New Zealand
Cyprus
Denmark
Finland
Poland
Malta
Czechia
Austria
Sweden
Italy
Egypt
Belgium
Portugal
Slovenia
Ireland
Romania
Greece
Argentina
Netherlands
Bulgaria
Latvia
South Africa
Malaysia
Japan
Slovakia
Philippines
Mexico
Thailand
