How to Call a MySQL Stored Procedure from Within PHP Code

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.

Unable to run mysql stored procedure from php code

Update

I'm not sure why you're taking the approach you have. There is no need to assign values to variables first, you can simply call the procedure directly e.g.

$sql = "CALL `upload_image`('$path.$imageName', $_SESSION['uid'])";
$result = mysqli_query($con,$sql);

Regardless, it would be far preferable to use prepared statements to protect yourself from SQL injection. Something like this:

$stmt = $con->prepare("CALL `upload_image(?, ?)");
$stmt->bind_param("si", $path.$imageName, $_SESSION['uid']);
$stmt->execute();
$result = $stmt->get_result();

Additionally, your stored procedure being only one statement means that it would be more optimal to just execute that statement directly:

$stmt = $con->prepare("UPDATE user SET profile_image_path = ? WHERE id = ?;");
$stmt->bind_param("si", $path.$imageName, $_SESSION['uid']);
$stmt->execute();
$result = $stmt->get_result();

Original Answer

Your problem is that you are trying to run three separate queries (each SET counts as a query), which mysqli_query doesn't support. You have two options, you can use mysqli_multi_query:

 $sql="SET @p0='".$path.$imageName."'; SET @p1='".$_SESSION['uid']."'; CALL `upload_image`(@p0, @p1);";
 $result = mysqli_multi_query($con,$sql);

Or you can run them as three separate queries:

 $sql="SET @p0='".$path.$imageName."'";
 $result = mysqli_query($con,$sql);
 $sql="SET @p1='".$_SESSION['uid']."'";
 $result = mysqli_query($con,$sql);
 $sql="CALL `upload_image`(@p0, @p1)";
 $result = mysqli_query($con,$sql);

The latter is probably preferred as mysqli_multi_query makes you more vulnerable to SQL injection.

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();

Calling MySQL Stored Procedure on PHP

It looks like you are mixing and matching mysqli object oriented calls with the deprecated mysql_* functions. I'm not sure you can do that.

You are doing:

$r1 = $mysqli->query($sp)

And then passing that to

mysql_fetch_assoc($r1);

Looping through your results, you need to do:

while ($result = $r1->fetch_array())
{ 
    printf($fmt,$result[0], $result[1], $result[2], $result[3]);
}

get output value from mysql stored-procedure to php?

You should be able to get the result(s) of a stored procedure the same way you would any other query:

$stmt = mysqli_query($conn,"CALL test();");
if ($row = mysqli_fetch_row($stmt)) {
    $value = $row[0];
}

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">

---

Related Topics