Jim R

$players = " INSERT IGNORE INTO a_players (nameFirst,nameLast,feet,inches,schoolID,grade,level,varsity) SELECT nameFirst , nameLast , feet , inches , school_id , grade , level , varsity FROM temp_csv;"; $roster = "INSERT INTO a_rosters(schoolID, playerID, uniform, varsity, season) SELECT t.schoolID, t.playerID t.uniform, t.varsity, t.season FROM temp_csv t JOIN a_players p ON t.nameFirst = p.nameFirst AND t.nameLast = p.nameLast AND t.schoolID = p.schoolID"; This is what I have, just renaming the table names. It didn't input the single entry I submitted.

This isn't working on what is coming from the form. I don't see how it could, since I'm not uploading a .csv file.

How does that handle the arrays being created?

I still don't see how it handles the array, and it won't add duplicates the Player Table anyway, because I have a duplicate key set up. The issue was just getting the playerID from the players already in the Player Table vs. insert_id. It was inserting the player to the Roster Table, but it was passing the playerID as 0 since insert_id wasn't 'used' on that row, and I didn't have anything in there to go get it. Changing gears on how to handle it isn't my strong suit. 😉

Get rid of the $player = $con->prepare and $player->bind_param lines? I'm thoroughly confused as to how it will handle the arrays being sent.

I have to do a separate INSERT from from temp_csv to Player Table, right? Especially since (from what I've read) the load data local infile doesn't input directly into multiple tables.

I have to do a separate INSERT from from temp_csv to Player Table, right? Especially since (from what I've read) the load data local infile doesn't input directly into multiple tables.

This is separate from the load data local. This is coming from the repeatable fields form.

$roster = $con->prepare("INSERT INTO a_rosters(schoolID, playerID, uniform, varsity, season) SELECT t.schoolID, t.playerID t.uniform, t.varsity, t.season FROM temp_csv t JOIN a_players p ON t.nameFirst = p.nameFirst AND t.nameLast = p.nameLast AND t.schoolID = p.schoolID"); In the FOREACH loop: $player->execute(); $roster->execute(); <-- line 72 Threw this error:

I want to make sure I'm clear on something before I dig into it. The above query is replacing the query in this statement only? $roster = $con->prepare("INSERT INTO a_rosters(schoolID, playerID, uniform, varsity, season) VALUES (?,?,?,?,?)"); $roster->bind_param('sssss', $schoolID, $playerID, $uniform, $varsity, $season);

Ok...thanks. I'm in and out until this afternoon, so I'll have time to sit down to this later.

{Soaking that in my brain.} I logged back in to clarify something that I'm not sure I expressed earlier. When a player is entered into table a_roster, I have successfully, with your help, set up that it INSERTS the newly created playerID from table a_players. However, while it won't enter a duplicate row in a_player, it doesn't port over the playerID from the player already in a_player. Does what you diagramed above solve that? I apologize for not realizing that earlier.

I was able to put $con->affected_rows; in a place that would return a 1, but I couldn't figure out how to create a loop with it. It was either a 1 or -1. I moved over to try $con->num_rows, but it's late, and I'm heading to bed. Currently, this is part of the FOREACH loop: $player->execute(); if(($con->num_rows) < 1) { echo $con->num_rows; exit(); } $playerID = $con->insert_id; $roster->execute(); $stmt2->execute(); $termID = $con->insert_id; $stmt3->execute();

Tried echo $con->affected_rows; to see what it returned. It's a -1, which means an error.

Like maybe ON DUPLICATE KEY.....stop....(it creates zero rows) then somehow tie the lack of rows created to the rest of the queries?

I have a duplicate key set up on a_players. Can I wrap everything that comes after the $player query in an IF condition?

Never mind...suddenly it worked. Is there a reason it would go from submitting 0's to suddenly working?

.edit (thought I figured out why it wasn't working, but I was wrong at that too)

I can't seem to get a value other than 0. I've tried various version of subquery, including... $playerID = $con->query("SELECT max(id) FROM a_players"); I keep going back to what's below. $playerID is what I'm looking for: $player = $con->prepare("INSERT INTO a_players(schoolID,nameFirst,nameLast,feet,inches,grade,position,varsity) VALUES (?,?,?,?,?,?,?,?)"); $player->bind_param('ssssssss',$schoolID,$fname,$lname,$feet,$inches,$grade,$position,$varsity); $roster = $con->prepare("INSERT INTO a_rosters(schoolID, playerID, uniform, varsity, season) VALUES (?,?,?,?,?)"); $roster->bind_param('sssss', $schoolID, $playerID, $uniform, $varsity, $season); This is the same FOREACH loop you had above: $player->execute(); $playerID = $con->insert_id; $roster->execute(); No errors. Just getting a 0.

It helps eliminate steps later for sure, as I thought of ways to better use the complete list. I double checked that. But then since you think that's the issue, I triple checked it. That was the issue. In my players table, it's schoolID. I figured it was something easy I overlooked. I changed all my columns to schoolID across those tables. Last step of converting this...getting the playerID from the players table to the rosters table. I figured that would be the hard part, and I've been reading more on subqueries. I want to figure that one out. I'm sure I'll have a question...later. Hope not. Taking laptop to dinner.

Grrrr...why is this throwing an error? It's pretty much the same line as above, but I changed the variable name, changed the table it goes into and deleted two variables: $player = $con->prepare("INSERT INTO a_players(team,nameFirst,nameLast,feet,inches,grade,position,varsity) VALUES (?,?,?,?,?,?,?,?)"); $player->bind_param('ssssssss',$team,$fname,$lname,$feet,$inches,$grade,$position,$varsity); <- line 17

Applied to $stmt3, it worked. Thank you, sir!

Oops...hold on. I had messed around with $stmt4 before you replied, then didn't go back when I got back home to test it. Let me apply what you suggested to $stmt3.

It's still posting 0 as the term_id $team = $_POST['school']; $level = $_POST['level']; $season = $_POST['season']; // This uploads it into the Rosters table $stmt = $con->prepare("INSERT INTO a_rosters(team, uniform, nameFirst, nameLast, feet, inches, level, season, grade, position) VALUES (?,?,?,?,?,?,?,?,?,?)"); $stmt->bind_param('ssssssssss', $team, $uniform, $fname, $lname, $feet, $inches, $level, $season, $grade, $position); $stmt2 = $con->prepare("INSERT INTO wp_terms(name, slug) VALUES (?,?)"); $stmt2->bind_param('ss', $name, $slug); $stmt3 = $con->prepare("INSERT INTO wp_term_taxonomy(taxonomy) VALUE (?)"); $stmt3->bind_param('s',$taxonomy); $stmt4 = $con->prepare("UPDATE wp_term_taxonomy SET taxonomy = ? WHERE term_id = 0"); $stmt4->bind_param ('s',$termID); 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]; $name = "$fname $lname"; $slug = strtolower("$fname-$lname"); $taxonomy = "post_tag"; $stmt->execute(); $stmt2->execute(); $termID = $con->insert_id; $stmt3->execute(); $stmt4->execute();

Now I tried this: $termID = mysqli_insert_id($con); It input a 0, but when I printf ("New Record has id %d.\n", mysqli_insert_id($con)); it shows the correct ID