CODEWITHSUNDEEP
phpmysqlpdo
user5360789
user5360789

Reputation:

Safe Way To Loop PDO Statement

Question:

Loop through a prepared PDO statement, check for duplicates, if no duplicates execute the query?

Current PDO Statement:

$STH = $DBH->prepare("INSERT INTO inbox (id,efrom,subject,msg,eread,date) VALUES ('',:efrom,:esubject,:emsg,:eread,:edate)");
$STH->bindParam(':efrom', $inbox_from[0]);
$STH->bindParam(':esubject', $inbox_subject[0]);
$STH->bindParam(':emsg', $inbox_msg[0]);
$STH->bindParam(':eread', $inbox_read[0]);
$STH->bindParam(':edate', $inbox_date[0]);

Why there needs to be a loop:

I need the arrays $inbox_* to be incremented then queried until it gets to the end of the array.

Example:

$inbox_from('hello','how','are','you');

someloop(somecondition) {
    //output would be:
    $STH->bindParam(':efrom', $inbox_from[0]);
    $STH->bindParam(':efrom', $inbox_from[1]);
    $STH->bindParam(':efrom', $inbox_from[2]);
    $STH->bindParam(':efrom', $inbox_from[3]);
    //It ends at [3] index because its the end of the array.
}
$STH = execute();
//So now it executes and should have put the 4 array indexes into different efrom columns. So column id 1 has 'hello' and id 4 has 'you'.

Maybe:

for($i = 0; //Something to check if array ended; ++$i) {
//Someway to Properly bind the arrays and execute?
}

or some use of a While Loop?

Hope I explained it my best.

Whats the best practice to use here?

Upvotes: 1

Views: 360

Answers (2)

Barmar
Barmar

Reputation: 780798

Use numbered parameters instead of named parameters, and build the query and parameters dynamically.

$sql = "INSERT INTO inbox (efrom,subject,msg,eread,date) VALUES ";
// array_fill will create an array of N "(?, ?, ?, ?, ?)" strings
// implode will then join them together with comma separators
$sql .= implode(', ', array_fill(0, count($inbox_from), "(?, ?, ?, ?, ?)"));
$STH = $DBH->prepare($sql);
$params = array();
// Populate the $params array with all the input values
foreach ($inbox_from as $i => $from) {
    $params[] = $from;
    $params[] = $inbox_subject[$i];
    $params[] = $inbox_msg[$i];
    $params[] = $inbox_read[$i];
    $params[] = $inbox_date[$i];
}
$STH->execute($params);

You can leave the id field out of the column list, and it will be filled in automatically using auto-increment.

To remove duplicate messages, you can do:

$check_stmt = $DBH->prepare("SELECT COUNT(*) AS count FROM inbox WHERE msg = :msg");
$check_stmt->bindParam(':msg', $msg);
$messages_seen = array();
foreach ($inbox_msg as $i => $msg) {
    // Check if the message is already in the DB
    $check_stmt->execute();
    $first_row = $check_stmt->fetch(PDO::FETCH_OBJ);
    $check_stmt->fetchAll(); // Fetch the rest of the query to get in sync
    if ($first_row->count > 0) {
        $messages_seen[$msg] = true; // Remember that we already saw this message
    } elseif (!isset($messages_seen[$msg])) // If we haven't already seen this message
        $params[] = $inbox_from[$i];
        $params[] = $inbox_subject[$i];
        $params[] = $msg;
        $params[] = $inbox_read[$i];
        $params[] = $inbox_date[$i];
        $messages_seen[$msg] = true; // Remember that we added this message
    }
}
$sql = "INSERT INTO inbox (efrom,subject,msg,eread,date) VALUES ";
// There's 1 (...) group for every 5 parameters, so divide the length of $params by 5 to know how many of them to put in the SQL
$sql .= implode(', ', array_fill(0, count($params)/5, "(?, ?, ?, ?, ?)"));
$STH = $DBH->prepare($sql);
$STH->execute($params);

When adding an index on a TEXT datatype, you have to specify the number of bytes of the text to store in the index. So it should be something like:

CREATE INDEX ix_msg ON inbox (msg(200));

Upvotes: 1

Yair.R
Yair.R

Reputation: 795

You can create one big query if you are using Insert to same table

INSERT INTO table (columns) VALUES (....), (....)...., (...)

Its better than calling to sql for each row. Faster too

Upvotes: 0

Related Questions