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.?
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.
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...
}
