Jim R

It's so I can create WordPress tags as I go. I'm using the tag template to auto-generate player profiles. I'm calling a function toward the top of tag.php (generic name) to put my custom information on there. Under that will be the normal content a tag.php would present on a WP site. Doing this will keep me from having to add tags later or update the table periodically. This is much easier than creating my own profile page, then digging into WP templating and styling it the way I want it. I've had this way for 5-6 years, but now with coaches entering so many extra players, I'm trying to take care of it all at once. So I'm not creating anything, just inserting to what is already there.

Do I not have that as name and slug?

Memory refresh: I'm this code handles the User typing in a roster of players, using a form with repeated fields. It would be any number of players from 1 to 12. Apply the code from above, using input from the same form, I want a separate INSERT with just the first and last name into an additional data table as 'name' and 'slug'. $stmt = $con->prepare("INSERT INTO wp_terms(concat(nameFirst,' ',nameLast) as name, concat(lower(nameFirst),'-',lower(nameLast)) as slug) VALUES (?,?)"); $stmt->bind_param('ss', $name, $slug); foreach ($_POST['name'] as $k => $name) { $slug = $_POST['slug'][$k]; $stmt->execute(); } This is the error I received:

It did work. Thank you. Always appreciate the help I get.

I changed the TYPE for feet and inches to VARCHAR. I think helped some, but it's also preceding the number with quotation marks. I opened the file in a text editor, and those fields are wrapped with " ". So in position = "PG" @height = "6'4"" <= extra set of double quotes at the end So I'd need to reshape the delimiters. For inches, we have: inches = substring_index(substring_index(@height, '\"', 1), '\'', -1) Would that be a 2 in this instance?

I increased the field lengths for position, feet and inches. Still getting 0's in feet and inches, but position looks like this now... So it's showing the whole position, but where the were multiple positions listed (I just want their primary position), it shows the quotation marks on the left.

Keep what a secret? Here is the file. I forgot to add it. I deleted the columns I'm not using. Here is what it looks like after loaded into the database: Database structure:

Ooops...I've been taking them out as I've posted them because I'm skipping over them in the CSV. Skipping @email, @cell, @role, @video, @weight It's lined up fine, other than the substrings not producing

Interesting too (at least to me), I changed position to @position then added this line: position = substring_index(@position, ',', 1), It should produce entries like PG, SG, SF. Instead, it's producing "P, "S, "S, whereas the rows w/o the , in position produce what's intended.

Disregard the previous post. I had misaligned columns. However, it's still showing 0 in feet and inches... ...with the current query $query = " LOAD DATA LOCAL INFILE '$tmp' INTO TABLE a_rosters FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 Lines (grade,uniform,nameFirst,nameLast,position,@height) SET team = '" .$team . "', level = '" .$level. "', feet = substring_index(@height, '\'', 1), inches = substring_index(substring_index(@height, '\"', 1), '\'', -1) ";

It didn't added the feet or inches columns in the database. They just show up as 0.

Yep...changing it to a CSV file worked. Is there an easy to handle an xlsx file, or will I need to have my Users (coaches) export to CSV? (Not sure what they're exporting from does that just yet. My test User is checking.) It would be easier for my users if I could handle an xlsx file. (I'll search for that after I hit submit.) EDIT: The answer seems to be that I will need to convert it to a CSV file. Agree?

Current code: $filename = $_FILES['fileToUpload']['name']; $tmp = $_FILES["fileToUpload"]["tmp_name"]; $team = $_POST['team']; if(isset($_POST["submit"])) { echo $target_file . '<br>Roster Sent'; echo '<br>' .$filename; echo '<br>' .$tmp; $uploadOk = 0; } $query = " LOAD DATA LOCAL INFILE '$tmp' INTO TABLE a_rosters FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 Lines (uniform,nameFirst,nameLast,position,@feet,@inches) SET team = '" .$team . "', feet = substring_index(@feet, '\'', 1) * 12 , inches = substring_index(substring_index(@inches, '\"', 1), '\'', -1) "; $results = mysqli_query($con,$query); echo mysqli_error($con);

I see your EDIT above. I omitted the second substring_index of line 30. I copied what you wrote above, changing @height to @feet and @inches. Still throwing this error: Parse error: syntax error, unexpected '', 1), '' (T_CONSTANT_ENCAPSED_STRING) in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_upload_process.php on line 30 $query = " LOAD DATA LOCAL INFILE '$tmp' INTO TABLE a_rosters FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 Lines (uniform,nameFirst,nameLast,@email,@cell,position,@role,@video,@feet,@inches,@weight) SET team = '" .$team . "', feet = substring_index(@feet, '\'', 1) * 12 , inches = substring_index(substring_index(@inches, '"', 1), '\'', -1) <= line 30 "; I added the / before the " in line 30. No more errors, but it's not loading the data. Would it have to do with the file being an .xlsx file vs. a .csv?

First I was getting this error: So I added a \ before the " include("/pathway/to/con.php"); $filename = $_FILES['fileToUpload']['name']; $tmp = $_FILES["fileToUpload"]["tmp_name"]; $team = $_POST['team']; if(isset($_POST["submit"])) { echo $target_file . '<br>Roster Sent'; echo '<br>' .$filename; echo '<br>' .$tmp; $uploadOk = 0; } $query = " LOAD DATA LOCAL INFILE '$tmp' INTO TABLE a_rosters FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 Lines (uniform,nameFirst,nameLast,position,@feet,@inches) SET team = '" .$team . "', feet = substring_index(@feet, '\'', 1), inches = substring_index(@inches, '\"', 1), '\'', -1) // <= This is where that error was "; $results = mysqli_query($con,$query); echo mysqli_error($con); It produced my echos, but it also threw this error: (Same line, really)

SET feet = substring_index(@feet, '\'', 1), inches = substring_index(@inches, '"', 1), '\'', -1) Trying that when I get back to my desk.

Can't I also set the substring_index to find @feet then @inches? SET feet = substring_index(@feet, '\'', 1) SET inches = substring_index(@inches, '"', 1), '\'', -1)

Height isn't roster dependent, and most of the players that will be entered by the coaches will not be in the database, so we'll need to include their height regardless. I have key indexes set up not to allow duplicates. Height will be imported here as #'#", but the my database is set up in two columns, feet and inches for over 800 rows. It's also easier for me to create exports for others with different formats, most notably those who want exports in #-# as is common in the US. I know how to get data out how I need it. (At least as it's currently situated.) I've just come across a way that I don't know how to get the data in as I want it.

I've been asked to allow coaches to upload rosters exported from another source. So I'll be allowing them to upload CSV files. The players' height will be exported as (US measurements) a single column, e.g. 6'4", but I need to have it be two separate columns -- feet and inches. Trying to make it as easy on the coaches as possible, so I'd rather not force them to reformat their file. (Because many won't.) Here is the query I'm currently working with. It works quite well for my test data, but with feet and inches as separate columns. LOAD DATA LOCAL INFILE '$tmp' INTO TABLE a_rosters FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 Lines (uniform,nameFirst,nameLast,position,feet,inches) SET team = '" .$team . "'"; I've dealt a decent amount with exploding an array, putting the results in a Foreach loop and echoing the results. I've not tried to use it before or in the query. (I'm guessing it's not a viable option in a query.) So the files that will be uploaded or emailed to me to upload will have the following columns: uniform, nameFirst,nameLast,position,height ...and I need to turn height into => feet, inches

Simple mistake. That was the issue then for most of this.

Thank you for that. I already had the connection setup and the database set up. That code is still throwing this error: Fatal error: Uncaught Error: Call to a member function prepare() on null in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_process.php:29 Stack trace: #0 {main} thrown in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_process.php on line 14 ($db = myConnect('####'); include("/path to... /con.php"); $db = myConnect('#####'); if ($_SERVER['REQUEST_METHOD']=='POST') { $stmt = $db->prepare("INSERT INTO a_rosters(uniform, nameFirst, nameLast) VALUES (?,?,?)"); $stmt->bind_param('sss', $uniform, $fname, $lname); foreach ($_POST['uniform'] as $k => $uniform) { $fname = $_POST['nameFirst'][$k]; $lname = $_POST['nameLast'][$k]; $stmt->execute(); } } Here is the connection file I use for all my queries... $con = mysqli_connect("localhost","username","password", "csi_wp"); // Check connection if (mysqli_connect_errno()) { //echo "Failed to connect to MySQL: " . mysqli_connect_error(); }

I tried this INSERT inside the loop: foreach ($data as $rec) { // insert record $query = "INSERT INTO a_rosters (uniform,nameFirst,nameLast) VALUES ('$uniform','$fname','$lname')"; It didn't insert anything. I even tried these values: '$rec[$uniform]', '$rec[$fname]','$rec[$lname] Is the below in lieu of all of your previous code? I did that, changing the table name, but it gave me the UNDEFINED error again.

Parse error: syntax error, unexpected ',', expecting ']' in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_process.php on line 46 Line 46 is :: echo '<pre>' . print_r[$data, 1] . '</pre>'; I haven't added the loop yet.

Back to the issue... This is what you gave me in your first response: $data = []; foreach ($_POST['uniform'] as $k => $uniform) { $fname = $_POST['nameFirst'][$k]; $lname = $_POST['nameLast'][$k]; $data[$k] = [$uniform, $fname, $lname]; } After the loop, not thinking I needed it in the loop, I added... $data = implode(",",$data[$k]); echo $data; It echoed the last entry: 10,Steve,Smith => close to what's needed So I placed that inside the loop but got this error: Warning: implode(): Invalid arguments passed in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_process.php on line 43 Changed the $data to $row, and it just produces "Array,Array,Array".

I know, and I did. Each option was giving me the same error. I don't know what the error is really telling me, so I don't know what to change. I don't use pdo, so I read pdo-prepare as preparing to put data I haven't gotten yet into a data table. Then I read the FOREACH loop as getting the $_POST data, which was executed with the $stmt->execute($data); When that didn't work, I put the FOREACH loop you provided earlier. That didn't work. Then I put query in the loop. That didn't work. Just telling you what my thinking was. Elementary...for sure, but that's why I'm here. I don't start posts until after I search here and Stackoverflow. The initial FOREACH loop that you posted, sort of produced what I was looking for. I'm trying to get data from a repeatable field form into a data table. I thought I was doing well to know the implode the array.