Access Result Set from Stored Procedures in MySQL

Posted in: My Diary |

This post is also available in: Indonesian

sp_mysql_thumb

This post is also available in: Indonesian

From cases put forward by my students, the result-set given fromMySQL’s stored procedures is not accessible in PHP so that they using the functions instead of stored procedures, well actually we can access those result sets. There are two ways to access the result-set returned by the stored procedure, using a MySQL extension but specify client_flag to support multiple statements and multiple result or using the MySQLi extension.

Case used below using database from WordPress.

Stored Procedure in used as follow:

CREATE PROCEDURE `GetPost`(IN limitCount INTEGER(11))
BEGIN
   set @limitCount=limitCount;
   set @sql="select post_title, display_name from wp_posts p
join wp_users u on p.post_author=u.id
where p.post_type='post' and post_status='publish'
order by post_date desc"; 

   if (not isnull(limitCount)) then
      set @sql=concat(@sql, " limit 0,?");
   end if;

   prepare stmt from @sql; 

   if (isnull(limitCount)) then
      execute stmt;
   else
      execute stmt using @limitCount;
   end if;

   deallocate prepare stmt;
END;
  1. Using the MySQL extension. MySQL extension for PHP is an extension for MySQL 4 though still can be used for MySQL 5 but with some limitations. MySQL extension for PHP is available only to procedural not object-oriented. To access the stored procedures it is necessary to set client_flags to support multi statement (value = 65.536) and multiple results (value = 131.072) when connecting to the database.
    Example as follow (using procedural):

       $sql = "call GetPost(5)"; //query to call stored procedure
    
       // connect to mysql, to access stored procedure
       // set client flags to support multi statement and multi result
       // 196608 = 65536 + 131072
       $mysql = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD, false, 196608);
       if (!$mysql){
          printf("connect failed: %s\n", mysql_error());
       }
       else
       {
          mysql_select_db(DB_NAME,$mysql);
          $result = mysql_query($sql);
          if ($result) {
             while ($row = mysql_fetch_object($result)) {
                printf("%s : %s ", $row->display_name, $row->post_title);
             }
             mysql_free_result($result); //free result set
          }
       }
       mysql_close($mysql);
  2. Using MySQLi Extension extension. MySQLi is an extension to access the functionality of MySQL 4.1 and above. This extension is available for both procedural and object-oriented. To access the stored procedure it is “not necessary” to set the client flags. Example as follow (using object-oriented).
       $sql = "call GetPost(5)";  //query to call stored procedure
    
       $mysqli = new mysqli(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME); //connect mysqli
       if (mysqli_connect_errno()){
          printf("connect failed: %s\n", mysqli_connect_error
       }
       else
       {
          if ($result = $mysqli->query($sql)) {
                while ($row = $result->fetch_object()) {
                      printf("%s : %s ", $row->display_name, $row->post_title);
                }
                $result->close(); //free result set
          }
       }
       $mysqli->close();

Screenshot:

Contoh tampilan hasil stored procedure MySQL lewat PHP

Example: screenshot of stored procedure result of MySQL using PHP




Speak Up!

Leave your own comment

Notify me of follow-up comments via e-mail (or subscribe here).




 

Share

Subscribe Feed

Email

Facebook

Twitter

Delicious

Digg

StumbleUpon

Google Buzz

Deviantart