[ciapug] Re: MySQL 5 Stored Procedures

[Carl Olsen]

For those of you who prefer PHP 4 and MySQL 3, my question was not directed
to you folks.  I'm simply looking for ways to write less code, so telling me
I've got a monster is sort of restating the obvious.  That was the point of
my question.  Thanks.

The example of returning a parameter from a stored procedure with PHP that I
found shows two queries.  The first query calls the stored procedure and the
second one does a SELECT for the parameter.

Some of my code wasn't necessary for the question, so here's the actual part
that uses the stored procedure:

public function insert($name)
{
    $rowsAffected = -1;
    $insertId = -1;
    $sql = "CALL sp_News_CreateNewsArticle(?, at article_id)";
    if ($stmt = $this->conn->prepare($sql))
    {
        $stmt->bind_param("s", $name);
        $stmt->execute();
        $rowsAffected = $stmt->affected_rows;
        $stmt->fetch();
        $stmt2 = $this->conn->prepare("SELECT @article_id");
        $stmt2->execute();
        $stmt2->bind_result($article_id);
        $stmt2->fetch();
        $insertId = $article_id;
        $stmt2->free_result();
        $stmt2->close();
        $stmt->free_result();
        $stmt->close();
    }
    if ($rowsAffected < 1)
    {
        throw new Exception("News Article insert error.");
    }
    return $insertId;
}

Here is the stored procedure:

drop procedure if exists `sp_Create`;

DELIMITER $$;

CREATE PROCEDURE `sp_Create`(IN name VARCHAR(150), OUT article_id INT)
BEGIN
	INSERT INTO article (sname) 
	VALUES (name);
	SET article_id = LAST_INSERT_ID();
END$$

DELIMITER ;$$