Jump to content

Explode in a LOAD DATA LOCAL query?


Jim R

Recommended Posts

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

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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 |
+-----------+---------+-----------+----------+-------------+--------+

 

  • Like 1
Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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 by Barand
  • Like 2
Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

 

Link to comment
Share on other sites

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

 

 

 

Link to comment
Share on other sites

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 by Jim R
Link to comment
Share on other sites

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 by Jim R
Link to comment
Share on other sites

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 by Jim R
Link to comment
Share on other sites

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

image.png.95b3f0ef6f780a1f9d82380a2938f52b.png

Link to comment
Share on other sites

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. 

 

image.png.f2bbde310c96444dd864aa196537b54b.png

 

Here is what it looks like after loaded into the database:

image.thumb.png.e87034c9cab520d9d10c0a6622169918.png

 

Database structure:

 

image.thumb.png.45babba1334a817c00500abbbbd3132e.png

Edited by Jim R
Link to comment
Share on other sites

I increased the field lengths for position, feet and inches.    Still getting 0's in feet and inches, but position looks like this now...

1758688838_ScreenShot2020-12-03at4_12_44PM.thumb.png.ed4d132539820af9b423b75d6c176714.png

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.

Link to comment
Share on other sites

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 by Jim R
Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.