Jim R

That's fair, and I wondered if I needed to store the file at all, but I kind of want to keep the files. I guess there isn't a need. So I tried it with just this... $filename = $_FILES['fileToUpload']['name']; $query = " LOAD DATA INFILE '$fileName' INTO TABLE a_stats FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n' IGNORE 2 Lines (jersey,points,2pm,2pa,3pm,3pa,ftm,fta,oreb,dreb,treb,ast,blk,stl,deflect,turn,charge,pf,tf) SET team = 426"; It still didn't input the data.

I'll be having .txt files uploaded via a form. Upon submission, it will be passed to another .php file to have the data entered into the database. It's not uploading the file to the folder, and while the trigger echoes what's set up to echo, it's loading the data into the database. Here is the form: echo '<form action="/wp-content/plugins/csi_stats/csi_process.php" method="post" enctype="multipart/form-data"> Select Boxscore to upload: <input type="file" name="fileToUpload" id="fileToUpload"> <input type="submit" value="Upload" name="submit"> </form>'; } Here is csi_process.php $filename = $_FILES['fileToUpload']['name']; $target_dir = "/wp-content/uploads/csi_boxscores/21/"; $target_file = $target_dir . basename($_FILES["fileToUpload"]["name"]); $uploadOk = 1; $imageFileType = strtolower(pathinfo($target_file,PATHINFO_EXTENSION)); // Check if image file is a actual image or fake image if(isset($_POST["submit"])) { // $check = getimagesize($_FILES["fileToUpload"]["tmp_name"]); echo $target_file . ' Boxscore Sent'; echo '<br>' .$filename; $uploadOk = 0; } $query = " LOAD DATA INFILE '$fileName' INTO TABLE a_stats FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n' IGNORE 2 Lines (jersey,points,2pm,2pa,3pm,3pa,ftm,fta,oreb,dreb,treb,ast,blk,stl,deflect,turn,charge,pf,tf) SET team = 426"; "team" will ultimately be a variable, and I'll have "game" in there too. Here is the data: 235a5d12-be10-43af-abba-e45456d9d8da Jersey|Points|TwoPointsMade|TwoPointAttempts|ThreePointsMade|ThreePointAttempts|FreeThrowsMade|FreeThrowAttempts|OffensiveRebounds|DefensiveRebounds|Rebounds|Assists|BlockedShots|Steals|Deflections|Turnovers|Charges|PersonalFouls|TechnicalFouls 10|8|1|2|2|6|0|0|0|4|4|1|1|1|0|1|0|0|0 12|3|0|5|0|3|3|4|1|2|3|0|1|1|0|0|0|2|0 20|8|4|6|0|0|0|0|1|1|2|0|1|1|0|2|0|0|0 22|3|0|1|1|1|0|0|1|2|3|1|0|0|0|2|0|2|0 24|18|4|7|1|3|7|10|0|2|2|1|0|1|0|3|0|3|0 32|1|0|2|0|3|1|2|2|1|3|0|0|1|0|0|0|2|0 41|8|0|0|2|3|2|2|0|0|0|0|0|1|0|0|0|1|0 43|11|5|7|0|0|1|2|0|6|6|1|0|4|0|2|0|3|0 52|3|1|6|0|0|1|1|4|3|7|0|0|1|0|1|0|4|0

Turns out it was just a typo. wp_terms_relationships - there shouldn't be an 's' in terms. Sorry about that. I should've run the basic query to see if I could get more info.

I assuming if you're here, you know WP table structure, but I also have two of my own tables called events and evaluations. Events columns are id, name, start, end, city, state, tag Evaluations isn't really part of this issue. I have a WP Page with custom code drawing anything from Evaluations that matches the Event to the page. Now, I would also like to list any WP Post that matches event.tag to this page. The error I'm getting: Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /home2/csi/public_html/wp-content/plugins/csi_reviews.php on line 970 So it's not finding any matches in the query, but I work it forward and back, the values line up. So I'm clearly missing something. So the URL that reads like this... webaddress.com/event/?event=18 $event = $_GET['event']; $query = "SELECT * FROM wp_posts p LEFT JOIN wp_terms_relationships tr ON p.ID = tr.object_id LEFT JOIN wp_terms t ON tr.term_taxonomy_id = t.term_id LEFT JOIN a_events e ON e.tag = t.slug WHERE e.id = '". $event ."' "; p.ID = 1459 tr.object_id = 1459 tr.term_taxonomy_id = 1231 t.term_id = 1231 e.tag = mibfl t.slug = mibfl e.id = 18 $event = 18

Even simpler as it turns out. This returns every coach whose tenure hasn't ended yet. select s.id as sid,coachFirst,coachLast,season_hired,season_end,username,s.server,toggle,city,school,nickname,bsect from a_coach c left join a_schools s on s.id = c.school_id where season_end is null order by s.id

I also have a seasons table. I could join that for the match, right?

OK...I'll play around with it, and I see what you're saying. I'lll have to set up the coaching tenure table before I can visualize how it will really help me with queries, but as always, I appreciate your input. When you suggested that, I wasn't ready to start adding coaches, and this is the first off-season I've messed with coaching changes. Everything is working well except this view, but I'd like to get the view set up for my writers. There is one issue with this though. When there is a coaching change, there is a change of contact information. I have the server name of each school in the school table, which for the most part is static (some use a personal email address instead of a school address). I have the Username (before the @) in the coach table. So even if using this extra table, I will have to type a new user name on each change. So either I change in the coach table with each change, or add it with a new coaching tenure line.

I am. I have a school table and coach table. In the coach table, I have school_id. Each time there is a coaching change, I add a new row and fill in the end season of the coach he replaces. I've read some things saying I can have a subquery in a view. Maybe it was because the subquery was in the From statement. MySQL program won't allow it, and there is something about MySQL workbench that I don't like. I don't remember at the moment though, but I've played around with it. I'm using Sequel Pro.

...and my host isn't upgrading to 5.7 anytime soon evidently. So I'm looking for an alternative way to query the most recent instances of a each result. I have the subquery that works for my site, but I'm wanting to create a View since I've eliminated all duplicated data 😉 Here is the query that creates the list pictured below: select s.id as sid,coachFirst,coachLast,season_hired,username,s.server,toggle,city,school,nickname,bsect from a_coach c inner join a_schools s on s.id = c.school_id order by s.id,season_hired desc sid = school ID sids 1, 4, 7 and 10 all made coaching changes this past summer. I want to reflect them as the current coach for the school (sid) based on the season_hired column. Group By, of course, shows the first instance, which really seems counterintuitive. I have messed around with using MAX, e.g. max(season_hired): max(season_hired) in the SELECT statement with a GROUP BY s.id after the JOIN. but it shows the past coach with the current hire date, so it would show for sid(1) = Rodney Williams hired in 2021. Is my only option the nested subquery? Here is the query with the subquery that gets me what I want for my website, but you can't create views with subqueries in MySQL 5.6. select * FROM ( select s.id as sid,coachFirst,coachLast,season_hired,username,s.server,toggle,city,school,nickname,bsect from a_coach c left join a_schools s on s.id = c.school_id order by sid,season_hired desc) mr group by mr.sid

Getting closer: 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 = 'Commit' THEN c.college END) as commit Didn't know I didn't have an 'else' in the Case statements.

I printed echo $line['list']; It produced... 0,0,0,0,0,0,0,0,0,0,0,0,0,0,Butler,Marquette There are 16 total rows with this player, 14 offers and 2 listed. I'm not sure why it would be 14 zeros. It should just produce Butler and Marquette because only two rows have List as recruit_type.

I'm pretty sure I had the wrong usage of GROUP_CONCAT. Here is the current query... SELECT *,p.id as pid, GROUP_CONCAT( CASE WHEN recruit_type = 'List' THEN c.college ELSE 0 END) as list, GROUP_CONCAT( CASE WHEN recruit_type = 'Offer' THEN c.college ELSE 0 END) as offers FROM a_players_offers po LEFT JOIN a_colleges c ON po.collegeID = c.id LEFT JOIN a_players p ON p.id = po.playerID LEFT JOIN a_schools s ON p.schoolID = s.id WHERE CONCAT(nameFirst,' ', nameLast) = '".$tag."' It's getting closer in terms of output...well...I think. The two which should be List no longer list the school as an offer. It's still not right, but it might be on the output side now.

I have a Player Profile that, among many other things, lists which scholarship offers he has. This was the 'offers' column in a_players (a CSV list that I exploded and sorted), but I decided I wanted an Offers table. It presumably will provide more flexibility in terms of how data is input and output. Many of the results from a_players (one row) will have about 5-6 rows from a_players_offers. To keep from printing all the profile information for each row of offers the player had, I dug around and found group_concat, which then allowed me to explode the list and sort it, much like before. I couldn't lump them all in one CASE command because offers can actually an offer or part of a list (fewer schools). $query = "SELECT *,p.id as pid, CASE WHEN recruit_type = 'List' THEN GROUP_CONCAT(c.college) ELSE 0 END as list, CASE WHEN recruit_type = 'Offer' THEN GROUP_CONCAT(c.college) ELSE 0 END as offers 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(nameFirst,' ', nameLast) = '".$tag."' "; Here is the part of the profile that produces the offers or list... (I added $line['recruit_type'] so I could see which alias it was using.) // Turn CSV into unordered list $offersList = $line['offers']; $offersList = explode(",",$offersList); asort($offersList); foreach ($offersList as $offers) { echo '<tr><td>' . $offers.$line['recruit_type']; echo '</td></tr>'; } echo '</table></div>'; Off them for print up as 'college-name'Offer. (Keep in mind, as I have changed over to a separate Offers table, I haven't redone all the output yet.) There should be 14 that say Offer after them and two that say List. Marquette and the second instance of Butler should have List after their name. Actually, I would think the way output is set up, it wouldn't list either of the List schools, just the 14 with Offer. (I want to create the separate aliases because once a player narrows down to his final list, that takes precedence over all of his offers. Most kids will have 5-6 before it's over, then reduce to two or three. In this kid's case, he has 14 offers, so far. He'll add more this summer, then come out with a list of 4-5 in the fall.)

I've restructured all the tables.

It's not a problem, just noting the irony of the situation. Still surprised there isn't a statement to get acronyms, or at least some way to use substring, noting a delimiter. Not anymore. All IDs now.

Between LEFT, RIGHT, SUBSTRING, SUBSR and a couple of more, there seems to be 4-6 ways I could just get the first letter of each field. None to get the first letter of each word? Code gymnastics? Anymore than using CASE? Normally I'm hammered on here for having too many columns or too many times duplicating data.

Hard to believe there isn't a way to get what is essentially an acronym.

It would just be the space in between the two words. Is the syntax close on what I typed in my OP?

I have a column = city In some instances, I just need the first the letter of each word in the city. Some city names are more than one word. So Flora would be (F) and Fort Wayne (FW). It's the WHEN toggle = 2 ... line. $query="SELECT id, (CASE WHEN toggle = 1 THEN concat(city,' ',school) WHEN toggle = 2 THEN concat(school,' (',substring(city,1,1),')') ELSE school END) as schools FROM a_schools ORDER BY schools "; Instead of substring i've tried acronym and initials. acronym(city) I found those in a search, but I got errors suggesting they weren't valid options. I've also tried regex_replace(city, [a-z], '') but got syntax errors too. Could be I didn't apply it right.

Well...I removed all the duplicate columns and replaced them IDs, and while it took some trial and error to reconfigure the joins and output, I was able to get it to work. Below is the final query. I'm glad it was at least something I hadn't done before and just couldn't get it figured out. Thank you for your time. $query = "SELECT *,mr.nameFirst,mr.grade, mr.nameLast FROM (SELECT p.id as pid, b.playerID as bplayerID, b.userID as buserID, s.toggle AS stoggle, o.toggle AS otoggle, p.city as pcity, p.school as pschool, s.city as scity, s.school as sschool, r.id as rid, o.city as ocity, o.school as oschool, oppID, nameFirst, nameLast, feet, inches, position, grade, time, event, review FROM a_players_reviews r LEFT JOIN a_players p ON p.id = r.playerID LEFT JOIN a_schools s ON p.schoolID = s.id LEFT JOIN a_schools o ON r.oppID = o.id LEFT JOIN a_player_bookmark b ON p.id = b.playerID && '". $userID ."' = b.userID WHERE bookmark>0 ORDER BY r.id desc LIMIT 15) mr ORDER BY mr.grade,mr.nameLast ";

It's not printing any of my name variables or the school and city of the player. It's printing the opponent now. So it's not printing the ones where I have duplicate entries.

Removed the alias from p.nameLast, and now the errors are gone. Still not fully there, as some of the content isn't being printed. It's not longer recognizing the player's name, his school and the opponent. I've added columns to the sub select, and I've used the aliases. The name alias rfirst and rlast aren't showing.

Removed the pgrade alias, now I'm getting Unknown column 'mostRecent.nameLast' in 'field list'

You said to alias them if needed.

I didn't see p.grade wasn't in there, but I was looking for mostRecent.grade. I put it in, bottom of the sub select. Same error shows up. Unknown column 'mostRecent.grade' in 'field list' $query = "SELECT *,mostRecent.grade, mostRecent.nameLast FROM (SELECT p.id as pid, b.playerID as bplayerID, b.userID as buserID, s.toggle AS stoggle, o.toggle AS otoggle, p.city as pcity, p.school as pschool, s.city as scity, s.school as sschool, r.id as rid, r.opp_city as roppcity, r.opp_school as roppschool, o.city as ocity, o.school as oschool, r.city as rcity, r.school as rschool, r.nameFirst as rfirst, r.nameLast as rlast, p.nameFirst as pfirst, p.nameLast as plast, p.grade as pgrade FROM a_players_reviews r LEFT JOIN a_players p ON CONCAT (r.nameFirst,r.nameLast) = CONCAT (p.nameFirst,p.nameLast) LEFT JOIN a_schools s ON CONCAT(r.city,r.school) = CONCAT(s.city,s.school) LEFT JOIN a_schools o ON CONCAT(r.opp_city,r.opp_school) = CONCAT(o.city,o.school) LEFT JOIN a_player_bookmark b ON p.id = b.playerID && '". $userID ."' = b.userID WHERE bookmark>0 ORDER BY r.id desc LIMIT 15) mostRecent ORDER BY mostRecent.grade,mostRecent.nameLast ";