php - Explode string and insert into multiple rows with foreach is too slow

Question

I want to explode a string with a space, check whether the word already exists. If not, insert each piece into multiple rows in mysql db. I've tried this before...

<?php
if($_SERVER['REQUEST_METHOD'] == 'POST') {
    include("connect.php");
    $counter = 0;
    $counters = 0;
    $string = mysql_real_escape_string($_POST['words']);
    $arr = explode(" ",$string);
    foreach($arr as $str) {
        $sql = mysql_query("SELECT * FROM unicode WHERE word = '$str'") or die (mysql_error());
        if (mysql_num_rows($sql) == 0) {
            $sqli = mysql_query("INSERT INTO unicode (word) VALUES ('$str')") or die (mysql_error());
            $counters++;
        } elseif (mysql_num_rows($sql) > 0) { 
            $counter++;
        }
    }
    header("Location: ../addspellwords?success=457394056369&entered=$counters&duplicates=$counter");
}
?>

This is too too too much slow....

Is there any other way to do this?

Thanks.

Can the word column in table unicode be unique?

Yan Berk
– Yan Berk

2012-06-08 11:16:33 +00:00
Commented Jun 8, 2012 at 11:16 — Yan Berk
– Yan Berk, Commented Jun 8, 2012 at 11:16
You have an extra } at the end. Is it intended?

Praveen Kumar Purushothaman
– Praveen Kumar Purushothaman

2012-06-08 11:28:30 +00:00
Commented Jun 8, 2012 at 11:28 — Praveen Kumar Purushothaman
– Praveen Kumar Purushothaman, Commented Jun 8, 2012 at 11:28
this is end of the server request @PraveenKumar

shahbaz
– shahbaz

2012-06-08 11:30:03 +00:00
Commented Jun 8, 2012 at 11:30 — shahbaz
– shahbaz, Commented Jun 8, 2012 at 11:30
Okay, I got it now. Sorry, my mistake! :)

Praveen Kumar Purushothaman
– Praveen Kumar Purushothaman

2012-06-08 11:31:12 +00:00
Commented Jun 8, 2012 at 11:31 — Praveen Kumar Purushothaman
– Praveen Kumar Purushothaman, Commented Jun 8, 2012 at 11:31

Aaron W. · Accepted Answer · 2012-06-08 11:35:31Z

1

Based on what's passed you can go SELECT the words that currently exist, then leave those out on the INSERT. Another option may be INSERT...ON DUPLICATE KEY if your word column is a key (which I hope based on your code). Try the following:

<?php
if($_SERVER['REQUEST_METHOD'] == 'POST') {
    include("connect.php");
    $counter = 0;
    $counters = 0;
    $string = mysql_real_escape_string($_POST['words']);
    $arr = explode(" ",$string);

    $sql = mysql_query("SELECT `word` FROM unicode WHERE word IN ('".implode("', '", $arr) . ")") or die (mysql_error());
    $dupes = array();
    while($r = mysql_fetch_assoc($sql) {
        $dupes[] = $r['word'];
    }
    $newwords = array_diff($arr, $dupes);
    if(count($newwords)) {
        $sqli = mysql_query("INSERT INTO unicode (word) VALUES ('" . implode("'),('", $newwords) . "')") or die (mysql_error());
    }
    header("Location: ../addspellwords?success=457394056369&entered=".count($newwords)."&duplicates=".count($dupes));
}
?>

edited Jun 8, 2012 at 11:35

answered Jun 8, 2012 at 11:30

Aaron W.

9,2952 gold badges36 silver badges47 bronze badges

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

1 Comment

h00ligan Over a year ago

You could even improve it slightly by using array_unique() on the $arr array

Collectives™ on Stack Overflow

php - Explode string and insert into multiple rows with foreach is too slow

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related