Php call stored function

PHP MySQL: Call MySQL Stored Procedures

Summary: in this tutorial, you will learn how to call MySQL stored procedures using PHP PDO. We will show you how to call stored procedures that return a result set and stored procedures that accept input/output parameters.

Calling stored procedures that return a result set

The steps of calling a MySQL stored procedure that returns a result set using PHP PDO are similar to querying data from MySQL database table using the SELECT statement. Instead of sending a SELECT statement to MySQL database, you send a stored procedure call statement.

First, create a stored procedure named GetCustomers() in the sample database for the demonstration. The GetCustomers() stored procedure retrieves the name and credit limit of customers from the customers table.

The following GetCustomers() stored procedure illustrates the logic:

DELIMITER $$ CREATE PROCEDURE GetCustomers() BEGIN SELECT customerName, creditlimit FROM customers; END$$Code language: SQL (Structured Query Language) (sql)

Second, create a new PHP file named phpmysqlstoredprocedure1.php with the following code:

html> html> head> title>PHP MySQL Stored Procedure Demo 1 title> link rel="stylesheet" href="css/table.css" type="text/css" /> head> body>  require_once 'dbconfig.php'; try < $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password); // execute the stored procedure $sql = 'CALL GetCustomers()'; // call the stored procedure $q = $pdo->query($sql); $q->setFetchMode(PDO::FETCH_ASSOC); > catch (PDOException $e) < die("Error occurred:" . $e->getMessage()); > ?> table> tr> th>Customer Name th> th>Credit Limit th> tr>  while ($r = $q->fetch()): ?> tr> td> echo $r['customerName'] ?> td> td> echo '$' . number_format($r['creditlimit'], 2) ?> td> tr>  endwhile; ?> table> body> html>Code language: HTML, XML (xml)

Everything is straightforward except the SQL query:

CALL GetCustomers();Code language: SQL (Structured Query Language) (sql)

We send the statement that calls the GetCustomers() stored procedure to MySQL. And we execute the statement to get a result set.

Third, test the script in the web browser to see how it works.

You can download the script via the following link:

Calling stored procedures with an OUT parameter

It is tricky to call a stored procedure with the OUT parameter. We will use the GetCustomerLevel() stored procedure that accepts a customer number as an input parameter and returns the customer level based on the credit limit.

Check the MySQL IF statement tutorial for detailed information on the GetCustomerLevel() stored procedure.

DELIMITER $$ CREATE PROCEDURE GetCustomerLevel( in p_customerNumber int(11), out p_customerLevel varchar(10)) BEGIN DECLARE creditlim double; SELECT creditlimit INTO creditlim FROM customers WHERE customerNumber = p_customerNumber; IF creditlim > 50000 THEN SET p_customerLevel = 'PLATINUM'; ELSEIF (creditlim = 10000) THEN SET p_customerLevel = 'GOLD'; ELSEIF creditlim < 10000 THEN SET p_customerLevel = 'SILVER'; END IF; END$$Code language: SQL (Structured Query Language) (sql)

In MySQL, we can call the GetCustomerLevel() stored procedure as follows:

CALL GetCustomerLevel(103,@level); SELECT @level AS level;Code language: SQL (Structured Query Language) (sql)

In PHP, we have to emulate those statements:

• First, we need to execute the GetCustomerLevel() stored procedure.
• Second, to get the customer level, we need to query it from the variable @level . It is important that we must call the method closeCursor() of the PDOStatement object in order to execute the next SQL statement.

Let’s take a look at how to implement the above logic in the following PHP script:

 require_once 'dbconfig.php'; /** * Get customer level * @param int $customerNumber * @return string */ function getCustomerLevel(int $customerNumber) < try < $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password); // calling stored procedure command $sql = 'CALL GetCustomerLevel(:id,@level)'; // prepare for execution of the stored procedure $stmt = $pdo->prepare($sql); // pass value to the command $stmt->bindParam(':id', $customerNumber, PDO::PARAM_INT); // execute the stored procedure $stmt->execute(); $stmt->closeCursor(); // execute the second query to get customer's level $row = $pdo->query("SELECT @level AS level")->fetch(PDO::FETCH_ASSOC); if ($row) < return $row !== false ? $row['level'] : null; > > catch (PDOException $e) < die("Error occurred:" . $e->getMessage()); > return null; > $customerNo = 103; echo sprintf('Customer #%d is %s', $customerNo, getCustomerLevel($customerNo));Code language: PHP (php)

If you test the script in the web browser, you will see the following screenshot:

You can download the script via the following link:

In this tutorial, you have learned how to call MySQL stored procedures using PHP PDO.

Источник

Call a MySQL Stored Procedure Using PHP PDO

Summary: in this tutorial, you will learn how to call a MySQL stored procedure using the PHP PDO.

Setting up a stored procedure in MySQL

To execute a statement in the MySQL database, you can use any MySQL client tool e.g., mysql client tool or MySQL Workbench.

First, insert data into the authors table by executing the following INSERT statement:

INSERT INTO books(title, isbn, published_date,publisher_id) VALUES ('Goodbye to All That','9781541619883','2013-01-05', 3), ('The Mercies','9780316529235','2020-01-28', 3), ('On the Farm','9780763655914','2012-03-27', 2), ('Joseph Had a Little Overcoat','9780140563580','1977-03-15', 2);Code language: SQL (Structured Query Language) (sql)

Note that the publishers table should have rows with id 2 and 3. If it doesn’t, you can run the script that inserts rows into the publishers table.

Second, execute the following CREATE PROCEDURE statement to create a new stored procedure called get_books_published_after :

USE `bookdb`; DELIMITER $$ USE `bookdb`$$ CREATE PROCEDURE `get_books_published_after` (IN published_year INT) BEGIN SELECT book_id, title, isbn, published_date, name as publisher FROM books b INNER JOIN publishers p ON p.publisher_id = b.publisher_id WHERE year(published_date) > published_year; END$$ DELIMITER ;Code language: SQL (Structured Query Language) (sql)

The stored procedure get_books_published_after returns all books published after a specific year.

Third, execute the stored procedure to check the result set:

CALL get_books_published_after(2010);Code language: SQL (Structured Query Language) (sql)

The statement returns the following result set:

+---------+---------------------+---------------+----------------+----------------------+ | book_id | title | isbn | published_date | publisher | +---------+---------------------+---------------+----------------+----------------------+ | 1 | Goodbye to All That | 9781541619883 | 2013-01-05 | Hachette Book Group | | 2 | The Mercies | 9780316529235 | 2020-01-28 | Hachette Book Group | | 3 | On the Farm | 9780763655914 | 2012-03-27 | Penguin/Random House | +---------+---------------------+---------------+----------------+----------------------+ 3 rows in set (0.005 sec)Code language: plaintext (plaintext)

Calling a MySQL stored procedure from PHP using PDO

The following script illustrates how to call the get_books_published_after stored procedure:

 $published_year = 2010; // connect to the database and select the publisher $pdo = require 'connect.php'; $sql = 'CALL get_books_published_after(:published_year)'; $publishers = []; $statement = $pdo->prepare($sql); $statement->bindParam(':published_year', $published_year, PDO::PARAM_INT); $statement->execute(); $publishers = $statement->fetchAll(PDO::FETCH_ASSOC); print_r($publishers); Code language: HTML, XML (xml)

$pdo = require 'connect.php';Code language: PHP (php)

Second, construct a SQL statement that calls the get_books_published_after stored procedure:

$sql = 'CALL get_books_published_after(:published_year)';Code language: PHP (php)

The statement accepts a named placeholder :published_year so that you can bind a value to it later.

Third, create a prepared statement by calling the prepare() method of the PDO instance:

$statement = $pdo->prepare($sql);Code language: PHP (php)

Fourth, bind a value to the statement:

$statement->bindParam(':published_year', $published_year, PDO::PARAM_INT);Code language: PHP (php)

Fifth, execute the stored procedure call:

$statement->execute();Code language: PHP (php)

Since the stored procedure returns a result set, you can fetch each row in the result set into an associative array using the fetchAll() method:

$publishers = $statement->fetchAll(PDO::FETCH_ASSOC);Code language: PHP (php)

Summary

Источник

How can I call and execute a function stored in the database?

I have function getArray(pram1, pram2) stored in database (MySQL) and it returns data in an array. When I pass the query, it returns the function name as I stored it in the database, but does not execute this function. The code, which I’m using, is this:

$result=eval("return \$ret = $db_query_function;"); $db_query_function is variable that have function name getArray(pram1, pram2). 

2 Answers 2

You’ll have to use call_user_func().

call_user_func('function_name', $parameter); 

Firstly, it’s probably not the best solution to store functions in the database.

If you want to store function implementations in the database, you’ll have to store only the function body and parameter list separately, (but no name should be given to the function) so you can use create_function to put your function into a variable at runtime.

should end up in variables similar to:

$arguments = '$a, $b'; // note single quotes, no variable expansion! $implementation = 'return $a + $b;'; // single quotes again $myFunction = create_function($arguments, $implementation); 

now you can call your function:

$sum = $myFunction(10, 20); // $sum will now hold 30 

If you just want your code stored in the database to be run, all you need is eval.

In both cases, you’re most likely (but not definitely) missing a better/easier way to deal with yout code. In your sample, the most likely error is that pram1 and pram2 require a dollar sign in the string, but the code sample is incomplete enough to be sure. could also be that the function doesn’t exist or you’re missing a semicolon, or.

$result = eval('return '.$db_query_function); getArray(pram1, pram2). 

Источник

Php call stored function

// Store procedure call without params

$MyConnection = new mysqli ( «DB_SERVER» , «DB_USER» , «DB_PASS» , «DB_NAME» );

mysqli_multi_query ( $MyConnection , «CALL MyStoreProcedure» ) OR DIE ( mysqli_error ( $MyConnection ));

while ( mysqli_more_results ( $MyConnection ))

if ( $result = mysqli_store_result ( $MyConnection ))

while ( $row = mysqli_fetch_assoc ( $result ))

// i.e.: DBTableFieldName=»userID»
echo «row keyword»>. $row [ «DBTableFieldName» ]. «
» ;
.

>
mysqli_free_result ( $result );
>
mysqli_next_result ( $conn );

// Store procedure call using params

$MyConnection = new mysqli ( «DB_SERVER» , «DB_USER» , «DB_PASS» , «DB_NAME» );

mysqli_query ( $MyConnection , «SET @p0='» . $MyParam1 . «‘» );
mysqli_query ( $MyConnection , «SET @p1='» . $MyParam2 . «‘» );
mysqli_multi_query ( $MyConnection , «CALL MyStoreProcedure (@p0,@p1)» ) OR DIE ( mysqli_error ( $MyConnection ));

while ( mysqli_more_results ( $MyConnection ))

if ( $result = mysqli_store_result ( $MyConnection ))

while ( $row = mysqli_fetch_assoc ( $result ))

// i.e.: DBTableFieldName=»userID»
echo «row keyword»>. $row [ «DBTableFieldName» ]. «
» ;
.

>
mysqli_free_result ( $result );
>
mysqli_next_result ( $conn );

/**
* Small function to facilitate call procedure with multiple arguments (supports in/inout/out)
*/
$db = new mysqli ( ‘localhost’ , ‘root’ , ‘password’ , ‘database’ );

$lt_query = callProcedure (
$db ,
«stored_procedure» ,
array(
«in_param1» => «Value1» ,
«in_param2» => «Value2» ,
«inout_param3» => «Value3» ,
«out_param4» => «» ,
«out_param5» => «»
));

function callProcedure ( $po_db , $pv_proc , $pt_args )
if (empty( $pv_proc ) || empty( $pt_args ))
return false ;
>
$lv_call = «CALL ` $pv_proc `(» ;
$lv_select = «SELECT» ;
$lv_log = «» ;
foreach( $pt_args as $lv_key => $lv_value )
$lv_query = «SET @_ $lv_key = ‘ $lv_value ‘» ;
$lv_log .= $lv_query . «;\n» ;
if (! $lv_result = $po_db -> query ( $lv_query ))
/* Write log */
return false ;
>
$lv_call .= » @_ $lv_key ,» ;
$lv_select .= » @_ $lv_key AS $lv_key ,» ;
>
$lv_call = substr ( $lv_call , 0 , — 1 ). «)» ;
$lv_select = substr ( $lv_select , 0 , — 1 );
$lv_log .= $lv_call ;
if ( $lv_result = $po_db -> query ( $lv_call ))
if( $lo_result = $po_db -> query ( $lv_select ))
$lt_result = $lo_result -> fetch_assoc ();
$lo_result -> free ();
return $lt_result ;
>
/* Write log */
return false ;
>
/* Write log */
return false ;
>

/**
* This will return an array like this:
*
* $lt_query = array(
* ‘in_param1’ = ‘Value1’, // Same value as in call
* ‘in_param2’ = ‘Value2’, // Same value as in call
* ‘inout_param3’ = ?, // Value is changed accordingly
* ‘out_param4’ = ?, // Value is changed accordingly
* ‘out_param5’ = ? // Value is changed accordingly
* )
*/
?>

Источник