Home arrow MySQL arrow Page 4 - Implementing the commit() and rollback() Methods with mysqli and PHP 5

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

If you want to use the "mysqli" extension, you've come to the right place. This is the second part of the series “Using mysqli with PHP 5.” Welcome back. In three tutorials, this series shows how to use the most important methods and properties included in the “mysqli” extension that comes with PHP 5, in order to take advantage of the new features bundled with MySQL 4.1 and above.

TABLE OF CONTENTS:
  1. Implementing the commit() and rollback() Methods with mysqli and PHP 5
  2. Working with “InnoDB” tables: using the “commit()” and “autocommit()” methods
  3. Canceling database modifications: using the “rollback()” method
  4. Escaping strings, counting rows and more: using the “real_escape_string()” method and the “affected_rows” property
By: Alejandro Gervasio
Rating: starstarstarstarstar / 20
July 03, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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!



 
 
>>> More MySQL Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: