在阵列中存储大量数据的最佳方法是什么？ [关闭]

Question

I have a database of 100,000 contacts.

I have a multiple select box which I would like to use to select contacts to add to a specific mailing list.

My problem is that I am currently storing the data for the mailing list in a serialized array format (PHP) in MySQL.

When I select over a certain number of contacts, something seems to break (I assume not enough memory) and does not update the array.

Is there a best way to store a large array in MySQL and is there a best way to keep memory usage in a PHP array() low?

Code Example

if(isset($_POST['add'])) {
    $name = $core->EscapeString($_POST['name']);
    $desc = $core->EscapeString($_POST['desc']);
    foreach($_POST['addSelect'] as $null => $id) {
        if(!in_array($id, $recipientArray)) {
            $recipientArray[] = $id;
        }
    }
    $contacts->updateML($lid, $name, $desc, serialize($recipientArray));
}
else if(isset($_POST['rm'])) {
    $name = $core->EscapeString($_POST['name']);
    $desc = $core->EscapeString($_POST['desc']);
    foreach($recipientArray as $null => $id) {
        foreach($_POST['rmSelect'] as $null1 => $id1) {
            if($id == $id1) {
                unset($recipientArray[$null]);
            }
        }
    }
    $contacts->updateML($lid, $name, $desc, serialize($recipientArray));
}

UpdateML Function

//Class 3, Function 16
function updateML($lid = '', $name = '', $desc = '', $recip = '') {
    global $MySQLi;
    $query = "UPDATE `mailing_lists` SET `name` = '".$name."', `desc` = '".$desc."', `recipients` = '".$recip."' WHERE `list_id` = '".$lid."' LIMIT 1";
    $commit = $MySQLi->query($query);
    if($commit == false) {
        die("Issues with the database were detected. Please email [e-mail] quoting error code: <strong>CLASS3/16.1</strong>.");
    }
    else
    {
        return true;
    }
}

Answer 1

You need 2 tables. One for Mailing List Definition, one for recipients. In recipients table you'd need to have a foreign key relating the record to appropriate mailing list.

so mailing_lists table looks like this:

ml_id
name
desc

and recipients looks like this:

r_id
email
ml_id

First add the mailing list to the database:

INSERT INTO mailing_lists SET name = 'my first list', desc = 'mailsareus'

When you add new recipients just add a new row to recipients table:

INSERT INTO recipients SET email = '[e-mail]', ml_id = 1

If you need to get all the recipients and definition from a mailing list just use a join

SELECT * FROM mailing_lists JOIN recipients ON recipients.ml_id = mailing_lists.ml_id