Barand Posted December 18, 2020 Share Posted December 18, 2020 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. Quote Link to comment https://forums.phpfreaks.com/topic/311751-trying-to-get-data-from-form-with-repeatable-fields-into-mysql/page/6/#findComment-1583248 Share on other sites More sharing options...
Jim R Posted December 18, 2020 Author Share Posted December 18, 2020 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. Quote Link to comment https://forums.phpfreaks.com/topic/311751-trying-to-get-data-from-form-with-repeatable-fields-into-mysql/page/6/#findComment-1583249 Share on other sites More sharing options...
Barand Posted December 19, 2020 Share Posted December 19, 2020 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 #" /> ' + '<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>'); }) }) </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> Quote Link to comment https://forums.phpfreaks.com/topic/311751-trying-to-get-data-from-form-with-repeatable-fields-into-mysql/page/6/#findComment-1583254 Share on other sites More sharing options...
Jim R Posted December 19, 2020 Author Share Posted December 19, 2020 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. Quote Link to comment https://forums.phpfreaks.com/topic/311751-trying-to-get-data-from-form-with-repeatable-fields-into-mysql/page/6/#findComment-1583257 Share on other sites More sharing options...
Jim R Posted December 19, 2020 Author Share Posted December 19, 2020 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. Quote Link to comment https://forums.phpfreaks.com/topic/311751-trying-to-get-data-from-form-with-repeatable-fields-into-mysql/page/6/#findComment-1583259 Share on other sites More sharing options...
Barand Posted December 19, 2020 Share Posted December 19, 2020 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) Quote Link to comment https://forums.phpfreaks.com/topic/311751-trying-to-get-data-from-form-with-repeatable-fields-into-mysql/page/6/#findComment-1583264 Share on other sites More sharing options...
Jim R Posted December 19, 2020 Author Share Posted December 19, 2020 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? Quote Link to comment https://forums.phpfreaks.com/topic/311751-trying-to-get-data-from-form-with-repeatable-fields-into-mysql/page/6/#findComment-1583269 Share on other sites More sharing options...
Barand Posted December 19, 2020 Share Posted December 19, 2020 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. Quote Link to comment https://forums.phpfreaks.com/topic/311751-trying-to-get-data-from-form-with-repeatable-fields-into-mysql/page/6/#findComment-1583271 Share on other sites More sharing options...
Jim R Posted December 20, 2020 Author Share Posted December 20, 2020 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. Quote Link to comment https://forums.phpfreaks.com/topic/311751-trying-to-get-data-from-form-with-repeatable-fields-into-mysql/page/6/#findComment-1583272 Share on other sites More sharing options...
Barand Posted December 20, 2020 Share Posted December 20, 2020 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. Quote Link to comment https://forums.phpfreaks.com/topic/311751-trying-to-get-data-from-form-with-repeatable-fields-into-mysql/page/6/#findComment-1583275 Share on other sites More sharing options...
Jim R Posted December 21, 2020 Author Share Posted December 21, 2020 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/311751-trying-to-get-data-from-form-with-repeatable-fields-into-mysql/page/6/#findComment-1583308 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.