PDO - bind param not working for long statement

Question

For some reason my PDO isn't inserting my data into mysql. If I were to preg_replace the stuff and copy paste it into the mysql terminal, it works as expected.

$list = [
  "data" => $_POST['data'],
  "c" => $_POST['company'],
  "co" => $_POST['country'],
  "s" => $_POST['state'],
  "su" => $_POST['suburb'],
  "st" => $_POST['street'],
  "ui" => 12
];

$statement = "
INSERT IGNORE INTO location (country, state, suburb, street)
VALUES (:co, :s, :su, :st);

SELECT id INTO @l
FROM location
WHERE
country = :co
AND
state = :s
AND
suburb = :su
AND
state = :st;


INSERT IGNORE INTO company (name)
VALUES (:c);

SELECT id INTO @c
FROM company
WHERE
name = :c;

SELECT id INTO @si
FROM store
WHERE
company_id = @c
AND
location_id = @l;


INSERT INTO map (data, store_id, user_id)
VALUES (:data, @si, :ui );
";
$query = $pdo->prepare($statement);

$temp_s = $statement;

foreach ($list as $key => $val)
{
  $temp_s = preg_replace("/:$key\b/", "'$val'", $temp_s);
  $query->bindParam(":$key", $val);
}

What my preg_replace looks like:

INSERT IGNORE INTO location (country, state, suburb, street)
VALUES ('Australia', 'Victoria', 'Melbourne', 'test');

SELECT id INTO @l
FROM location
WHERE
country = 'Australia'
AND
state = 'Victoria'
AND
suburb = 'Melbourne'
AND
state = 'test';


INSERT IGNORE INTO company (name)
VALUES ('coles');

SELECT id INTO @c
FROM company
WHERE
name = 'coles';

SELECT id INTO @si
FROM store
WHERE
company_id = @c
AND
location_id = @l;


INSERT INTO map (data, store_id, user_id)
VALUES ('data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAcIAAAH0CAYAAACuOy31AAAc9UlEQ…D9HAIIIIBAi4AQtnxYgwACCCBwmIAQHgbu5xBAAAEEWgR+AanQ2Peo03NfAAAAAElFTkSuQmCC', @si, '12' );

Your `$statement` contains multiple statements. Don't do that — Phil, Jun 22 '17 at 00:17
Possibly not the cause of the issue, but in your `SELECT id INTO @l` you are calling `state` twice, rather than `street` the second time around. This will not insert anything, as `Victoria` is not equal to `test`. — Obsidian Age, Jun 22 '17 at 00:17
@Phil So PDO isn't very happy about more than single statements? — A. L, Jun 22 '17 at 00:21
@ObsidianAge It seems that all of my data being inserted has the value `12` for some reason — A. L, Jun 22 '17 at 00:24
@A.Lau it's possible to perform multiple queries in a single execution but I wouldn't recommend it. See https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd — Phil, Jun 22 '17 at 00:25
@Phil I think I found the reason why it wasn't working. All of my param binds are taking on the last value in my `$list` array. So all the `values` are `12`. Any ideas why? — A. L, Jun 22 '17 at 00:27
Because `bindParam` uses a reference. Either try `bindValue` or simply pass your to the `execute` method, ie `$query->execute($list)` — Phil, Jun 22 '17 at 00:41
It's optional. If absent, PHP prepends it for you. You could also simply prefix the array keys with `:` since it doesn't appear to effect your code in any other way — Phil, Jun 22 '17 at 01:02
@Phil Ah okay, I just read the documentation http://php.net/manual/en/pdostatement.execute.php and it showed it with it. That's why I deleted the comment. Still not working though zzz — A. L, Jun 22 '17 at 01:03
@Phil The PDO execution should take into account the saved variables `@l` and stuff right — A. L, Jun 22 '17 at 01:06

PDO - bind param not working for long statement

0 Answers0