Trying to get data from form with Repeatable fields into MySQL...

[Barand]

  On 12/18/2020 at 8:57 PM, Jim R said:

 It seems easier to have their varsity status in that table than having to join a_rosters

Expand  

Then the sole reason for your making a dog's breakfast out of your DB design is purely laziness?

I think I'll be lazy too and go and watch some TV.

[Jim R]

  On 12/18/2020 at 9:01 PM, Barand said:

Then the sole reason for your making a dog's breakfast out of your DB design is purely laziness?

I think I'll be lazy too and go and watch some TV.

Expand  

Not even remotely out of laziness.  

The query just gets written once, whether I have to join it or not.  It's already written.  The viewing of data will be utilized far more times than the inserting of it.  It gets inserted/updated once a year, vs. viewed dozens to hundreds of times a day.  

 

It certainly has nothing to do with the issue at hand.  

[Barand]

I decided to get it working using your method and, as a comparison, created a separate pair of tables (player2, roster2) into which I add the data using my method. I timed the processing when adding 2, 10 and 20 records to the roster. Data results were identical in both cases. Processing times (as expected) were different.

The code...

<?php
if ($_SERVER['REQUEST_METHOD']=='POST') {
        $schoolID = $_POST['school']; 
        $varsity = $_POST['varsity']; 
        $season = $_POST['season'];
        
##
## Jimr mysqli code
##
        $jimt1 = microtime(1);
                
        $player = $con->prepare("INSERT INTO player (schoolID,nameFirst,nameLast,feet,inches,grade,position) 
                                VALUES (?,?,?,?,?,?,?)
                                ON DUPLICATE KEY UPDATE
                                    feet = VALUES(feet),
                                    inches = VALUES(inches),
                                    grade = VALUES(grade),
                                    position = VALUES(position)
                                ");
        $player->bind_param('issiiis', $schoolID,$fname,$lname,$feet,$inches,$grade,$position);
    
    
        $pid = $con->prepare("SELECT id FROM player WHERE nameFirst = ? AND nameLast = ? AND schoolID = ?");
        $pid->bind_param('ssi',$fname,$lname,$schoolID);
             
        $roster = $con->prepare("INSERT INTO roster (schoolID, playerID, uniform, varsity, season) 
                                  VALUES (?,?,?,?,?)
                                  ");
        $roster->bind_param('sissi', $schoolID, $playerID, $uniform, $varsity, $season);


        foreach ($_POST['uniform'] as $k => $uniform)  {
            $fname = $_POST['nameFirst'][$k];
            $lname = $_POST['nameLast'][$k];
            $feet = $_POST['feet'][$k];
            $inches = $_POST['inches'][$k];
            $grade = $_POST['grade'][$k];
            $position = $_POST['position'][$k];

            $player->execute();
            $pid->execute();
            $pid->bind_result($playerID);
            $pid->fetch();
            $pid->reset();
            $roster->execute();
        }
        $jimt2 = microtime(1);
        printf("\nJIMR time to process: %0.3f seconds\n\n", $jimt2 - $jimt1);
        
##
##  My PDO method
##
        $t1 = microtime(1);
        $db->exec("CREATE TEMPORARY TABLE temp_csv (
                    id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    schoolid int(11) DEFAULT NULL,
                    varsity varchar(10) DEFAULT NULL,
                    season int,
                    uniform varchar(45) DEFAULT NULL,
                    nameFirst varchar(45) DEFAULT NULL,
                    nameLast varchar(45) DEFAULT NULL,
                    feet tinyint(4) DEFAULT NULL,
                    inches tinyint(4) DEFAULT NULL,
                    grade int(11) DEFAULT NULL,
                    position varchar(15) DEFAULT NULL
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
                  ");
        
        $sql = "INSERT INTO temp_csv (schoolid, varsity, season, uniform, nameFirst, nameLast, feet, inches, grade, position) VALUES \n";
        $holders = $vals = [];          
        foreach ($_POST['uniform'] as $k => $uniform)  {
            $fname = $_POST['nameFirst'][$k];
            $lname = $_POST['nameLast'][$k];
            $feet = $_POST['feet'][$k];
            $inches = $_POST['inches'][$k];
            $grade = $_POST['grade'][$k];
            $position = $_POST['position'][$k];
        
            $holders[] = "(?,?,?,?,?,?,?,?,?,?)";
            array_push($vals, $schoolID, $varsity, $season, $uniform, $fname, $lname, $feet, $inches, $grade, $position);
        }
        $stmtc = $db->prepare($sql . join(',', $holders));
        $stmtc->execute($vals);
        
        $db->exec("INSERT IGNORE INTO player2
                           (schoolID,nameFirst,nameLast,feet,inches,grade,position)
                    SELECT schoolid
                         , nameFirst
                         , nameLast
                         , feet
                         , inches
                         , grade
                         , position
                    FROM temp_csv
                    ");
                    
        $db->exec("INSERT IGNORE INTO roster2 (schoolID, playerID, uniform, varsity, season)
                    SELECT t.schoolid
                         , p.id
                         , t.uniform
                         , t.varsity
                         , t.season
                    FROM temp_csv t
                         JOIN player2 p ON t.nameFirst = p.nameFirst
                                        AND t.nameLast = p.nameLast;
                    ");
        $t2 = microtime(1);
        printf("\nMy time to process: %0.3f seconds\n\n", $t2 - $t1);
       
}
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-language" content="en">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.15.1/css/all.css">
<title>Example</title>
<meta name="creation-date" content="11/23/2020">
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.0/jquery.min.js"></script>
<script type='text/javascript'>
    $().ready( function() {
        $("#add-player").click( function(e) {
            e.preventDefault()
            $("#myRepeatingFields").append('<div class="entry input-group col-xs-3">' +
                    '<input class="form-control" name="uniform[]" type="text" placeholder="Uni #" />&nbsp;' +
                    '<input class="form-control" name="nameFirst[]" type="text" placeholder="First Name" />&nbsp;' +
                    '<input class="form-control" name="nameLast[]" type="text" placeholder="Last Name" />&nbsp;' +
                    '<input class="form-control" name="feet[]" type="text" placeholder="Feet" />&nbsp;' +
                    '<input class="form-control" name="inches[]" type="text" placeholder="Inches" />&nbsp;' +
                    '<input class="form-control" name="grade[]" type="text" placeholder="Grade" />&nbsp;' +
                    '<input class="form-control" name="position[]" type="text" placeholder="Position" />' +
                    '</div>');
        })
    })
</script>

</head>
<body>

<form action="" method="post">
<fieldset>
<legend>Roster</legend>
School ID <input type="text" name="school"><br>
Varsity <input type="text" name="varsity"><br>
Season <input type="text" name="season"><br>
</fieldset>
<fieldset><br>
<legend>Players</legend>
    <div id="myRepeatingFields">
        <div class="entry input-group col-xs-3">
            <input class="form-control" name="uniform[]" type="text" placeholder="Uni #" />
            <input class="form-control" name="nameFirst[]" type="text" placeholder="First Name" />
            <input class="form-control" name="nameLast[]" type="text" placeholder="Last Name" />
            <input class="form-control" name="feet[]" type="text" placeholder="Feet" />
            <input class="form-control" name="inches[]" type="text" placeholder="Inches" />
            <input class="form-control" name="grade[]" type="text" placeholder="Grade" />
            <input class="form-control" name="position[]" type="text" placeholder="Position" />
        </div>
    </div>
</fieldset><br>    
    <button id='add-player' title='add new player entry'><i class='fas fa-plus-circle'></i></button>
    <input type="submit" value="Send Roster" name="submit">
</form>

</body>
</html>

 

[Jim R]

I do appreciate your time.  

 

1)  There is a decent amount of code amid the INSERT IGNORE you had not presented yet, which likely answers the question I asked before of how does it account for the arrays.  I don't believe you answered that, and toward that I wasn't sure where to apply that code.  I'm presuming, "holders" and "array_push" answer that question.  

 

2)  I have no doubt my code is often sloppy and piecemeal.  

 

3)  How do the INSERT IGNOREs operate?  Do they only ignore the entries that match the presented fields entirely?  If so, I don't think that will work for a few reasons:

• On a_players, the heights could and often do change from season to season.  Any change in height would cause the insertion of the same player.  
• On a_rosters, there will be duplicate players entered, once for each season they're on varsity.  I have a DUPLICATE KEY set up to check for schoolID, playerID and season.

 

I would like cleaner code.  I'll have a chance to dig into this later today.

Thanks. 

[Jim R]

Another issue with INSERT IGNORE is it has never worked for me.  

 

		$stmt2 = $con->prepare("INSERT IGNORE INTO wp_terms(name, slug) VALUES (?,?)");
        $stmt2->bind_param('ss', $name, $slug);

We also had that earlier in this discussion -- adding WordPress slugs.  Each time I test out entering Jon Doe into a_players and a_rosters, it's added a new row in wp_terms with the same values.  

[Barand]

  On 12/19/2020 at 2:59 PM, Jim R said:

How do the INSERT IGNOREs operate?

Expand  

Just like it says in the manual.

  On 12/19/2020 at 2:59 PM, Jim R said:

 I'm presuming, "holders" and "array_push" answer that question

Expand  

The query is a multiple insert like this, which insert 3 records with a single insert query, and is many times faster than several single record inserts.

INSERT INTO tableX (colA, colB) VALUES (1, 'A'), (2, 'B'), (3, 'C');

Because it's a prepared query it looks a little different though - the $holders would be the array

[ "(?,?)","(?,?)", "(?,?)" ]

and the $vals contain

[ 1, 'A', 2, 'B', 3, 'C' ]

(ie a value for each of the placeholders)

[Jim R]

  On 12/19/2020 at 5:49 PM, Barand said:

Just like it says in the manual.

Expand  

That didn't answer my questions, nor address my concerns over it.  If I could derive understanding from what I had already read, I wouldn't ask questions.  So either i'm misunderstanding its application, or it's not working for my needs.  

 

It would be like if I handed you a book of how to coach basketball and sat back watched you try to do that.  

 

Do they only ignore the entries that match the presented fields entirely? 

[Barand]

If you use the ignore modifier, ignorable errors that occur while executing the insert statement are ignored.
For example, without IGNORE, a row that duplicates an existing unique index or primary key value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs.

[Jim R]

  On 12/19/2020 at 11:35 PM, Barand said:

If you use the ignore modifier, ignorable errors that occur while executing the insert statement are ignored.
For example, without IGNORE, a row that duplicates an existing unique index or primary key value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs.

Expand  

It's really just a yes or no question, but ok.  

Let's pretend I understand all of it.  That means it has to trigger a unique or duplicate key, and I don't want that in a general way for the reasons I cited above.  

 

[Barand]

  On 12/19/2020 at 10:58 PM, Jim R said:

It would be like if I handed you a book of how to coach basketball and sat back watched you try to do that.

Expand  

One option would be to take your approach - totally ignore the book then every day get on the phone to you saying "Hey, get your arse down to the court and coach these kids for me".

  On 12/19/2020 at 10:58 PM, Jim R said:

or it's not working for my needs.

Expand  

And those needs would be what?

 

  On 12/19/2020 at 10:58 PM, Jim R said:

Do they only ignore the entries that match the presented fields entirely? 

Expand  

Define "entirely". If the combination of (firstName, LastName) is defined as unique then it will only allow one record. If you have "Jon Doe" on the team it won't allow another "John Doe" to be added. With IGNORE it ignores the second John Doe and continues adding the rest of the players' records. Without the ignore the query would fail with an error.

[Jim R]

  On 12/20/2020 at 1:32 PM, Barand said:

Define "entirely". If the combination of (firstName, LastName) is defined as unique then it will only allow one record. If you have "Jon Doe" on the team it won't allow another "John Doe" to be added. With IGNORE it ignores the second John Doe and continues adding the rest of the players' records. Without the ignore the query would fail with an error.

Expand  

So just unique keys set up in the schema?  

I though you meant all the selected fields:

schoolid , nameFirst , nameLast , feet , inches , grade , position

 

Your answer was way more succinct than what the manual provided.  

Trust me, you can ignore most coaching books.  

Edited December 21, 2020 by Jim R