Checking elements in an array, vector, in PHP against entries, column values, in a MySQL table
Basically what I wanted to do was to check if all the elements in the array was in the MySQL table and if so update it and if it not existed enter the values into the table.
The table consisted of two columns id and frequency. The procedure would be update - Add one to frequency, insert - Add new id and set frequency to one.
Now I could do this with a foreach statement and probing the table for each element but as the array/vector was quite large that would mean a lot of SELECTS and calls to the database which didn't sound very efficient to me.
After I while i figured out a nice simple way, even though I don't know if it is the most efficient one. First update
UPDATE table_name SET col_name2 = col_name2 + 1 WHERE col_name1 IN ( id1, id2, id3, id4... )
UPDATE would ignore the col_name1 id's in the list not found in the table. Now we just have to put every entry that is not in the table into the database
INSERT INTO table_name ( col_name1, col_name2 ) VALUES ( id1, 1), ( id2, 1), ( id3, 1), ....
INSERT would then ignore the duplicate ids and add the other ones to the table.
A more efficient way if you have MySQL 4.1 is to use ON DUPLICATE KEY which efficiently reduces everything to
IINSERT INTO table_name (col_name1, col_name2) VALUES (id1,1),(id2,1),(id3,1)....
ON DUPLICATE KEY UPDATE col_name2=col_name2+1;
The only problem left is now to update every entry with different frequencies which gives away maybe to a foreach with INSERT ... ON DUPLICATE for every frequency.
The final code became this
<?php
# Create Query creator
$valueStrings = array();
# Get key and value by traversing with each, then put values
# into query string with the incremental value as the array index.
while ( list ( $word, $frequency ) = each ( this->$wordCountArray ) ) {
$valueStrings[$frequency] = $valueStrings[$frequency] . '(' . $word . ',' . $frequency . '),';
}
$query = "INSERT INTO table_name (col_name1, col_name2) VALUES %s" .
"ON DUPLICATE KEY UPDATE col_name2 = col_name2 + %d";
# Traverse array and execute queries (not forgetting to remove the last comma)
while ( list ( $frequency, $valueString ) = each ( $valueStrings ) ) {
db_query ( $query, substr ( $valueString, 0, -1 ), $frequency );
}
?>Which seems reasonable as the spread of frequencies can't be that big, at least not for words bigger than three letters, and therefore can't be that heavy on the database.