CODEWITHSUNDEEP
phpmysqlstored-procedures
Cesarg2199
Cesarg2199

Reputation: 579

Using Stored Procedures in php

Im using mysql stored procedures in my databases and now I want to be able to use it with my php code but none of the examples I've searched show a clear way to do this. Can someone tell me why this isn't working? 

<html>
<head>
<title>Add</title>
<link rel="stylesheet" type="text/css" href="main.css">
<?php include("navbar.php")?>
</head>

<body>
<?php
if(isset($_POST['submit'])) {
    $link = mysqli_connect("localhost", "root", "", "test");

    $firstname = $_POST['firstname'];
    $lastname = $_POST['lastname'];

    mysqli_query($link, "CALL AddUser($firstname, $lastname)");
}
?>
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
    First Name:<input type="text" name="firstname" required>
    Last Name:<input type="text" name="lastname" required>
    <input type="submit" value="submit">
</form>

</body>
</html>

Here is the stored procedure 

CREATE DEFINER=`root`@`localhost` PROCEDURE `AddUser`(IN in_first_name VARCHAR(100), IN in_last_name VARCHAR(100) )
BEGIN
    INSERT INTO `users`(first_name, last_name) VALUES (in_first_name, in_last_name );
END

Here is the table in the database test

CREATE TABLE IF NOT EXISTS `users` (
    `users_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `first_name` varchar(100) NOT NULL,
    `last_name` varchar(100) NOT NULL,
    PRIMARY KEY (`users_id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

--

-- Dumping data for table users

INSERT INTO `users` (`users_id`, `first_name`, `last_name`) VALUES
(1, 'Joey', 'Rivera'),
(2, 'John', 'Doe'),
(3, 'Cesar', 'Guerrero'),
(4, 'Maribel', 'Guerrero'),
(8, 'Marc', 'Jacobs');

Upvotes: 2

Views: 2303

Answers (3)

peterm
peterm

Reputation: 92785

String literals should be quoted. Try to change

mysqli_query($link, "CALL AddUser($firstname, $lastname)");

to

mysqli_query($link, "CALL AddUser('$firstname', '$lastname')");
                                  ^          ^  ^         ^

and add at least some the basic error handling

$link = mysqli_connect("localhost", "root", "", "test");
if (mysqli_connect_errno()) {
    echo "Connect failed: " . mysqli_connect_error();
    exit();
}
if (!$mysqli_query("CALL AddUser('$firstname', '$lastname')")) {
    echo "CALL failed: " . $mysqli_errno($link) . " - " . $mysqli_error($link);
    exit();
}

On a side note: consider to learn and use prepared statements intend of interpolating query strings.

A version of your code using prepared statements might look like

<?php
if (isset($_POST['submit'])) {

    $firstname = $_POST['firstname'];
    $lastname = $_POST['lastname'];

    $link = new mysqli('localhost', 'root', '', 'test');
    if (mysqli_connect_errno()) {
        die('Connect failed: ' . mysqli_connect_error()); //TODO: a better error handling
    }
    $stmt = $link->prepare("CALL AddUser(?, ?)");
    if (!$stmt) {
        die('Prepare failed: ' . $link->error); //TODO: a better error handling
    }
    $stmt->bind_param('ss', $firstname, $lastname);
    if(!$stmt->execute()) {
        die('Execute failed: ' . $link->error); //TODO: a better error handling
    }
}
?>

And as @MayurKukadiya mentioned add name attribute to your input

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

Upvotes: 3

Mayur Kukadiya
Mayur Kukadiya

Reputation: 994

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

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

Upvotes: 2

HRK
HRK

Reputation: 385

PDO is your need, make sure you have pdo-mysql extention enabled and installed

<?php
$stmt = $dbh->prepare("CALL sp_returns_string(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000); 

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

print "procedure returned $return_value\n";
?>

http://php.net/manual/en/pdo.prepared-statements.php

Upvotes: -1

Related Questions