CODEWITHSUNDEEP
phpmysqllaravel-5.8
xec86
xec86

Reputation: 1421

MYSQL CREATE EVENT with laravel DB::statement

This question relates to Laravels DB::statement function handling of MySQL querys with PHP variable bindings and not PHP's PDO binding process as found in the possible duplicate here. PDO is used in the backend by Laravel eloquent but the structure of DB::statement function differs from the way you do it in the PDO method.

I am attempting to have my controller's store function create a mysql event when an entry is saved to the database. When the form is submitted, Laravel is throwing the error

'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens (SQL: CREATE EVENT test ON SCHEDULE EVERY 5 MINUTE DO UPDATE pagers SET escalation_level = escalation_level + 1 WHERE id = :issue)'.

I am attempting to recreate the following MySQL query 

CREATE EVENT test ON SCHEDULE EVERY 5 MINUTE DO UPDATE issues SET escalation_level = escalation_level + 1 WHERE id = 'Rd8409347-70bb-4e3e-b351-b49c10ce9a2a';

To me it seems that I have 1 variable in the query and then 1 variable passed by the array, so I cant seem to get the query to succeed. Below is the controller that the form submits to. Thank you for your help.

IssueController.php 

  public function store(Request $request)
{
    //Validate Input
    $request->validate([
       'company_id' => 'string|max:255',
       'message' => 'required|string|max:255' 
    ]);

    //Once the form input has passed validation add input to the database

    $issueData = new Issue();
    $issueData->id = "R" . Uuid::generate(4)->string;
    $issueData->company_id = $request->company_id;
    $issueData->first_name = Auth::user()->first_name;
    $issueData->last_name = Auth::user()->last_name;
    $issueData->user_id = Auth::user()->id;
    $issueData->message = $request->message;


    //Create MySQL event to increment escalation level
    //Event will be named equal to the new page 'R' ID
    //That has just been generated.

    DB::statement('CREATE EVENT test ON SCHEDULE EVERY 5 MINUTE DO UPDATE issues SET escalation_level = escalation_level + 1 WHERE id = :issue_id', array('issue_id' => $issueData->id));

    $issueData->save();

    return redirect('home')->with('status', ['Message sent successfully.', 
        'You can monitor the status of your message from Check Status section']);

}

Upvotes: 1

Views: 1052

Answers (1)

xec86
xec86

Reputation: 1421

I found the issue with the help of the SO post here. Some queries in Laravel require the use of unprepared statements. I was able to resolve the issues by using DB::unprepared like this:

        DB::unprepared('CREATE EVENT event_name ON SCHEDULE EVERY 5 MINUTE DO UPDATE issues SET escalation_level = escalation_level + 1 WHERE id = ' . '/'' . $issueData->id . '/'');

Upvotes: 2

Related Questions