Jim R

How does it separate the 'commit' and work as expected but not the 'decommit'? This is what the output looks like: https://www.courtsideindiana.com/tag/kamari-lands/ The 'commit' triggers the top line of the profile, as well as the asterisks next to the college of choice. He recently de-committed from Syracuse. There are no 'list' instances for this player. Until I added the CASE for 'decommit', this code has been unchanged, working and live on my site since May of 2020.

I left column 'other' out of the database picture. It's for those who commit to a college in a sport other than basketball or commit as a walk-on (no scholarship).

The below query has worked up to now fully as intended until yesterday when I added one additional CASE. It's the final one, dealing with 'recommit'. I've stripped out all the other output. This code ultimately looks what scholarship offers are being made from colleges, if the player made a final list, then finally a commitment. However, we've had a couple of players break free from their commitment, and we want to note that. Code wise, I'm looking to have the 'decommit' trump the 'commit'. However, that's not the issue I'm having up to this point. Right now, it's not recognizing the Decommit in the database. So my output at the bottom: echo $line['decommit']; ...doesn't show anything. Here is the query and output. $query = "SELECT *,p.id as pid, concat(nameFirst,' ',nameLast) as nameFull, GROUP_CONCAT( CASE WHEN recruit_type = 'Offer' THEN c.college END) as offers, GROUP_CONCAT( CASE WHEN recruit_type = 'List' THEN c.college END) as list, GROUP_CONCAT( CASE WHEN recruit_type = 'Offer' THEN date_format(po.time, '%m.%d.%y') END) as time, GROUP_CONCAT( CASE WHEN recruit_type = 'List' THEN date_format(po.time, '%m.%d.%y') END) as timeList, CASE WHEN other !='' THEN concat(c.college,' (',other,')') WHEN recruit_type = 'Commit' THEN c.college END as commit, CASE WHEN recruit_type = 'Decommit' THEN c.college END as decommit FROM a_players p LEFT JOIN a_schools s ON p.schoolID = s.id LEFT JOIN a_players_offers po ON p.id = po.playerID LEFT JOIN a_colleges c ON po.collegeID = c.id WHERE CONCAT(lower(nameFirst),'-',lower(nameLast)) = '".$tag."' || CONCAT(nameFirst,' ',nameLast) = '". $search ."' ORDER BY list,offers "; // End tag vs search term, finish the query $results = mysqli_query($con,$query); echo mysqli_error($con); while($line = mysqli_fetch_assoc($results)) { echo $line['decommit']; } This is what the last few rows in the database look like: I've checked spelling. I've made sure there isn't whitespace. I didn't think adding the extra case with an alias would be the problem I'd have, but I'm having it.

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.

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.

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?

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.

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.

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.

1) They will be the same, but there are times when I will call the list of players from a_players. It seems easier to have their varsity status in that table than having to join a_rosters just to get it. (Duplicate key update to handle that as kids get older.) 2) There will not be players from different schools playing on a school's team, just players from that school.

team = schoolID I've made all the changes accordingly, and that's not been a problem at all. I have varsity in both tables because there will be times I call on a_players and only needing their varsity status off of a_rosters. a_players a_rosters However, hold for a minute. I tested it out with multiple players, and it didn't really work. As best as I can tell it produced the following: in a_players: player 1: id, fname, name, feet, inches, schoolID, grade, varsity player 2: nothing in a_rosters: player 1: nothing player 2: schoolID, uniform, player ID of player 1, varsity, season WTF?

Yes

I echoed out what you suggested above: $pid->execute(); $pid->bind_result($playerID); $row = $pid->fetch(); echo 'playerID: ' . $playerID .' value of row: '. $row;

But it didn't work. 😊

What's $row producing in what you typed? Why wouldn't that be a variable being passed to the next query? Not fully sure how it works, but if we're binding a result to $playerID, shouldn't that value be passed to the next query?

That yielded what I had earlier, at least visually. It produced the row in a_players (table). It echoed the newly created id, but it did not produce an id in a_rosters. That version didn't even produce a new row in a_rosters. What I had was doing that, just without passing the $ playerID.

Line 95 ==> $row = $pid->fetch_assoc(); I have an echo in there too, so I know it's finding the id. It's not pushing to the $roster query.

I should say it worked enough to get and print the id in question and didn't throw an error.

This is working to get the $playerID $pid = $con->prepare("SELECT id FROM a_players WHERE nameFirst = ? AND nameLast = ? AND schoolID = ?"); $pid->bind_param('sss',$fname,$lname,$schoolID); In the FOREACH loop $pid->execute(); $pid->bind_result($playerID); $pid->fetch(); But now, it's not inputting into the roster table, a query which has been unchanged.

changed fetchColumn() to fetch_assoc(). Same error. The code I've added to this is a direct application to what you suggested from the start.

This is the code you gave me back on page 2 of this topic. I've used the same format (and tried a couple of others) based on what you sent. It worked...except as I've tried to get the $playerID.

$pid = $con->prepare("SELECT id FROM a_players WHERE nameFirst = ? AND nameLast = ? AND schoolID = ?"); $pid->bind_param('sss',$fname,$lname,$schoolID); In the FOREACH loop: $pid->execute(); $playerID = $pid->fetchColumn(); <-- line 95

I have tried $pid->fetch(); As well as fetchAll, fetchColumn. Same error. (Or whatever the syntax is) Using this as a reference: https://phpdelusions.net/pdo

Here is the total code as it stands: if(isset($_POST['submit'])) { $schoolID = $_POST['school']; $varsity = $_POST['varsity']; $season = $_POST['season']; // This uploads it into the Rosters table $player = $con->prepare("INSERT INTO a_players(schoolID,nameFirst,nameLast,feet,inches,grade,position,varsity) VALUES (?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE feet = ?, inches = ?, varsity = ? "); $player->bind_param('sssssssssss',$schoolID,$fname,$lname,$feet,$inches,$grade,$position,$varsity,$feet,$inches,$varsity); $pid = $con->prepare("SELECT id FROM a_players WHERE nameFirst = ? AND nameLast = ? AND schoolID = ?"); $pid->bind_param('sss',$fname,$lname,$schoolID); $roster = $con->prepare("INSERT INTO a_rosters(schoolID, playerID, uniform, varsity, season) VALUES (?,?,?,?,?)"); $roster->bind_param('sssss', $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(); echo $playerID; $roster->execute(); } It all works except getting the $playerID. I'll try a subquery with $roster now.

I have, otherwise I wouldn't have known this. I have tried both bind_param and bindparam. They both yield the same error. I have tested the query on my database with values typed in. It produces what it should. I know the values are being passed to the variables because they're working through the other queries. I'm showing you what i've tried, and where the current code sits. Also, keep in mind, this path was your suggestion in the beginning of this topic. It worked, except for what I found I needed, which was the previously established ids. I have since amended one of the queries to involve a Duplicate Key Update. Easy enough. So I'm not without learning. This one seems to evade us.