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?
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 =)