nickifrandsen nickifrandsen - 4 years ago 92
SQL Question

Insert multiple rows into DB by only clicking one submit button

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 Source

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
     )
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download