Calling MySQL procedure from PHP

Question

As an example, suppose I want to execute the following query:

SELECT * FROM posts;

Therefore, I write the following:

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_posts`(IN `zip` INT)
  LANGUAGE SQL
  NOT DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY DEFINER
COMMENT ''
BEGIN
  SELECT * FROM posts WHERE posts.zip = zip;
END

Is the following change the only one I have to make:

mysql_query("SELECT * FROM posts");
// to
mysql_query("CALL get_posts");

...and then I can fetch rows, etc.?

Answer 1

you also need to supply the parameter

mysql_query("CALL get_posts(11)");

another suggestion is by using PDO extension on this.

Example of using PDO,

<?php
    $zipCode = 123;
    $stmt = $dbh->prepare("CALL get_posts(?)");
    $stmt->bindParam(1, $zipCode);
    if ($stmt->execute(array($_GET['columnName']))) 
    {
        while ($row = $stmt->fetch()) 
        {
            print_r($row);
        }
    }
?>

this will protect you from SQL Injection.

Answer 2

Your procedure expects an input parameter, so call it with one:

$result = mysql_query("CALL get_posts(12345)");

This will supply a result resource on a successful call, then you can run a fetch loop as you would a normal query.

if ($result) {
  // fetch in a while loop like you would any normal SELECT query...
}