1

I'm trying to write a query to insert/update a table and it's my first time using prepared statements, I'm receiving the error SQLSTATE[HY093]: Invalid parameter number but from what I can see I'm passing all the columns/values required.

(I'm trying to do this without using bindParam as in example #2 here)

This is just a test for now, I plan on making it dynamic once I've actually got a query working.

$data_test = [
            ':event_id' => 3354,
            ':event'    => 'TESTESTEST',
            ':staff_booking_id' => 27255,
            ':is_read' => 'yes',
            ':priority' => 'medium'
        ];

        $q = "INSERT INTO events(event_id, event, staff_booking_id, is_read, priority) 
              VALUES(:event_id, :event, :staff_booking_id, :is_read, :priority) 
              ON DUPLICATE KEY UPDATE event_id = LAST_INSERT_ID(:event_id), event = :event, staff_booking_id = :staff_booking_id, is_read = :is_read, priority = :priority;";

        $result = $this->db->prepare($q);
        $result = $result->execute($data_test);
Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
Adam Roberts
  • 562
  • 1
  • 5
  • 20
  • Tip: Don't put `;` in queries run through an API like PDO. That's a delimiter used for interactive environments to indicate the end of the query. That's done implicitly in the `prepare()` function. – tadman Feb 13 '20 at 22:43
  • For the life of me, I can't remember which link on php.net talks about this but it does have something to do with your reusing the same named placeholders again. It has something to do with "emulation", if my memory serves me right. There is someone who knows this here, that I know. Edit: About *"(I'm trying to do this without using bindParam as in example #2 here)"* - The page you referenced doesn't mention the use of `ON DUPLICATE KEY`. – Funk Forty Niner Feb 13 '20 at 22:43
  • 1
    @FunkFortyNiner I've seen issues with using the same place-holder twice, and it may depend on if your driver supports it or not. Emulation may fix the issue. – tadman Feb 13 '20 at 22:44
  • 1
    @tadman That's what I meant, thanks. – Funk Forty Niner Feb 13 '20 at 22:45
  • Note: I added the "on-duplicate-key" tag, since I thought it was relevant. – Funk Forty Niner Feb 13 '20 at 22:46
  • Could you explain what you mean? Is the issue with the place holders on duplicate? – Adam Roberts Feb 13 '20 at 22:47
  • @AdamRoberts [About your comment, reload mine...](https://stackoverflow.com/questions/60217542/prepared-statements-sqlstatehy093-invalid-parameter-number#comment106512726_60217542). I edited it before seeing your comment. – Funk Forty Niner Feb 13 '20 at 22:48
  • I'd have thought the same placeholders would have just bound the values in the same way. Is there a way to do this without making it difficult to do so dynamically? – Adam Roberts Feb 13 '20 at 22:49
  • 1
    @tadman (and Adam). I found [the duplicate](https://stackoverflow.com/questions/2432084/pdo-parameterized-query-reuse-named-placeholders) and closed the question. That is what I meant earlier. I Google'd the right words. – Funk Forty Niner Feb 13 '20 at 22:50
  • 1
    @FunkFortyNiner I've taken a read and added `PDO::ATTR_EMULATE_PREPARES => true,` but I'm now getting the error `SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'event = 'TESTESTEST', staff_booking_id = '27255', is_read = 'yes', priority = 'm' at line 3` I can't see any issue with this, any ideas? – Adam Roberts Feb 13 '20 at 22:56
  • 1
    Yes, it's because `EVENT` is a mysql reserved word. Either rename it to one that isn't reserved or use ticks around it. – Funk Forty Niner Feb 13 '20 at 22:59

1 Answers1

2

As commentented by FunkFortyNiner and tadman, it is possible that the issue comes from the fact that you are reusing the same placeholder.

Actually the MySQL syntax does not require you to reuse the named parameter: you can use the VALUES() to refer to the values initially passed for INSERT.

Also, your attempt to update event_id using LAST_INSERT_ID() does not seem right; I am unsure that this is valid syntax - and anyway, if this is the primary key of table, then you don't want to update it.

Finally, as pinpointed by FunkFortyNiner, event is a reserved word in MySQL, so it needs to be quoted.

$q = 
    "INSERT INTO events(
        event_id, 
        `event`, 
        staff_booking_id, 
        is_read, 
        priority
    ) 
    VALUES(
        :event_id, 
        :event, 
        :staff_booking_id, 
        :is_read, 
        :priority
    ) 
    ON DUPLICATE KEY UPDATE 
        `event` = VALUES(`event`),
        staff_booking_id = VALUES(staff_booking_id),
        is_read = VALUES(is_read), 
        priority = VALUES(priority)";
GMB
  • 216,147
  • 25
  • 84
  • 135
  • There's also the thing about `EVENT` being reserved in mysql. I added [a note](https://stackoverflow.com/questions/60217542/prepared-statements-sqlstatehy093-invalid-parameter-number?noredirect=1#comment106513064_60217542) about that under their question in comments, in response to theirs above it. – Funk Forty Niner Feb 13 '20 at 23:00
  • @FunkFortyNiner: ah yes, you are right, did not spot that. I edited my answer accordingly... Thanks. – GMB Feb 13 '20 at 23:03
  • 1
    It also eluded me earlier till I noticed [their comment](https://stackoverflow.com/questions/60217542/prepared-statements-sqlstatehy093-invalid-parameter-number#comment106512999_60217542) about having a new error. That's what made me take another (closer) look at their code. Edit: You know; I'm glad you managed to put in an answer even after I closed it. – Funk Forty Niner Feb 13 '20 at 23:05