No problem I thought, this would be a one-minute fix. But MySQL is tricky, it doesn't support named parameters, so you have to use a question mark and add parameters in the right order. So, not having heard of such thing as parameterized queries, I created my SQL statements the same way in C#, until I read about this practice being "not done". You simply build your own SQL string and make sure it doesn't contain anything harmful. I come from a PHP background, where there is no such thing as parameterized queries. In this tutorial, you have learned how to call MySQL stored procedures using PHP PDO.Today I was looking over a project I'm working on currently, more specifically, at the SQL queries in it. If you test the script in the web browser, you will see the following screenshot:ĭownload PHP MySQL Stored Procedure with the OUT Parameter Source Code Return $row != false ? $row : null Įcho sprintf( 'Customer #%d is %s', $customerNo, getCustomerLevel($customerNo)) Code language: PHP ( php ) $row = $pdo->query( "SELECT AS level")->fetch(PDO::FETCH_ASSOC) execute the second query to get customer's level $stmt->bindParam( ':id', $customerNumber, PDO::PARAM_INT) 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.Ĭheck the MySQL IF statement tutorial for detailed information on the GetCustomerLevel() stored procedure. It is tricky to call a stored procedure with the OUT parameter. You can download the script via the following link:ĭownload PHP MySQL Stored Procedure Source Code Calling stored procedures with an OUT parameter Third, test the script in the web browser to see how it works. And we execute the statement to get a result set. We send the statement that calls the GetCustomers() stored procedure to MySQL. ?> Customer Name Credit Limit fetch()): ?> Code language: HTML, XML ( xml )Įverything is straightforward except the SQL query: CALL GetCustomers() Code language: SQL (Structured Query Language) ( sql ) Second, create a new PHP file named phpmysqlstoredprocedure1.php with the following code : PHP MySQL Stored Procedure Demo 1 query($sql) ĭie( "Error occurred:". The following GetCustomers() stored procedure illustrates the logic: DELIMITER $$ĮND$$ Code language: SQL (Structured Query Language) ( sql ) The GetCustomers() stored procedure retrieves the name and credit limit of customers from the customers table. Instead of sending a SELECT statement to MySQL database, you send a stored procedure call statement.įirst, create a stored procedure named GetCustomers() in the sample database for the demonstration. 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. Calling stored procedures that return a result set We will show you how to call stored procedures that return a result set and stored procedures that accept input/output parameters. Summary : in this tutorial, you will learn how to call MySQL stored procedures using PHP PDO.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |