-3

I don't wan't to prepare or bind params, I just want to simply execute the call to the procedure, which is stored in my restaurant database. The procedure receives 2 input parameters that I need to pass in via variables that I got from the AJAX script. When I call the procedure from Workbench, it works perfectly, but will not work when called from PHP script.

<?php
//Open Database Connection
    $connection = "mysql:host=localhost;dbname=restaurant";
    $user = "root";
    $pwd = "Dumpweed37!";
    $db = new PDO($connection, $user, $pwd);

//Get variables from AJAX
    $first = $_GET["first"];
    $last = $_GET["last"];
    $type = $_GET["type"];

//Add new employee
    if ($type==="server"){
        $add = $db->exec('CALL AddServer($first, $last)'); }
    else if ($type==="bartender"){
        $add = $db->exec('CALL AddBartender($first, $last)'); }
    else if ($type==="host"){
        $add = $db->exec('CALL AddHost($first, $last)'); }

Here is my stored procedures:

DROP PROCEDURE IF EXISTS AddServer$$

CREATE PROCEDURE AddServer(firstname TEXT,lastname TEXT)
BEGIN
    INSERT INTO employee VALUE (NULL, firstname,lastname);
    INSERT INTO server VALUE (LAST_INSERT_ID(), NULL);
END$$

DROP PROCEDURE IF EXISTS AddBartender$$

CREATE PROCEDURE AddBartender(firstname TEXT,lastname TEXT)
BEGIN
    INSERT INTO employee VALUE (NULL, firstname,lastname);
    INSERT INTO bartender VALUE (LAST_INSERT_ID());
END$$

DROP PROCEDURE IF EXISTS AddHost$$

CREATE PROCEDURE AddHost(firstname TEXT,lastname TEXT)
BEGIN
    INSERT INTO employee VALUE (NULL, firstname,lastname);
    INSERT INTO `host` VALUE (LAST_INSERT_ID());
END$$
AmalJo
  • 106
  • 9
Katrina Renwick
  • 1
  • 1
  • No, you want to *execute a query with user provided values* (that this involves calling a procedure is relatively irrelevant), which means you *do* want to prepare and bind. – deceze Jan 10 '18 at 10:40

1 Answers1

2

I think it is all about missing quotes:

$db->exec("CALL AddServer('$first', '$last')");

However, you should take care of sql injection, so you should consider using prepared statements!

Mihai Matei
  • 24,166
  • 5
  • 32
  • 50