MySQL
  Home arrow MySQL arrow Page 4 - Implementing the commit() and rollback...
Dev Shed Forums 
Administration  
AJAX  
Apache  
BrainDump  
DHTML  
Flash  
Java  
JavaScript  
Multimedia  
MySQL  
Oracle  
Perl  
PHP  
Practices  
Python  
Reviews  
Security  
Style-Sheets  
Web Services  
XML  
Zend  
Zope  
Forums Sitemap 
IBM® developerWorks 
Sun Developer Network 
Dedicated Servers 
E-Commerce Hosting 
Linux Web Hosting 
Managed Hosting 
Small Business Hosting 
Actuate Whitepapers 
VeriSign Whitepapers 
VPS Hosting 
Weekly Newsletter

 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
MYSQL

Implementing the commit() and rollback() Methods with mysqli and PHP 5
By: Alejandro Gervasio
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 13
    2006-07-03

    Table of Contents:
  • Implementing the commit() and rollback() Methods with mysqli and PHP 5
  • Working with “InnoDB” tables: using the “commit()” and “autocommit()” methods
  • Canceling database modifications: using the “rollback()” method
  • Escaping strings, counting rows and more: using the “real_escape_string()” method and the “affected_rows” property

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT

    Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here

    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


    (Page 4 of 4 )

    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!


    DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

       · In this second installment of the series, the use of the "commit()" and rollback()...
       · what about MyISAM type databases, can same methods work with MyISAM DBs. If not,...
       · Thank you for commenting on my article. With regard to your question, you have some...
       · Thank you for this tutorial. As I was implementing transactions into my project I...
       · I made a mistake. I was using mysqli->multi_query(), this prevented the transaction...
       · Hello again Rafael,Thank you for posting your comments here, and also I'm glad...
     

       

    MYSQL ARTICLES

    - MySQL Table Prefix Changer Tool in PHP
    - Using the SIGNAL Statement for Error Handling
    - Error Handling Examples
    - Error Handling
    - Completing a Search Engine with MySQL and PH...
    - Paginating Result Sets for a Search Engine B...
    - Building a Search Engine with MySQL and PHP 5
    - Using Boolean Operators for Full Text and Bo...
    - PHP, MySQL and the PEAR Database
    - Working with PHP and MySQL
    - Getting PHP to Talk to MySQL
    - Creating an RSS Reader: the Reader
    - MySQL Security Overview
    - Creating the Admin Script for a PHP/MySQL Bl...
    - Creating the Blog Script for a PHP/MySQL Blo...





    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 5 hosted by Hostway