0

I got this code

$i             = -1;
$random_string = array();
while (sizeof($random_string) < 1600000) {
    $i++;
    $zmienna           = generatePassword();
    if (!in_array($zmienna, $random_string))
        $random_string[$i] = $zmienna;
    else
        continue;
}
//print_r($random_string);

foreach ($random_string as $value) {
    $sql = "INSERT INTO `kody`(`kod`) VALUES ('$value')";
    mysql_query($sql, $con);
}

But it will take a lot of hours to insert it to databse, or even to array. Do someone know how to improve this code?

Improve this question
3
  • Not an answer to your question, but.. don't use the mysql_ functions! They are deprecated, unmaintained and will be removed in a future version of PHP. Commented Aug 23, 2012 at 21:24
  • 1
    It may not help answer your question, but you should stop using mysql_* functions. They're being deprecated. Instead use PDO (supported as of PHP 5.1) or mysqli (supported as of PHP 4.1). If you're not sure which one to use, read this article. Commented Aug 23, 2012 at 21:25
  • Rocket and Adrian's answers are both correct. Commented Aug 23, 2012 at 21:25

3 Answers 3

Reset to default
2

Well, in_array() is rather expensive. Use a hash instead of a simple array, and then you can use isset() instead of in_array().

Also, don't use things like sizeof() and count() as loop conditions. Instead, just use a simple for ($i = 0; $i < 1600000; ++$i) { ... } array.

Depending on your web host permissions, another significant optimization would be to use fputcsv() to write your array to disk and then make use of MySQL's LOAD DATA INFILE to load the contents into your database, instead of generating 1.6 million queries.

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

3 Comments

I'm not sure, but you may not have permission to use LOAD DATA INFILE. Depends on your web host.
@Rocket: Thanks, that's a very good point. I'll add a note about it.
could you tell me how use hash?Becouse I didn't use it ever
1

Yes, use one query to insert all of them at once with an SQL multi-insert:

$values = "('" . implode( "'), ('", $random_string) . "')";
$sql="INSERT INTO `kody`(`kod`) VALUES " . $values;
mysql_query($sql,$con);

As drrcknlsn very correctly points out, in_array() is inefficient, as it performs a linear O(n) search on the array. Here is how you can fix that (which is a hash implementation):

while( sizeof($random_string) < 1600000) {
    $i++;
    $zmienna = generatePassword();
    if( !isset( $random_string[$zmienna]))
        $random_string[$zmienna] = $zmienna;
    else
        continue;
}

Now, you can use the above code to generate a single SQL query, and this should run much, much faster.

Improve this answer

1 Comment

Careful with this - if the size of the query string exceeds max_allowed_packet, things are going to go boom. Doing multi-value inserts is definitely more efficient, but you have to worry about going too far.
0

The problem is probably that it's trying to update the INDEX after each insert. Try using transactions. This will only update the INDEX once (after COMMIT) is called. This will also let you ROLLBACK if something goes wrong.

mysql_query("SET AUTOCOMMIT=0");
mysql_query("START TRANSACTION");

foreach($random_string as $value)
{
$sql="INSERT INTO `kody`(`kod`) VALUES ('$value')";
   mysql_query($sql,$con);
}

mysql_query("COMMIT");
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.