Explode in a LOAD DATA LOCAL query?

[Jim R]

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

[Barand]

Does the height of your player depend on which roster they are in?

If a player is the same height no matter what roster s/he is placed in then the height columns should be in the player table, not the roster.

In answer to your question, it is often useful to load csv data into an intermediate table then move it to the correct table using PHP, where you can use functions like explode().

Why have 2 columns (feet/inches) and not just convert to inches?

Edited December 3, 2020 by Barand

[Barand]

That said, here's a solution

INPUT (roster.csv)

uniform, firstname, lastname, position, height
101,Freda,Greaves,left out,6'4"
102,Jamie,Oliver,left behind,4'11"

SQL

LOAD DATA LOCAL INFILE 'c:/inetpub/wwwroot/test/roster.csv'
INTO TABLE roster
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(uniform, nameFirst, nameLast,position,@height)
SET height = substring_index(@height, '\'', 1) * 12                             -- feet
             + substring_index(substring_index(@height, '"', 1), '\'', -1)      -- inches

TABLE (roster)

+-----------+---------+-----------+----------+-------------+--------+
| roster_id | uniform | nameFirst | nameLast | position    | height |
+-----------+---------+-----------+----------+-------------+--------+
|         1 | 101     | Freda     | Greaves  | left out    |     76 |
|         2 | 102     | Jamie     | Oliver   | left behind |     59 |
+-----------+---------+-----------+----------+-------------+--------+

 

[Jim R]

4 hours ago, Barand said:

Does the height of your player depend on which roster they are in?

If a player is the same height no matter what roster s/he is placed in then the height columns should be in the player table, not the roster.

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.  

 

4 hours ago, Barand said:

In answer to your question, it is often useful to load csv data into an intermediate table then move it to the correct table using PHP, where you can use functions like explode().

Why have 2 columns (feet/inches) and not just convert to inches?

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.

 

 

[Jim R]

2 hours ago, Barand said:

That said, here's a solution

INPUT (roster.csv)

uniform, firstname, lastname, position, height
101,Freda,Greaves,left out,6'4"
102,Jamie,Oliver,left behind,4'11"

SQL

LOAD DATA LOCAL INFILE 'c:/inetpub/wwwroot/test/roster.csv'
INTO TABLE roster
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(uniform, nameFirst, nameLast,position,@height)
SET height = substring_index(@height, '\'', 1) * 12                             -- feet
             + substring_index(substring_index(@height, '"', 1), '\'', -1)      -- inches

TABLE (roster)

+-----------+---------+-----------+----------+-------------+--------+
| roster_id | uniform | nameFirst | nameLast | position    | height |
+-----------+---------+-----------+----------+-------------+--------+
|         1 | 101     | Freda     | Greaves  | left out    |     76 |
|         2 | 102     | Jamie     | Oliver   | left behind |     59 |
+-----------+---------+-----------+----------+-------------+--------+

 

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)

[Jim R]

SET feet = substring_index(@feet, '\'', 1), 
inches = substring_index(@inches,  '"', 1), '\'', -1)

Trying that when I get back to my desk.

Edited December 3, 2020 by Jim R

[Barand]

5 minutes ago, Jim R said:

SET feet = substring_index(@feet, '\'', 1), 
inches = substring_index(@inches,  '"', 1), '\'', -1)

Trying that when I get back to my desk.

That's why I laid it out the way I did with the comments - so it would be easy for you get the separate feet/inches values if you still wanted to go that way.

 

[edit] Look more closely at my code - you require two substring_index()s to extract the inches. The inner to get the string before the final " and the outer one to get the string after the '

SET feet = substring_index(@height, '\'', 1) * 12 ,
    inches = substring_index(substring_index(@height, '"', 1), '\'', -1)      

 

Edited December 3, 2020 by Barand

[Jim R]

First I was getting this error:

Quote

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

 

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)

Quote

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''\'', -1)' at line 10

 

[Barand]

13 hours ago, Jim R said:

The players' height will be exported as (US measurements) a single column, e.g. 6'4",

then why have you defined the csv columns as

(uniform,nameFirst,nameLast,position,@feet,@inches)

?

With the mix of quotes in the SQL you would better using heredoc syntax when defining the query.

[Jim R]

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?

 

[Jim R]

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);

 

 

 

[Jim R]

5 minutes ago, Jim R said:

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?

 

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?

Edited December 3, 2020 by Jim R

[Jim R]

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

[Jim R]

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)
    ";

 

Edited December 3, 2020 by Jim R

[Jim R]

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.

Edited December 3, 2020 by Jim R

[Barand]

2 hours ago, Jim R said:

(uniform,nameFirst,nameLast,@email,@cell,position,@role,@video,@feet,@inches,@weight)

Your queries seem to be sprouting columns.

What do the csv  and your a_rosters table actually look like?

[Jim R]

7 minutes ago, Barand said:

Your queries seem to be sprouting columns.

What do the csv  and your a_rosters table actually look like?

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

[Barand]

Ok, keep it a secret. Good luck.

[Jim R]

15 minutes ago, Barand said:

Ok, keep it a secret. Good luck.

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:

 

Edited December 3, 2020 by Jim R

[Jim R]

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.

[Jim R]

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?

Edited December 3, 2020 by Jim R

[Barand]

this worked

$team  = 65;
$grade = 2020;
$level = 1;

/**** CSV CONTENT (export from Excel) 

uniform,nameFirst,nameLast,position,height
10,Laura,Norder,PG,"6'4"""
20,Tom,DiCanari,SF,"4'11"""

********************************************/
$sql = <<<SQL
    LOAD DATA LOCAL INFILE 'c:/inetpub/wwwroot/test/roster2.csv'
    INTO TABLE roster
    FIELDS TERMINATED BY ','
           OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n'
    IGNORE 1 ROWS
    (uniform, nameFirst, nameLast,position,@height)
    SET feet = substring_index(@height, '\'', 1) , 
        inches = substring_index(substring_index(@height, '"', 1), '\'', -1) ,
        team = $team ,
        grade = $grade ,
        level = $level
SQL;

$stmt = $my->query($sql);

/**** TABLE: roster
  
CREATE TABLE `roster` (
  `roster_id` int(11) NOT NULL AUTO_INCREMENT,
  `team` int(11) DEFAULT NULL,
  `grade` int(11) DEFAULT NULL,
  `uniform` varchar(45) DEFAULT NULL,
  `nameFirst` varchar(45) DEFAULT NULL,
  `nameLast` varchar(45) DEFAULT NULL,
  `position` varchar(15) DEFAULT NULL,
  `feet` tinyint(4) DEFAULT NULL,
  `inches` tinyint(4) DEFAULT NULL,
  `level` int(11) DEFAULT NULL,
  PRIMARY KEY (`roster_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
+-----------+------+-------+---------+-----------+----------+----------+------+--------+-------+
| roster_id | team | grade | uniform | nameFirst | nameLast | position | feet | inches | level |
+-----------+------+-------+---------+-----------+----------+----------+------+--------+-------+
| 1         | 65   | 2020  | 10      | Laura     | Norder   | PG       | 6    | 4      | 1     |
| 2         | 65   | 2020  | 20      | Tom       | DiCanari | SF       | 4    | 11     | 1     |
+-----------+------+-------+---------+-----------+----------+----------+------+--------+-------+
 
*/

 

Edited December 4, 2020 by Barand

[Jim R]

It did work.  Thank you. 

 

Always appreciate the help I get.