Stored Procedures, MySQL and PHP

How to call a MySQL stored procedure from within PHP code?

I now found solution by using mysqli instead of mysql.

<?php 

// enable error reporting
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
//connect to database
$connection = mysqli_connect("hostname", "user", "password", "db", "port");

//run the store proc
$result = mysqli_query($connection, "CALL StoreProcName");

//loop the result set
while ($row = mysqli_fetch_array($result)){     
  echo $row[0] . " - " . + $row[1]; 
}

I found that many people seem to have a problem with using mysql_connect, mysql_query and mysql_fetch_array.

Stored Procedures, MySQL and PHP

Forget about mysqli, it's much harder to use than PDO and should have been already removed. It is true that it introduced huge improvements over mysql, but to achieve the same effect in mysqli sometimes requires enormous effort over PDO i.e. associative fetchAll.

Instead, take a look at PDO, specifically
prepared statements and stored procedures.

$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 

// call the stored procedure
$stmt->execute();

print "procedure returned $value\n";

Using Stored Procedures in php

u should have to name attribute for submit button..i hope this will help.

 <input type="submit" name='submit' value="submit">

PHP Call mysql stored procedure with in parameter

Edited: After seeing more code, you have attempted to mix the mysql_() functions with PDO. You cannot do that -- instead, use PDO only. The two APIs do not work together, and the old mysql_*() API does not support prepared statements at all.

You have not connected to your database or instantiated a PDO object.

$username_v = $_POST['username'];
$password_v = $_POST['password'];
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';

// You must first connect to the database by instantiating a PDO object
try {
    $dbh = new PDO($dsn, 'root', 'root_db_pw');
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

// Then you can prepare a statement and execute it.    
$stmt = $dbh->prepare("CALL login(?, ?)");
// One bindParam() call per parameter
$stmt->bindParam(1, $username_v, PDO::PARAM_STR); 
$stmt->bindParam(2, $password_v, PDO::PARAM_STR); 

// call the stored procedure
$stmt->execute();

What are the benefits of creating Stored Procedures in SQL and MySQL?

Some benefits include:

• Maintainability: you can change the logic in the procedure without needing to edit app1, app2 and app3 calls.

• Security/Access Control: it's easier to worry about who can call a predefined procedure than it is to control who can access which tables or which table rows.

• Performance: if your app is not situated on the same server as your DB, and what you're doing involves multiple queries, using a procedure reduces the network overhead by involving a single call to the database, rather than as many calls as there are queries.

• Performance (2): a procedure's query plan is typically cached, allowing you to reuse it again and again without needing to re-prepare it.

(In the case of your particular example, the benefits are admittedly nil.)

Return MySQL stored procedure result set using php

It doesn't seem very elegant, but I've come up with the following, which satisfies me enough to move on:

<?php
//imports the connection
require "conn.php";

//builds the MySQL stored procedure all
$name = "chrisf";
$input = "CALL getEngineersJob('".$name."')";

//executes the store procedure
$result = mysqli_query($conn,$input);

//loop through the output and echo
while ($row = mysqli_fetch_array($result)){   
   echo $row[0] . "<br>"; 
}

MySQL stored procedures in PHP with examples

You can create procedure very easily in MySQL --
Syntax :-

`//add a delimiter; 
Create Procedure <procedure_name>(parameters "IN(for using inside the procedure)" and "OUT(for returning)")
Begin
   // doing some thing.
end;
//end delimiter;`

Call Procedure - `CALL <procedure_name>(parameter1, parameter2, ....)`

Example -

`DELIMITER //  
CREATE PROCEDURE EmployeeName(IN EId INT)  
BEGIN  
    SELECT EmpName FROM Employee WHERE EmpId = EID;  
END//`

Calling- assume i want to know the name of employee id "10", so --

CALL EmployeeName(10);

Create Stored Procedures with PDO in PHP

Well, PMA Helped me with answering this Question of my own.

To overcome this you need to remove the delimiter part of the procedure, so that your queries become like:

 DROP PROCEDURE IF EXISTS `add_hits`;
 CREATE DEFINER=`root`@`localhost` PROCEDURE `add_hits`( In id varchar(255))
 BEGIN
 declare hits_bk int;
 select hits into hits_bk from db_books where Book_ID = id;
 update db_books set hits=hits_bk+1 where Book_ID = id;
 END;

Now the queries will work.

Thanks to @Your Common Sense and @RiggsFolly for helping out.

---

Related Topics