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

[Barand]

2 minutes ago, Jim R said:

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

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]

1 minute ago, 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.

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]

2 hours ago, Jim R said:

How do the INSERT IGNOREs operate?

Just like it says in the manual.

2 hours ago, Jim R said:

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

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]

4 hours ago, Barand said:

Just like it says in the manual.

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]

27 minutes ago, 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.

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]

14 hours ago, 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.

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".

14 hours ago, Jim R said:

or it's not working for my needs.

And those needs would be what?

 

14 hours ago, Jim R said:

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

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]

12 hours ago, 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.

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