Escaping strings, counting rows and more: using the “real_escape_string()” method and the “affected_rows” property - MySQL

Escaping conflictive characters before inserting data into the tables of a database has always been a problematic topic, since most of the time it’s not properly addressed. Luckily, the “mysqli” extension also comes with a useful method for escaping input data. Here, I’m talking about the “real_escape_string()” method, which works in a way closely similar to its “cousin,” that is, the “mysql_escape_string()” function.

Having introduced this method, here is a simple example that shows how to use it. Take a look:

// escaping single quotes
$mysqli=new mysqli('host','user','password','database');
if(mysqli_connect_errno()){
    trigger_error('Error connecting to host. '.$mysqli-
>error,E_USER_ERROR);
}
$customerName="Sandra Zet's Smith";
$customerName=$mysqli->real_escape_string($customerName);
// run query
$mysqli->query("INSERT INTO CUSTOMERS (id,name,email) VALUES
(NULL,'$customerName','customer1@domain.com')");
// close connection
$mysqli->close();

As you can see, the above example is extremely simple, and shows a typical case for escaping single quotes on a given string, before proceeding to insert this data into a sample database table. Nearly identical to the “mysql_escape_string()” function, this one will escape single and double quotes, new lines, NULL characters and semicolons, which makes escaping potentially-conflictive data a no-brainer process.

Now that you know how the “real_escape_string()” works, let’s examine a couple of properties that can be used for counting rows, after performing a specific query. The first property that I’ll explain is “affected_rows,” which comes in handy for counting the number of rows that have been affected after running a SQL query.

With reference to this property, I wrote an example below that demonstrates how to use it. Please examine the corresponding source code:

// using the 'affected_rows' property
$mysqli=new mysqli('host','user','password','database');
if(mysqli_connect_errno()){
    trigger_error('Error connecting to host. '.$mysqli-
>error,E_USER_ERROR);
}
$mysqli->query("SELECT * FROM customers WHERE id>5");
echo 'Number of affected rows: '.$mysqli->affected_rows;

In this case, the previous example doesn’t bear much discussion. What I did basically was use the property in question, in order to count the number of rows returned by a simple SELECT statement. Assuming that the sample “CUSTOMERS” database table originally held only two rows with an ID less than 5, then the result echoed by the prior code snippet would be the following:

Number of affected rows: 2

And since I’m talking about counting rows, there’s also another property that can be used for determining the number of rows contained in a result set. That’s precisely the functionality of the “num_rows” property, which can be utilized as follows:

// using the 'num_rows' property
$mysqli=new mysqli('host','user','password','database');
if(mysqli_connect_errno()){
    trigger_error('Error connecting to host. '.$mysqli-
>error,E_USER_ERROR);
}
if($result=$mysqli->query("SELECT * FROM customers")){
    // display number of rows
    echo 'Query returned the following number of
rows:<br />'.$result->num_rows;
    // close result set
    $result->close();
}
// close connection
$mysqli->close();

All right, the above example uses the “num_rows” property to determine the number of rows returned by the corresponding “SELECT” statement. However, it should be noticed that there’s a difference between the “affected_rows” property that you learned before and this one: the “affected_rows” property belongs to the “mysqli” class, while the current one is only a property of dynamically-generated result set objects. Thus, whenever you need to use one or both properties, be careful spotting the difference.

Finally, and returning to the above example, say you have a dozen records stored in the “CUSTOMERS” database table. The output echoed to the browser would be the following:

Query returned the following number of rows: 12

Although these examples might look rather trivial at first glance, I purposely kept all the source code simple, since I want you to learn properly how the methods and properties that I covered in this tutorial fit into the whole picture. If you have already grasped the concepts for putting the “mysqli” extension to work for you, then I must say my journey has almost finished.

Wrapping up

Over this second part of the series, I explained several methods and properties bundled with the “mysqli” library, to show you how to get the most out of them. During this tutorial, you hopefully learned how to handle the “COMMIT” and “ROLLBACK” features of MySQL 4.1 and above, as well as counting rows in result sets.

However, the series hasn’t ended yet. In the last article, I’ll be covering some additional methods, useful for seeking data within result sets, finding insertion IDs and much more. See you in the last part!