pyram pyram - 11 months ago 110
MySQL Question

PHP Call mysql stored procedure with in parameter

I'm calling a mysql stored procedure with two input parameters. This is the code I have:

if (isset($_POST['button1'])) {
$con = mysql_connect("localhost:3306","root","");
if (!$con) {
echo '<b>Could not connect.</b>';
die(mysql_error()); // TODO: better error handling
} else {
mysql_select_db("php_database_1", $con);

$username_v = $_POST['username'];
$password_v = $_POST['password'];

$stmt = $dbh->prepare("CALL login(?, ?)");
$stmt->bindParam(2, $username_v, $password_v, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);

// call the stored procedure

print "procedure returned $username_v\n";

When executing, I get:

Notice: Undefined variable: dbh in E:\xampp\htdocs\php4\default.php on line 52
Fatal error: Call to a member function prepare() on a non-object in E:\xampp\htdocs\php4\default.php on line 52

How can I fix this?


Answer Source

Edited: After seeing more code, you have attempted to mix the mysql_() functions with PDO. You cannot do that -- instead, use PDO only. The two APIs do not work together, and the old mysql_*() API does not support prepared statements at all.

You have not connected to your database or instantiated a PDO object.

$username_v = $_POST['username'];
$password_v = $_POST['password'];
$dsn = 'mysql:dbname=testdb;host=';

// You must first connect to the database by instantiating a PDO object
try {
    $dbh = new PDO($dsn, 'root', 'root_db_pw');
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();

// Then you can prepare a statement and execute it.    
$stmt = $dbh->prepare("CALL login(?, ?)");
// One bindParam() call per parameter
$stmt->bindParam(1, $username_v, PDO::PARAM_STR); 
$stmt->bindParam(2, $password_v, PDO::PARAM_STR); 

// call the stored procedure