how to solve 'array overflow', in either php or using MySQL?

Question

Before the question , How to using php to get unique word pair(string) and insert into mysql table should read first

for example: if we have dog cat pair , we will not see cat dog

As @pala_ suggestion here 's 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 question appear, this array must very huge and MySQL insert stop at 540000 rows ,is any idea that could use array dynamic or together with MySQL code?

are the elements in array unique? I.e. there would never be two "dog" entries? Also are you sure that you want to do this in PHP? Where is the data coming from originally? If the array is coming from MYSQL it would be quicker to do it using it. — cr0atIAN
– cr0atIAN, Commented May 4, 2015 at 18:53
if all MySQL that would be easy but now data is very large and it should be compress or do something in PHP — yihang hwang
– yihang hwang, Commented May 5, 2015 at 9:12

cr0atIAN · Accepted Answer · 2015-05-05 14:50:11Z

2

I still think that 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

That should give you all of the unique pairings and should be faster than getting unique pair with in_array testing in PHP. if the data is already in PHP and not in MySQL you could insert it 540000 rows at the time into a temp table and then run something like above to get the pairs you are interested in. Databases are meant for set operation, PHP definitely isn't.

If you insist on building the array in the way that you are doing it and keeping it all in PHP memory you should run mysql_query(substr($sql,0,-1)); line only if you've hit your row limit or have ended the outer loop. At which point you could reset your $sql string to

$sql= "INSERT INTO EM (source,target) VALUES ";

again and start building the remaining portion as you have up until now. Rinse and repeat until you are done or PHP process is out of memory =)

answered May 5, 2015 at 14:50

cr0atIAN

98910 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

yihang hwang Over a year ago

Actually , the rows need to insert must be 379200*379200,what a such large ,I found the reason stop at 54000 rows must php.ini memory_limit setting ,if i need to insert and using MySQL or solve after php coding,that should really need a big hard disk or memory...

cr0atIAN Over a year ago

I wish I could help you more but I can't make further suggestions without understanding your goals and the reasons why you need this much data in memory in PHP better.

Collectives™ on Stack Overflow

how to solve 'array overflow', in either php or using MySQL?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related