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
– Funk Forty Niner, Commented May 18, 2016 at 13:56
See if you get anything out of php.net/manual/en/pdo.error-handling.php and make sure those POST arrays contain values. — Funk Forty Niner
– Funk Forty Niner, Commented May 18, 2016 at 13:58
@ Fred -- both are different procedures. First one works fine but the second one doesnt work ( proc_report_filtered ) — sravan kumar
– sravan kumar, Commented May 18, 2016 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 =>> php.net/manual/en/function.error-reporting.php <<= ;-) — Funk Forty Niner
– Funk Forty Niner, Commented May 18, 2016 at 14:01

sravan kumar · Accepted Answer · 2016-05-18 16:39:41Z

1

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']));

edited May 18, 2016 at 16:39

sravan kumar

6133 gold badges12 silver badges24 bronze badges

answered May 18, 2016 at 14:48

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sravan kumar Over a year ago

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 Over a year ago

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'])); "

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related