Warning: Use of undefined constant TWITTER_CONSUMER_KEY - assumed 'TWITTER_CONSUMER_KEY' (this will throw an Error in a future version of PHP) in /home/hikaruyu/public_html/wp-content/themes/hikaruyuuki/functions.php on line 9

Warning: Use of undefined constant TWITTER_CONSUMER_SECRET - assumed 'TWITTER_CONSUMER_SECRET' (this will throw an Error in a future version of PHP) in /home/hikaruyu/public_html/wp-content/themes/hikaruyuuki/functions.php on line 10

Warning: Use of undefined constant TWITTER_ACCESS_TOKEN - assumed 'TWITTER_ACCESS_TOKEN' (this will throw an Error in a future version of PHP) in /home/hikaruyu/public_html/wp-content/themes/hikaruyuuki/functions.php on line 11

Warning: Use of undefined constant TWITTER_ACCESS_TOKEN_SECRET - assumed 'TWITTER_ACCESS_TOKEN_SECRET' (this will throw an Error in a future version of PHP) in /home/hikaruyu/public_html/wp-content/themes/hikaruyuuki/functions.php on line 12

Warning: Use of undefined constant USE_COLLAPSIBLE - assumed 'USE_COLLAPSIBLE' (this will throw an Error in a future version of PHP) in /home/hikaruyu/public_html/wp-content/themes/hikaruyuuki/functions.php on line 14

Warning: Use of undefined constant USE_MULTILINGUAL - assumed 'USE_MULTILINGUAL' (this will throw an Error in a future version of PHP) in /home/hikaruyu/public_html/wp-content/themes/hikaruyuuki/functions.php on line 15

Warning: Use of undefined constant THEME_BODY_WIDTH - assumed 'THEME_BODY_WIDTH' (this will throw an Error in a future version of PHP) in /home/hikaruyu/public_html/wp-content/themes/hikaruyuuki/functions.php on line 16

Warning: Use of undefined constant THEME_COMMENT_THREADED - assumed 'THEME_COMMENT_THREADED' (this will throw an Error in a future version of PHP) in /home/hikaruyu/public_html/wp-content/themes/hikaruyuuki/functions.php on line 17

Warning: Use of undefined constant EDITOR_INLINE_STYLES - assumed 'EDITOR_INLINE_STYLES' (this will throw an Error in a future version of PHP) in /home/hikaruyu/public_html/wp-content/themes/hikaruyuuki/functions.php on line 18

Warning: Use of undefined constant SUPPORT_MENU - assumed 'SUPPORT_MENU' (this will throw an Error in a future version of PHP) in /home/hikaruyu/public_html/wp-content/themes/hikaruyuuki/functions.php on line 19

Warning: Use of undefined constant SUPPORT_THUMBNAIL - assumed 'SUPPORT_THUMBNAIL' (this will throw an Error in a future version of PHP) in /home/hikaruyu/public_html/wp-content/themes/hikaruyuuki/functions.php on line 20

Warning: Use of undefined constant THUMBNAIL_WIDTH - assumed 'THUMBNAIL_WIDTH' (this will throw an Error in a future version of PHP) in /home/hikaruyu/public_html/wp-content/themes/hikaruyuuki/functions.php on line 21

Warning: Use of undefined constant THUMBNAIL_HEIGHT - assumed 'THUMBNAIL_HEIGHT' (this will throw an Error in a future version of PHP) in /home/hikaruyu/public_html/wp-content/themes/hikaruyuuki/functions.php on line 22

Warning: Use of undefined constant DISABLE_PINGTRACKBACK - assumed 'DISABLE_PINGTRACKBACK' (this will throw an Error in a future version of PHP) in /home/hikaruyu/public_html/wp-content/themes/hikaruyuuki/functions.php on line 24
Akses Result Set dari Stored Procedure MySQL Lewat PHP | Hikaru {Aka} Yuuki のブログ
Warning: Use of undefined constant php - assumed 'php' (this will throw an Error in a future version of PHP) in /home/hikaruyu/public_html/wp-content/themes/hikaruyuuki/header.php on line 9

Warning: Use of undefined constant php - assumed 'php' (this will throw an Error in a future version of PHP) in /home/hikaruyu/public_html/wp-content/themes/hikaruyuuki/header.php on line 25
 


Akses Result Set dari Stored Procedure MySQL Lewat PHP

Posted in: Basis Data |

This post is also available in: English

sp_mysql_thumb

This post is also available in: English

Dari kasus yang dikemukakan oleh mahasiswa saya, result-set yang diberikan stored procedure dari MySQL tidak dapat diakses dari PHP sehingga menggunakan Function, well sebetulnya bisa saja. Ada dua cara mengakses result-set yang dikembalikan oleh stored procedure, menggunakan MySQL extension dengan client_flag untuk dukungan multi statement dan multi result atau menggunakan MySQLi extension.

Kasus yang digunakan di bawah menggunakan database dari WordPress.

Stored Procedure yang digunakan:

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. Menggunakan MySQL extensionMySQL Extension pada PHP merupakan extension untuk MySQL 4 walaupun masih dapat digunakan untuk MySQL 5 namun ada keterbatasan. MySQL extension untuk PHP tersedia hanya untuk procedural tidak untuk object-oriented. Untuk mengakses stored procedure diperlukan mengeset client_flags untuk multi statement (nilai=65536) dan multi results (nilai=131072)pada saat koneksi database.Contohnya adalah sebagai berikut (prosedural).
       $sql = "call GetPost(5)"; //query untuk panggil stored procedure
    
       // koneksi mysql, untuk mengakses stored procedure
       // client flags diset untuk multi statement dan 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. Menggunakan MySQLi extensionMySQLi Extension merupakan extension untuk mengakses fungsionalitas MySQL 4.1 ke atas. Extension ini tersedia baik untuk procedural maupun object-oriented. Untuk mengakses stored procedure “tidak diperlukan” untuk mengeset client flags.Contohnya adalah sebagai berikut (object-oriented).
       $sql = "call GetPost(5)";  //query untuk panggil stored procedure
    
       $mysqli = new mysqli(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME); //koneksi 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();

Contoh screenshotnya:

Contoh tampilan hasil stored procedure MySQL lewat PHP

Contoh tampilan hasil stored procedure MySQL lewat PHP




Speak Up!

Leave your own comment

KESALAHAN: plugin si-captcha.php mengatakan bahwa dukungan GD image tidak terdeteksi di PHP!

Hubungi hosting situs anda dan tanyakan mereka mengapa dukungan GD image tidak diaktifkan untuk PHP.

KESALAHAN: plugin si-captcha.php mengatakan bahwa fungsi imagepng tidak terdeteksi di PHP!

Hubungai hosting situs Anda dan tanyakan mereka mengapa fungsi imagepng tidak diaktifkan di PHP.

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




 

Share

Subscribe Feed

Email

Facebook

Twitter

Delicious

Digg

StumbleUpon

Google Buzz

Deviantart



Warning: Use of undefined constant scripts - assumed 'scripts' (this will throw an Error in a future version of PHP) in /home/hikaruyu/public_html/wp-content/themes/hikaruyuuki/library/functions/scripts.php on line 50