Stored procedure in mysql and php

No comments
Hello guys, Are you using stored procedure in your PHP application? If not, then you should read this article. So first of all, we understand what stored procedure is and why we should use. In database management system, a stored procedure is group of structured query language (SQL) statement complied into a single execution plan. By using stored procedure it can help you in controlling access to data (end user can change/remove/edit data but cannot write procedures), preserving data integrity and improving productivity.

First we create a table with name ‘tbl_users’.


CREATE TABLE tbl_users (user_ id INT PRIMARY KEY AUTO_INCREMENT,User_name VARCHAR(255), );

And it’s time to write procedure. Go to your database and click ‘SQL’ tab on top bar. Now write the below code in opened window.
CREATE PROCEDURE get_users() SELECT user_id,user_name FROM tbl_users;

Or you can create the same in 'Routines' tab as below. This is great tool for beginners.

  • Go to your database and click on ‘Routines’ tab in header section.
  • Now click on ‘Add routine’.
Then a popup window will be open. In that window, write your procedure and click on go button as below.
That’s it. Now you can see your created procedures under ‘Routines’ Tab.
Now its time to call the above created procedure in your php script. Here, i am using PDO for connecting to database.
Create index.php page and put below code into it.


<?php $db = new PDO('mysql:dbname=test;host=localhost', 'root', ''); 
$sql = 'CALL userdetail()';
$q = $db->query($sql);
$q->setFetchMode(PDO::FETCH_ASSOC);

?>
<table width="445" border="1">
  <tr>
    <td width="188"><strong>User ID</strong></td>
    <td width="241"><strong>User Name</strong></td>
  </tr>
  <?php while ($r = $q->fetch()){?>
  <tr>
    <td><?php echo $r['user_id'];?></td>
    <td><?php echo $r['user_name'];?></td>
  </tr>
  <?php }?>
</table>

Now run the index.php file. If everything is ok then you will be able to see the output like:
Download complete source code and database file

Please share this tutorials to your friends, also let me know if you are facing any problem while creating stored procedure in comment box below. I would love to help you. 

No comments :

Post a Comment