How to solve "array overflow", either in php or using MySQL?
Prior to the question How to use php to get a unique word (string) pair and insert into a mysql table, you should first read
eg: if we have a pair dog cat
, we will not seecat dog
As @pala_ suggestion here is my code
$sql= "INSERT INTO EM (source,target) VALUES ";
$res = array();
foreach($combine_words_array as $v1) {
foreach($combine_words_array as $v2) {
$t = array($v1, $v2);
asort($t);
if(!in_array($t, $res)){
$res[] = $t;
$sql.="('$t[0]','$t[1]'),";
mysql_query(substr($sql,0,-1));
}
}
}
and the question comes up, this array must be very huge, and MySQL insert stop in 540000
rows, any idea that can use dynamic array or together with MySQL code?
source to share
I still think you should keep this logic in SQL as such:
SELECT t1.column AS source, t2.column AS target FROM input_table t1
INNER JOIN input_table t2 ON t1.column < t2.column
This should give you all the unique pairs and should be faster than getting a unique pair with PHP in_array testing. if the data is already in PHP and not MySQL, you can insert rows 540000
at that time into a temporary table and then run something like above to get the pairs you are interested in. Databases are designed to work with a lot, PHP definitely isn't.
If you insist on creating an array the way you do, and keep the whole thing in PHP memory, you should only run the string mysql_query(substr($sql,0,-1));
if you've reached the limit of your string or ended an outer loop. At this point you can reset the $ sql string to
$sql= "INSERT INTO EM (source,target) VALUES ";
again and start building the remainder of the building as you have so far. Flush and repeat until you are done or PHP process is not working =)
source to share