Insert multiple rows into DB by only clicking one submit button

Question

Hey Folk

I've got a little problem i hope you're able to help me fix.

First of all my DB table that is involved look like this:

+---------------+--------------+------+-----+-------------------+----------------+
| Field         | Type         | Null | Key | Default           | Extra          |
+---------------+--------------+------+-----+-------------------+----------------+
| id            | int(11)      | NO   | PRI | NULL              | auto_increment |
| KlubID        | varchar(11)  | NO   |     | 0                 |                |
| Medlemsnummer | varchar(11)  | NO   |     | 0                 |                |
| KlubType      | varchar(128) | NO   |     |                   |                |
| Handling      | varchar(128) | NO   |     |                   |                |
| Tidspunkt     | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+---------------+--------------+------+-----+-------------------+----------------+

Say i got X entries where "Handling" equals Y in my table lets call the results P . Now I would like to take all P and insert a row foreach P where the value of "Handling" now will be equal to Q.

The problem here is that I want to do the insert with one button (submit) not multiple forms.

In advance thank you very much for your help. Feel free to ask for more info if this needs more clarity.

/Nicki

EDIT: For more clarity

public static function find_todays_children() {
    global $db;
    $sql = "SELECT * 
            FROM (
                SELECT * 
                FROM Handlinger 
                AS a
                WHERE date(Tidspunkt) = curdate() 
                AND Tidspunkt = (
                    SELECT max(Tidspunkt) 
                    FROM Handlinger 
                    AS b
                    WHERE a.Medlemsnummer = b.Medlemsnummer
                    )
                )
            AS c
            ORDER BY handling DESC, medlemsnummer";
    return static::find_by_sql($sql);
}

This query above returns the following.

+------+--------+---------------+----------+----------+---------------------+
| id   | KlubID | Medlemsnummer | KlubType | Handling | Tidspunkt           |
+------+--------+---------------+----------+----------+---------------------+
| 5786 | 0      | 1             | FK       | Kommet   | 2010-10-06 13:48:06 |
| 5787 | 0      | 2             | FK       | Kommet   | 2010-10-06 13:48:10 |
| 5789 | 0      | 4             | FK       | Kommet   | 2010-10-06 13:48:16 |
| 5790 | 0      | 3             | FK       | G?et     | 2010-10-06 13:48:27 |
+------+--------+---------------+----------+----------+---------------------+

I then want to be able to insert 3 rows where the field "Handling" is another value. I can do this by HTML form with PHP but I can't figure out how to do it all by only clickin once... The important thing is that not to insert anything where the latest entry for a specific "Medlemsnummer" (user_id) is already equal to the value "Gået"

My form looks like this:

...
    if(isset($action->Handling) != "Kommet") {
        $do_action = "Kommet";
    } else {
        $do_action = "Gået";
    }
...

<section>
    <form action="phineaslog_barn.php" method="post">
        <label for="Status">Klub Navn: </label>
        <input type="text" name="Medlemsnummer" value="<?php echo $child->Medlemsnummer; ?>" />
        <input type="text" name="Handling" value="<?php echo $do_action; ?>" />
        <input type="submit" name="submit" value="<?php echo $do_action; ?>" />
    </form>
</section>

Answer 1

You could do something like this:

Write a Method to return a DataTable
Ex:
private DataTable pResutls()
{
do your select handling=Y;
return DataTableWithResult;
}



in your button click event

DataTable p = pResutls();
foreach(DataRow row in p)
{ if (row["Handling"].toSring()=='Q')
{

then insert this row (write your method to insert)
}

}
by this way you will need a method to get the Results (handling=='y')
a method that insert a row (you could pass a DataRow as parameter and perform the update from there)
and then from your Button you can do everything in one single Click like above.

Answer 2

In essence, you want to run this query?

INSERT INTO tablename (KlubID,Medlemsnummer,KlubType,Handling,Tidspunkt)
SELECT KlubID,Medlemsnummer,KlubType,'Gået',NOW()
FROM tablename
WHERE Handling = 'Kommet';

Or, more in PHP:

$from = $_POST['Handling'] == 'Kommet' ? 'Kommet' : 'Gået';
$to   = $from == 'Kommet' ? 'Gået' :'Kommet';

mysql_query("
INSERT INTO tablename (KlubID,Medlemsnummer,KlubType,Handling,Tidspunkt)
SELECT t1.KlubID,t1.Medlemsnummer,t1.KlubType,'$to',NOW()
FROM tablename t1
LEFT JOIN tablename t2
ON t1.Medlemsnummer = t2.Medlemsnummer
AND t2.Tidspunkt > t1.Tidspunkt
WHERE DATE(t1.Tidspunkt) = CURDATE() AND t1.Handling = '$from' AND t2.id IS NULL");

Or are there more restrictions on which rows you'd like to insert besides Handling?

Answer 3

My understanding of the question:

• For each row which is the final entry for a user, where handling = kommet

  • Insert another row with handling = gået, tidspunkt = now()

So... to combine the best of two worlds...

INSERT INTO tablename (KlubID,Medlemsnummer,KlubType,Handling,Tidspunkt)
    SELECT KlubID,Medlemsnummer,KlubType,'Gået',NOW()
    FROM Handlinger 
    AS a
    WHERE date(Tidspunkt) = curdate() 
    AND Handling = 'Kommet'
    AND Tidspunkt = (
        SELECT max(Tidspunkt) 
        FROM Handlinger 
        AS b
        WHERE a.Medlemsnummer = b.Medlemsnummer
     )