How to discover inconsistencies in a PDO query?

PHP
1

I have the following

  • A multidimensional array encoded as JSON. Each index holds exactly 80 keys
  • A CSV file with 80 items representing identical keys in the indexes above since dumping them in the same script will be too cumbersome

First, I mutate the original keys in the JSON array to use underscores since named placeholders don’t accept that

foreach ($content as $key => &$bulk) {

    $vals = array_values($bulk);

    $keys = array_map(function ($item) {

    $item = str_replace(' ', '_', $item);

    return ":$item"; // semi-colons are redundant but I tried it also to be on the safe side

    }, array_keys($bulk));

    $bulk = array_combine($keys, $vals);

    // add date to each fixture
    $bulk[':date'] = date('Y-m-d');
}

Then I do the exact same thing for items in the CSV file and convert them to what can be used in the actual query

// swap out spaces
array_walk($validColumns, function (&$val) {

    $val = str_replace(' ', '_', $val);
});

// prepare named placeholders
$transform = array_map(function ($val) {

    return ":$val";
}, $validColumns);

$placeholders = implode(',', $transform);

$validColumns = array_map(function ($val) {

    return "`$val`";
}, $validColumns);

After this, I build the query using the following string

$query = 'INSERT INTO container (' . implode(',', $validColumns) . ") VALUES ($placeholders)";
And attempt to execute with this

try {

    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $conn->beginTransaction();

    $transaction = $conn->prepare($query);

    foreach ($content as $key => $bulk) {

        if (!is_array($bulk)) $bulk = [$bulk];

        var_dump(array_diff($transform, array_keys($bulk))); // this returns an empty array even when I swap the arguments

        $transaction->execute($bulk);
    }

But it keeps throwing the dreaded error SQLSTATE[HY093]: Invalid parameter number: parameter was not defined’. My question is how may I know what keys I’m missing, from the nested array_diff in the foreach?

When I var_dump the lengths, they both return 80. Same with the placeholder values. I have also tried using the object keys without adding the semi-colon but the issue persists.

Are there any additional tools I can use to detect why it relentlessly throws the error as per what the inconsistency is–whether it can’t find a matching placeholder or if there are more values than placeholders? I have scoured every single question with this error down to the 16th search result and every time, someone posts his query and bound parameters but in my case where I have 80 length arrays and 80 length cryptic compound-word strings representing keys, that will be an effective way to deter anyone from trying to help.

2

Is this a syntax error?

<?php 
declare(strict_types=1);
error_reporting(-1);
ini_set('display_errors', '1');

foreach ($content as $key => &$bulk)
{

    $vals = array_values($bulk);

    $keys = array_map(function ($item)
    {
      $item = str_replace(' ', '_', $item);

      return ":$item"; // semi-colons are redundant but I tried it also to be on the safe side

    }, array_keys($bulk)); // SYNTAX ERROR ???

    $bulk = array_combine($keys, $vals);

    // add date to each fixture
    $bulk[':date'] = date('Y-m-d');
}
3

Hi! I just ran the script again with error_reporting turned on and it did not throw any errors. Should it? bulk is coming from the foreach above

Why do you think it should throw an error though 'cause it got me really scared when I saw your comment? Remember it’s a multidimensional array. I said array_keys($bulk) not array_keys($content). Bulk is its own array with its own keys. Why should trying to modify those keys result in an error?

4

After the foreach loop there is this:

}, array_keys($bulk)); // SYNTAX ERROR ???
5

Nope it’s still in the foreach scope. Let me post the code again

foreach ($content as $key => &$bulk) {

					$vals = array_values($bulk);

					$keys = array_map(function ($item) {

						$item = str_replace(' ', '_', $item);

						return ":$item";
					}, array_keys($bulk));

					$bulk = array_combine($keys, $vals);

					$bulk[':date'] = date('Y-m-d');
				}
6

WHOOPS, I just checked and noticed the round brackets enclosing the $keys = array_map(function ($item).

I would have formatted the script to make it easier to recognise.

$keys = array_map
(
  function ($item)
  {
    $item = str_replace(' ', '_', $item);
    return ":$item";
  }, 
  array_keys($bulk)
);
7

It’s the unorderly nature of PHP functions. Ideally the array should come first, before the callback. But that’s beside the point. I’m really touchy about derailing this precarious thread

1 Like
8

It is not easy to spot errors and maybe better if a small data sample of the Json and/or Csv file is supplied to enable the scipt to be run locally.

9

https://pastebin.com/zWJm9Gdh. You might notice there’s a trailing comma after the date index in the array. That’s because I used var_export($bulk). The comma disappears when I use print_r instead

1 Like
10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.