I have to store into MySQL from PHP quite a lot; usually the content to be stored I have in arrays. So I have written a small set of helper functions that saved me a lot of time.
This is the main function store that stores $array into MySQL $table:
function store($array, $table, $limit = 1000, $mode = '') {
if (empty($array)) return FALSE;
$sql_insert = "INSERT INTO `".$table."` (";
$array_keys = array_keys($array);
$keys = array_keys($array[$array_keys[0]]);
foreach ($keys as $key) $sql_insert .= "`".$key."`,";
$sql_insert = replaceEND($sql_insert, ') VALUES ', 1);
$count = 1;
$sql = $sql_insert;
foreach ($array as $array_num => $row) {
$sql .= '(';
foreach ($row as $key => $value) {
$string = $value;
if (!is_null($value)) {
$sql .= "'".str_replace("'", "\'", $string)."',";
} else {
$sql .= "NULL,";
}
}
$sql = replaceEND($sql, '),', 1);
$count = checkInsert($sql, $count, $limit);
if ($count == 0) {
if ($mode == 'PRINT') echo $sql.'<br />';
$sql = $sql_insert;
}
$count++;
}
$last = lastInsert($sql);
if ($last) {
if ($mode == 'PRINT') echo $sql.'<br />';
}
return TRUE;
}
I am using the following helpers within this function -
To add the correct ending to the statement strings:
function replaceEnd($string, $replacer = ';', $number_of_chars = 1) {
return left($string, strlen($string) - $number_of_chars) . $replacer;
}
To check, if $limit is reached which will lead to execution of the statement:
function checkInsert($sql, $sqlcount, $sql_limit) {
if ($sqlcount >= $sql_limit) {
$sql = replaceEND($sql);
query($sql);
$sqlcount = 0;
}
return $sqlcount;
}
To check for the last insert after the loop is finished:
function lastInsert($sql, $sql_insert = '') {
if (right($sql, 1) != ';') {
if ($sql != $sql_insert) {
$sql = replaceEND($sql);
query($sql);
return TRUE;
}
}
return FALSE;
}
Finally, to execute my statements:
function query($sql) {
$result = mysql_query($sql);
if ($result === FALSE) {
## Error Logging
}
return $result;
}
Basically, there is nothing wrong with these functions. But there is one problem, that I just could not solve: As you see, $limit is set to 1000 by default. This is usually perfectly fine, but when it comes to insert statements with much content / many characters per line of the insert statement, I have to reduce $limit manually in order to avoid errors. My question: does anyone know a way to automatically check if the current insert statement has reached the maximum in order to be executed correctly without wasting time by executing it too early? This would solve two problems at once: 1. I could get rid of $limit and 2. It would minimize the execution time needed to store the array into MySQL. I have just not found any approach to check the maximum possible length of a string that represents a MySQL insert statement, that I could apply before executing the statement...!?
