Jim R

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

I figured a little more out: $termID = "SELECT LAST_INSERT_ID()"; It at least doesn't throw an error. It input a zero though.

I have built on this, as I mentioned above (thinking it would be easier), trying to write a third table. In table wp_term_taxonomy, a new row is created when wp_terms adds one. It tells what kind of "term" was added. For my purposes, it's always "post-tag", and it links the id from wp_term, which always matches the id in wp_term_taxonomy. I need to duplicate that term_id as a I add a row. wp_term_taxonomy columns: (Only 1294 is what I inserted, but the previous rows shows you what it should be) Here is the code I'm using (built off of what we have above). Trying to use lastInsertID(). Right now, we're just looking at $stmt3 variables ($taxonomy, $termID) $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,term_id) VALUE (?,?)"); $stmt3->bind_param('ss',$taxonomy,$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"; $termID = $con->lastInsertID(); <---- line 35 $stmt->execute(); $stmt2->execute(); $stmt3->execute(); Before I worried about $termID, I started with just the $taxonomy part, and it added "post_tag" to row 1294. I hadn't defined term_id yet, so it added a zero. I haven't been able get the $termID to recognize the lastInsertID(). I've seen various 'solutions' and tried 3 or 4 of them. Mostly I get this error... Part of me also wonders, is lastInsertID() getting the id of the current row or the previous row, and would I need to somehow add a +1 to the mix.

I used the top version, very simple to understand...after seeing it of course.

Interesting you led with combining it with the initial query. While searching for ideas on how a query would look, I considered making it all one query. Many of the instances said while it made sense to try it just as easy to use a separate query. Which do you think is better? 1-12 rows at a time, likely not much of a resource / time difference. In the first one, wouldn't I still need the [$k] for the array? EDIT: I added this, and it worked. Why $name and $slug twice in the second example? One for each concat?

So I don't concat in the insert? Does the concat take place in the foreach loop?

It's already created. It's what WordPress uses. I've piggy backed on the tag.php page to put my own content on it, pulling the slug off the URL. I'm just trying to update the wp_terms table as names get entered into the site. (I'll update the wp_terms_taxonomy table as well in the process, but that's take care of.)

I have name and slug in the insert query. Not sure what you mean by it's not in there. Never dealt with PDO syntax before this topic, nor dealt with bind_param. I was trying to apply your earlier code to this.