insert multiple rows same time

A PHP script is generating the following table panel based on the data from a mysql DB. Each cell of the table contains a checkbox which sets the access to some 'areas'. The name of my checkboxes are following a certain rule: u[user_id]a[area_name] and I was thinking to get all the $_POST's which are set and to save them in the data base with their values.

The nu开发者_如何学JAVAmber of the areas are fixed, but the number of the users varies by days. The table in which I have to save the status of the table is: user_id, current_date, area1, area2, area3, area4, area5, .. area25.

The problem is that I don't know how to save this table into the database. At this moment I do an insert for each user and I think it has to better solution because this takes too much time.

<form name="foo" method="post" action="saveme.php" />    
<table><tr>
    <td>name</td><td>area1</td><td>area2</td><td>area3</td><td>area4</td>
</tr>
<tr>
    <td>John Smith</td>
    <td><input type="checkbox" value="12" name="u1a1" checked /></td>
    <td><input type="checkbox" value="13" name="u1a2" /></td>
    <td><input type="checkbox" value="16" name="u1a3" /></td>
    <td><input type="checkbox" value="21" name="u1a4" checked /></td>
</tr>
</table><input type="submit" value="Save" /> </form>

---

I think this should do the trick. However, when I see fields like 'area1, area2, ... area25' - I have a tendency to think there may be a better solution. Perhaps the table should have the fields 'user_id, current_date, area_id, area' instead?

$users = array();
foreach ($_POST as $key => $value) {
    $matches = null;
    if (preg_match('/^u(\d+)a(\d+)$/', $key, $matches)) {
        $user_id = $matches[1];
        $area_id = $matches[2];
        if (!isset($users[$user_id))) {
            $users[$user_id] = array_fill(1, 25, 0);
        }
        $users[$user_id][$area_id] = mysql_real_escape_string($value);
    }
}
$values = array();

foreach ($users as $user_id => $areas) {
    $values[] = '(' . $user_id .', now(), '. implode(', ', $areas) .')';
}

if ($values) {
    $sql = 'INSERT INTO user_areas (user_id, current_date, area1, area2, area3, area4, area5, area6, area7, area8, area9, area10, area11, area12, area13, area14, area15, area16, area17, area18, area19, area20, area21, area22, area23, area24, area25) VALUES ' . implode(', ', $values);

    // execute $sql query
}

---

May be you need mysqli multiquery. Build query dynamically. Here an example straight from PHP manual. http://php.net/manual/en/mysqli.multi-query.php

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query  = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";

/* execute multi query */
if ($mysqli->multi_query($query)) {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->free();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}

/* close connection */
$mysqli->close();
?>