PDO: bindParam empty string

Question

Theres's a similar question here, but actually that doesn't give me the answer:
PHP + PDO: Bind null if param is empty

I need my statement work in a loop, with only changing the binded variables.
Like:

$this->array = array(
    "cell1" => "",
    "cell2" => "",
);

$this->sth = $db->prepare("INSERT INTO `table`
    (`coloumn1`, `coloumn2`)
    VALUES (:coloumn1, :coloumn2)");
$this->sth->bindParam(:coloumn1, $this->array['cell1'], PDO::PARAM_STR);
$this->sth->bindParam(:coloumn2, $this->array['cell2'], PDO::PARAM_STR);

//Data proccessing...

foreach($data as $value){
    $this->array['cell1'] = $value['cell1'];
    $this->array['cell2'] = $value['cell2'];
    try {
        this->sth->execute();
        print_r($this->sth->errorInfo());
    }
    catch(PDOException $e){
        echo 'sh*t!';
    }
}

Everything works well until either of the values is an empty string. My problem is when 'cell1' is an empty string, the bound parameter is a nullreference, and it won't work. But I need the referenced binding because of the loop, so bindValue isn't a solution.

And I need the loop very bad, because of the huge data I want to process.

Any suggestion?

I tried right before execute:

foreach($this->array as $value){
   if(!$value) {
        $value = "";
    }
}

It doesn't work. The only way that solved my problem is modifying to this:

    $this->array['cell1'] = !empty($value['cell1']) ? $value['cell1'] : "";
    $this->array['cell2'] = !empty($value['cell2']) ? $value['cell2'] : "";

But this seems too rubbishy...

It's for preparing the statements. I'll have the structure, like this: $data = array( array( "value1", "value2", ), array( "value1", "value2", ) ); I'll turn it into the array which is bound to the statement from element to element and then execute. — LWjuniOr, Jul 04 '13 at 09:24
if yyou have your array already - why another array and even a class variable? — Your Common Sense, Jul 04 '13 at 09:55

score 0 · Answer 1 · answered Apr 26 '18 at 08:21

I know its necroposting, but maybe will help to someone. You trying to check if the variable false, but not null. And not reapplying values back to array.

foreach($this->array as $value){
   if(!$value) {
        $value = "";
    }
}

Try to check for null in loop

foreach($this->array as $index => $value)
{
   $this->array[$index] = !empty($value) ? $value : '';
}

Your Common Sense · Answer 2 · 2013-07-04T09:56:32.547

-1

Your question has nothing to do with PDO but with basic PHP. When there is no variable available - you can't use it at all. So, you have to create it somehow. The way you are using at the moment is not "rubbishy" but quite acceptable. I'd rather call whole code "rubbishy" as it's twice as big as as it should be.

But I need the referenced binding because of the loop, so bindValue isn't a solution.

This assumption is wrong too. Why do you think you can't use bind by value?

$sql = "INSERT INTO `table` (`coloumn1`, `coloumn2`) VALUES (?, ?)";
$sth = $db->prepare($sql);
foreach($data as $value)
{
    $value['cell1'] = !empty($value['cell1']) ? $value['cell1'] : "";
    $value['cell2'] = !empty($value['cell2']) ? $value['cell2'] : "";
    $sth->execute($value);
}

as simple as this

And I need the loop very bad, because of the huge data I want to process.

I don't think it's really huge, as it fits for the PHP process memory. However, consider to use LOAD DATA INFILE query for the real huge amounts.

edited Jul 04 '13 at 09:56

answered Jul 04 '13 at 08:11

Your Common Sense

156,878
40
214
345

Thanks for your time! Actually the data is huge as i sequentially pull it from the database. The code is big, because readability, of course i reduced it. So my idea was preparing the statements then processing the data to arrays then loop through the array with the inserts. Can I somehow declare those arrays for preparing the statements? If I bind as a value, I can't execute with the changed variable value again. That's why I said bindValue is not a solution. – LWjuniOr Jul 04 '13 at 09:09
Who said you can't? Did you try? All you need is to move bindvalue inside of a loop. Or you can just pass it in execute as in my example. – Your Common Sense Jul 04 '13 at 09:54
The concept was to make the fastest solution. Actually I don't know what actually bindValue does step by step, but my logic tells that giving value to a variable is less needy than this binding process. That's why, I didn't want to place into the loop. – LWjuniOr Jul 04 '13 at 12:21
I see. Speed optimization fever. No further questions. – Your Common Sense Jul 04 '13 at 12:29
I see your sarcasm, but you didn't see my code running for half an hour. Anyway, thanks for your time. You helped me a lot. – LWjuniOr Jul 04 '13 at 12:37
You know, I am not only seen thousands codes in my life, but I also have some common sense to tell the real reason from some groundless fantasy that has nothing to do with your real problem. Want me to make your code run 1000 times faster? – Your Common Sense Jul 04 '13 at 12:42
I hope there's no misunderstanding between us. If you can tell me, what is the real problem, and you can make my code 1000 times faster, please do. I'd really appreciate it(but then I should give you my salary, so that wouldn't be fair). I don't want you to do my job. I don't think, this little modification will make the code much more faster, but I believe it could be significant, because there will be thousands of inserts(it will be a kind of data import process) and a millisec of an insert is seconds in real. Thanks, bye. – LWjuniOr Jul 04 '13 at 14:09
`"but I believe"` - that's what you are doing wrong. A programmer should not *believe*. A programmer should *know*. Thousands of inserts should go in one *fraction* of second. So, a millisecond won't be of any good or notice. That's what I am talking about but - alas - in vain. – Your Common Sense Jul 04 '13 at 14:14
By the way, a millisecond is a way too much for such a trifle operation mind you – Your Common Sense Jul 04 '13 at 14:23

PDO: bindParam empty string

2 Answers2