PDO multiple fetching of same query

Question 1

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!

Question 2

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
}

Question 3

You could simply return the statement object.
Or if you want to encapsulate it (so that it can only be used to fetch the data) use the SPL's IteratorIterator

return new IteratorIterator($stmtAA);

then you can use

foreach( testQuery() as $row) { ... }

in your script.

Question 4

You need to declare it outside the loop

$help_text = "";
while($row = $STH->fetch()) {
    echo $row['text'];
    $help_text .= $row['text'];
}
echo "->";
echo $help_text;
echo "<-";

Question 5

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.

Question 6

Here is the code to join from two separate mysql databases using PDO.

Pastebin: Tested code

Limitations:

Both mysql databases must be on the same server
Only one connection is used.
The database user must have the necessary privileges on both databases

Query using archive_pfizer (logger) and pdone_legacy (session_brand_presentation)

$sqlBoth = 'SELECT DISTINCT
                logger.hcp_id,
                logger.rep_id,
                logger.type,
                session_brand_presentation.ID,
                session_brand_presentation.brand_id,
                session_brand_presentation.createdAt,
                session_brand_presentation.modifiedAt
            FROM
                archive_pfizer.logger
            JOIN
                pdone_legacy.session_brand_presentation
                   ON logger.session_id = session_brand_presentation.local_session_id

            WHERE
                logger.type = :lg_type';

$stmt = $dbTest->prepare($sqlBoth);
$stmt->bindValue(':lg_type', 'email_sent', PDO::PARAM_STR);
$stmt->execute();

$resultBoth = $stmt->fetchAll();
$stmt->closeCursor();

Output:

pdone_legacy and archive_pfizer

Array
(
    [0] => Array
        (
            [hcp_id] => hcp_id_01
            [rep_id] => rep_od_01
            [type] => email_sent
            [ID] => ID_01
            [brand_id] => brand_id_01
            [createdAt] => 2015-09-24 01:42:51
            [modifiedAt] => 
        )
)

Database Connection:

/**
 * must have access rights to both db's
 */
// db connection to archive_pfizer and pdone_legacy
$dsn = 'mysql:host=localhost;dbname=pdone_legacy';
$username = 'pfizer';
$password = 'pfizer';
$options = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);
$dbTest = new PDO($dsn, $username, $password, $options);
$dbTest->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Test that we can access the separate databases...

1) archive_pfizer (logger)

/* ----------------------------------------------------------
 *   Query archive_pfizer (logger)
 */
$sqlArchive = 'SELECT  hcp_id, rep_id, type, session_id, createdAt, modifiedAt
FROM archive_pfizer.logger
WHERE session_id = :a_session_id';

$stmt = $dbTest->prepare($sqlArchive);
$stmt->bindValue(':a_session_id', 'session_id_01', PDO::PARAM_STR);
$stmt->execute();

$resultArchive = $stmt->fetchAll();
$stmt->closeCursor();

echo '<br />', 'archive_pfizer.logger', '<br />';
echo '<pre>';
print_r($resultArchive);
echo '</pre>';

2) pdone_legacy (session_brand_presentation)

/* --------------------------------------------------
 *  Query pdone_legacy (session_brand_presentation)
 */
$sqlPDone = 'SELECT ID,
                    local_session_id,
                    brand_id,
                    createdAt,
                    modifiedAt
FROM pdone_legacy.session_brand_presentation
WHERE local_session_id = :sbp_session_id';

$stmt = $dbTest->prepare($sqlPDone);
$stmt->bindValue(':sbp_session_id', 'session_id_01', PDO::PARAM_STR);
$stmt->execute();

$resultPDone = $stmt->fetchAll();
$stmt->closeCursor();
echo '<br />', 'pdone_legacy.session_brand_presentation', '<br />';
echo '<pre>';
print_r($resultPDone);
echo '</pre>';

PDO multiple fetching of same query

Answers

php,pdo,fetch