Correct usage of php PDO with mysql stored routines to fetch table data

Question

I would like to call mysql stored procedure with two in parameters say from and to and print the data in table. I have used the following code:

try
{       
    $age_from = $_POST['from'];
    $age_to = $_POST['to'];
    $stmt = $db->prepare("CALL proc_report_filtered(:age_from,:age_to)");
    $stmt->bindParam(':age_from',$age_from);
    $stmt->bindParam(':age_to',$age_to);
    $records = $stmt->execute();
    $records->setFetchMode(PDO::FETCH_ASSOC);

}
catch (PDOException $e)
{   
    die("Some problem getting data from database !!!" . $e->getMessage());
}

Later I used while loop as before to print the table data.

while (  $report = $records->fetch()  )

Stored routine proc_report_filtered is a simple query to fetch data between the age range as follows:

select * from employee where age in between 30 and 40;

where 30 is $age_from and 40 is $age_to passed to stored routine.

Unfortunately the code doesnt work. Please correct me where I am going wrong. I am new to using php PDO.

Something I don't get. You have `query("call proc_report")` yet you're using `CALL proc_report_filtered(:n_c_from,:n_c_to)` in the other one. Are those 2 different procedures, or are you calling the wrong one? — Funk Forty Niner, May 18 '16 at 13:56
See if you get anything out of http://php.net/manual/en/pdo.error-handling.php and make sure those POST arrays contain values. — Funk Forty Niner, May 18 '16 at 13:58
@ Fred -- both are different procedures. First one works fine but the second one doesnt work ( proc_report_filtered ) — sravan kumar, May 18 '16 at 14:01
and that's where error reporting would have been your friend here. I don't post that anymore ever since I've been bombed about it (last week or so). But hey... why not, just for old times sake =>> http://php.net/manual/en/function.error-reporting.php <<= ;-) — Funk Forty Niner, May 18 '16 at 14:01
oh, nice Stealth edit there. without marking it as an edit saying you made an error. Now I'm forced to reopen; lovely. Thanks for that — Funk Forty Niner, May 18 '16 at 14:04
sorry for sharing the wrong code. I have corrected the try para in the second procedure. Please correct me. Excuse me for this mistake. — sravan kumar, May 18 '16 at 14:06
I mean it doesnt fetch data on the screen, it just shows a blank screen. — sravan kumar, May 18 '16 at 14:07
Sorry for mistakes in previous versions. I have re edited the question. Please help me out — sravan kumar, May 18 '16 at 14:40

score 1 · Accepted Answer · edited May 18 '16 at 16:39

In general, there is nothing essentially special in calling stored procedures with PDO. The only issue may affect only following queries, but not the current one.

So, your problem is not related to stored procedures, but to the basic PDO syntax only: you are using wrong code to get the records. Here goes the proper one

$stmt->execute();
$records = $stmt->fetchAll(PDO::FETCH_ASSOC);

In case it is not the issue but a copy/paste problem, and to help further visitors, here is a short check-list for the PDO debugging:

Step 1. Run your query in a DB console to make sure it works by itself.

Step 2. Make sure that you can see ALL PHP errors.

error_reporting(E_ALL);
ini_set('display_errors', 1); // for the dev server only

Step 3. Make sure that your PDO throws exceptions, by adding this code
```
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
```
Step 4. Run your query without prepared statements, using PDO::query(), with values written by hand:
```
$stmt = $db->query("CALL proc_report_filtered(30,40)");
```

Step 5. run with a prepared statement but in a simplified form, to eliminate all possible spelling issues:

$stmt = $db->prepare("CALL proc_report_filtered(?,?)");
$stmt->execute(array($_POST['from'],$_POST['to']));

Thank you for your reply, It helped me half way. How should I use ( $report = $records->fetch() ) in while loop again ?? As we already used $stmt->fetchAll already. Thanks once again. — sravan kumar, May 18 '16 at 14:55
Thank you for your time and detailed explanation. A small correction in step 5. The last statement should be "$records->execute(array($_POST['n_c_from'],$_POST['n_c_to'])); " — sravan kumar, May 18 '16 at 15:41

Correct usage of php PDO with mysql stored routines to fetch table data

1 Answers1