CODEWITHSUNDEEP
phpmysqlmysqli
alda1234
alda1234

Reputation: 204

PHP: How to use date interval for a stored procedure?

I have this php script that executes a stored procedure. I am retrieving the StoredProcedure that takes two parameter values.

Problem:

How can I put lets say: the actual day past 10 minutes? So, I want the every 10 minute old record. Is this possible even though this StoredProcedure has been provided to me only for readable access with a start and end date as paramaters?

Thanks in advance

PHP:

    //DATE values to replace in parameters
    $start_date = "2014-11-11";
    $end_date   = "2014-11-12"; 

    if($row = mysqli_fetch_assoc($query_overlay_result)){
        $sp_value = $row['query']; 
    }

    //replacing parameter values for Dates
    $sp_value = str_replace('start_date',$start_date,$sp_value);
    $sp_value = str_replace('end_date',$end_date,$sp_value);

Stored Procedure: It is in a different server, so I saved the actual String value of the SP in my table "overlay" like so: CALL telephony.sp_get_spec_stat_all_agents( 'start_date', 'end_date')

    //Overlay table
    $query_overlay_sql    = "SELECT query FROM overlays";
    //prepare query
    $query_overlay_result = mysqli_query($dbh1,$query_overlay_sql);

I then execute the SP by retrieving two values that I need to join tables from different servers.

while ($row = mysqli_fetch_assoc($dbh2_result)){   
        $memo[] = array( "username"  => $row['memo_code'],
                         "aht_value" => $row['avg_handle_time']
                       );
}

Hope this clarifies things

Upvotes: 1

Views: 309

Answers (1)

Sam Dufel
Sam Dufel

Reputation: 17598

You probably don't need to store the name of the prepared statement in the database.

The simpler approach is to run the prepared statement directly:

$sql = 'CALL telephony.sp_get_spec_stat_all_agents(NOW() - INTERVAL 10 MINUTE, NOW())';
$query_overlay_result = mysqli_query($dbh2, $sql);

Upvotes: 1

Related Questions