Asked  7 Months ago    Answers:  5   Viewed   36 times

I am fetching the results from a db with PDO::FETCH_ASSOC. The issue is that I am doing it twice in the same file. Is that a known issue? What would be the alternative?

Here is my code:

FIRST TIME

while($row = $ordersQuery->fetch(PDO::FETCH_ASSOC)) 
        {
            $totalAmount += $row['clientPrice']/100; 
        }
        echo $totalAmount;

SECOND TIME

    while($row = $ordersQuery->fetch(PDO::FETCH_ASSOC)) 
            {
    ....
    }

Whenever I remove the first fetching, the second works fine. If I have both, the second does not return anything.

Thanks!

 Answers

19

You can't fetch from the DB multiple times like this. Do this instead:

$orders = $ordersQuery->fetchAll(PDO::FETCH_ASSOC);

...

foreach ($orders as $val) {
    // stuff 1
}

...

foreach ($orders as $val) {
    // stuff 2
}
Wednesday, March 31, 2021
 
Juriy
answered 7 Months ago
72

After much reading I think I finally came across the answer: it works this way intentionally, and you have the option of making it operate otherwise.

There's a mostly undocumented PDO constant called PDO::FETCH_PROPS_LATE which you can use to cause the properties to be fetched into the object after its been constructed. For example:

$obj = $STH->fetchAll(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, 'person');

Will cause the properties to be assigned AFTER the object is created, so my example above would not modify the properties at all. Leaving the PDO::FETCH_PROPS_LATE off, of course, causes it to act as described in my example in the original question.

The maintainers seem to have actively considered that both behaviors may be desirable and given you the option to do either. The documentation doesn't even explain it -- I was reading through a list of PDO constants and saw it, and gave it a shot.

Saturday, May 29, 2021
 
cegfault
answered 5 Months ago
28

Multiple Values Insert with PDO Prepared Statements

Inserting multiple values in one execute statement. Why because according to this page it is faster than regular inserts.

$datafields = array('fielda', 'fieldb', ... );

$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);
$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);

more data values or you probably have a loop that populates data.

With prepared inserts you need to know the fields you're inserting to, and the number of fields to create the ? placeholders to bind your parameters.

insert into table (fielda, fieldb, ... ) values (?,?...), (?,?...)....

That is basically how we want the insert statement to look like.

Now, the code:

function placeholders($text, $count=0, $separator=","){
    $result = array();
    if($count > 0){
        for($x=0; $x<$count; $x++){
            $result[] = $text;
        }
    }

    return implode($separator, $result);
}

$pdo->beginTransaction(); // also helps speed up your inserts.
$insert_values = array();
foreach($data as $d){
    $question_marks[] = '('  . placeholders('?', sizeof($d)) . ')';
    $insert_values = array_merge($insert_values, array_values($d));
}

$sql = "INSERT INTO table (" . implode(",", $datafields ) . ") VALUES " .
       implode(',', $question_marks);

$stmt = $pdo->prepare ($sql);
$stmt->execute($insert_values);
$pdo->commit();

Although in my test, there was only a 1 sec difference when using multiple inserts and regular prepared inserts with single value.

Tuesday, June 1, 2021
 
kensil
answered 5 Months ago
68

You mentioned two parameters (with the same name) for the prepare statement, yet you supply a value for the first parameter only (that's what the error was about).

Not quite sure how PDO internally solved the same-parameter-name issue, but you can always avoid that.

Two possible solutions:

$sql = "select * from $table ".
       "where "
       "first_name like concat('%', :fname, '%') or ".
       "last_name  like concat('%', :lname, '%')";
$stmt= $DBH->prepare($sql);
$stmt->bindValue(':fname', $string, PDO::PARAM_STR);
$stmt->bindValue(':lname', $string, PDO::PARAM_STR);

$sql = "select * from $table ".
       "where "
       "first_name like concat('%', ?, '%') or ".
       "last_name  like concat('%', ?, '%')";
$stmt= $DBH->prepare($sql);
$stmt->bindValue(1, $string, PDO::PARAM_STR);
$stmt->bindValue(2, $string, PDO::PARAM_STR);

By the way, the existing way you have done still has SQL injection issues.

Friday, July 30, 2021
 
jul
answered 3 Months ago
jul
62

This is what PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION is for. Use it like this:

$pdo = new PDO(
            'mysql:host=localhost;port=3306;dbname=mydb;charset=utf8'
            , 'user'
            , 'pass'
            , [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ]
    );

When used this way errors actually get thrown as exceptions. This means that should an error occur with fetch (or other methods using this pdo object) an exception will be thrown and the method won't actually return at all. This is a very effective way of handling errors in PDO. Now you know that if fetch returns a value no errors occured and therefore if it is false then the query returned no records.

Monday, August 23, 2021
 
Soviut
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :