Trying to get data from form with Repeatable fields into MySQL...

[Jim R]

Like maybe

ON DUPLICATE KEY.....stop....(it creates zero rows)

then somehow tie the lack of rows created to the rest of the queries?

 

 

[Jim R]

Tried         echo $con->affected_rows;  to see what it returned.  It's a -1, which means an error.

[Jim R]

I was able to put $con->affected_rows; in a place that would return a 1, but I couldn't figure out how to create a loop with it.  It was either a 1 or -1.  I moved over to try $con->num_rows, but it's late, and I'm heading to bed.

 

Currently, this is part of the FOREACH loop:

			$player->execute();
			
            if(($con->num_rows) < 1) {
				echo $con->num_rows;
				exit();
			}
	            $playerID = $con->insert_id;


			$roster->execute();
			
			$stmt2->execute();
	            $termID = $con->insert_id;
			$stmt3->execute();

 

[Barand]

Easiest way is a three-step process

[Jim R]

{Soaking that in my brain.}

 

I logged back in to clarify something that I'm not sure I expressed earlier.  

When a player is entered into table a_roster, I have successfully, with your help, set up that it INSERTS the newly created playerID from table a_players.  However, while it won't enter a duplicate row in a_player, it doesn't port over the playerID from the player already in a_player.  

 

Does what you diagramed above solve that?  

 

I apologize for not realizing that earlier.  

[Barand]

5 minutes ago, Jim R said:

Does what you diagramed above solve that?  

It should

[Jim R]

Ok...thanks.  I'm in and out until this afternoon, so I'll have time to sit down to this later.  

 

 

[Jim R]

 

 

On 12/14/2020 at 6:15 AM, Barand said:

Easiest way is a three-step process

 

I want to make sure I'm clear on something before I dig into it.  The above query is replacing the query in this statement only? 

 

	$roster = $con->prepare("INSERT INTO a_rosters(schoolID, playerID, uniform, varsity, season) VALUES (?,?,?,?,?)");
		$roster->bind_param('sssss', $schoolID, $playerID, $uniform, $varsity, $season);

 

Edited December 15, 2020 by Jim R

[Barand]

Process 1 is a LOAD DATA LOCAL INFILE ... query to put the csv data into a temporary table.

Process 2 inserts player data from the temp table (new records only).

Process 3 matches the temp data with the player data using the names to get the player ids and inserts records into the roster table.

[Jim R]

 

$roster = $con->prepare("INSERT INTO a_rosters(schoolID, playerID, uniform, varsity, season) 
							SELECT t.schoolID,
									t.playerID
									t.uniform,
									t.varsity,
									t.season
							FROM temp_csv t
							JOIN
							a_players p
								ON t.nameFirst = p.nameFirst
								AND t.nameLast = p.nameLast
								AND t.schoolID = p.schoolID");		




In the FOREACH loop:

	$player->execute();
			$roster->execute(); <-- line 72

 

Threw this error:

 

Quote

Fatal error: Uncaught Error: Call to a member function execute() on boolean in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_form_process.php:72 Stack trace: #0 {main} thrown in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_form_process.php on line 72

 

[Jim R]

5 minutes ago, Barand said:

Process 1 is a LOAD DATA LOCAL INFILE ... query to put the csv data into a temporary table.

Process 2 inserts player data from the temp table (new records only).

Process 3 matches the temp data with the player data using the names to get the player ids and inserts records into the roster table.

 

This is separate from the load data local.  This is coming from the repeatable fields form.  

 

[Barand]

The write the data from the form into the temp csv file.

Then the process then on is exactly same, regardless of the data source.

[Jim R]

4 minutes ago, Barand said:

The write the data from the form into the temp csv file.

Then the process then on is exactly same, regardless of the data source.

 I have to do a separate INSERT from from temp_csv to Player Table, right?

Especially since (from what I've read) the load data local infile doesn't input directly into multiple tables. 

[Jim R]

7 minutes ago, Barand said:

The write the data from the form into the temp csv file.

Then the process then on is exactly same, regardless of the data source.

 I have to do a separate INSERT from from temp_csv to Player Table, right?

Especially since (from what I've read) the load data local infile doesn't input directly into multiple tables. 

[Jim R]

Get rid of the $player = $con->prepare and $player->bind_param lines?

 

I'm thoroughly confused as to how it will handle the arrays being sent.  

Edited December 15, 2020 by Jim R

[Barand]

2 minutes ago, Jim R said:

 I have to do a separate INSERT from from temp_csv to Player Table, right?

That is what I said Process 2 does.

INSERT IGNORE INTO player (...) SELECT ... FROM temp_csv;

 

[Jim R]

I still don't see how it handles the array, and it won't add duplicates the Player Table anyway, because I have a duplicate key set up.  The issue was just getting the playerID from the players already in the Player Table vs. insert_id.

 

It was inserting the player to the Roster Table, but it was passing the playerID as 0 since insert_id wasn't 'used' on that row, and I didn't have anything in there to go get it.

 

Changing gears on how to handle it isn't my strong suit.  😉

Edited December 15, 2020 by Jim R

[Barand]

1 hour ago, Barand said:

Process 3 matches the temp data with the player data, using the names, to get the player ids and inserts records into the roster table.

 

Edited December 15, 2020 by Barand

[Jim R]

How does that handle the arrays being created?  

[Barand]

Process 1a, with the multiple form records, is the only process that involves arrays.

Once you've got the data into temp_csv, it's just a couple of queries.

[Barand]

Here is the sequence of events.
I am not too sure about whether fields like grade and level belong with the player or the roster (that is known only to you so, correct normalizition fo the data is up to you).
The point is you have data in the csv file, some of which belong to the player table and some to the roster. In the roster table you require the players' ids and not names.

To start with, we have 2 existing players in the player table, Tom and Laura.

+-----------+-----------+----------+------+--------+----------+-------+-------+---------+
| player_id | nameFirst | nameLast | feet | inches | schoolID | grade | level | varsity |
+-----------+-----------+----------+------+--------+----------+-------+-------+---------+
|         1 | Laura     | Norder   |    6 |      4 |       21 |  2021 |     1 |      42 |
|         2 | Tom       | DiCanari |    4 |     11 |       21 |  2021 |     1 |      42 |
+-----------+-----------+----------+------+--------+----------+-------+-------+---------+

The csv file contains

uniform,nameFirst,nameLast,position,height
10,Laura,Norder,PG,"6'4"""
20,Tom,DiCanari,SF,"4'11"""
30,Percy,Flage,PG,"6'3"""
40,Scott,Chegg,PG,"6'2"""
45,Drew,Peacock,SF,"6'1"""
46,Wanda,Lust,PG,"5'10"""

PROCESS 1 - create and load temp_csv table

    CREATE TEMPORARY TABLE temp_csv (
        id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        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,
        varsity int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	 
    LOAD DATA LOCAL INFILE 'c:/inetpub/wwwroot/test/roster2.csv'
    INTO TABLE temp_csv
    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 = 65 ,
        grade = 2021 ,
        level = 1,
        varsity = 42,
        school_id = 21;

PROCESS 2 - add new players to player table

    INSERT IGNORE INTO player
           (nameFirst,nameLast,feet,inches,schoolID,grade,level,varsity)
    SELECT nameFirst
         , nameLast
         , feet
         , inches
         , school_id
         , grade
         , level
         , varsity
    FROM temp_csv;
    +-----------+-----------+----------+------+--------+----------+-------+-------+---------+
    | player_id | nameFirst | nameLast | feet | inches | schoolID | grade | level | varsity |
    +-----------+-----------+----------+------+--------+----------+-------+-------+---------+
    |         1 | Laura     | Norder   |    6 |      4 |       21 |  2021 |     1 |      42 |
    |         2 | Tom       | DiCanari |    4 |     11 |       21 |  2021 |     1 |      42 |
    |         5 | Percy     | Flage    |    6 |      3 |       21 |  2021 |     1 |      42 |
    |         6 | Scott     | Chegg    |    6 |      2 |       21 |  2021 |     1 |      42 |
    |         7 | Drew      | Peacock  |    6 |      1 |       21 |  2021 |     1 |      42 |
    |         8 | Wanda     | Lust     |    5 |     10 |       21 |  2021 |     1 |      42 |
    +-----------+-----------+----------+------+--------+----------+-------+-------+---------+

PROCESS 3 - Add roster records (with player ids)

    INSERT IGNORE INTO roster (team,uniform,position,player_id)
    SELECT t.team
         , t.uniform
         , t.position
         , p.player_id
    FROM temp_csv t
         JOIN player p ON t.nameFirst = p.nameFirst
                       AND t.nameLast = p.nameLast;
    +-----------+------+---------+----------+-----------+
    | roster_id | team | uniform | position | player_id |
    +-----------+------+---------+----------+-----------+
    |         1 |   65 | 10      | PG       |         1 |
    |         2 |   65 | 20      | SF       |         2 |
    |         3 |   65 | 30      | PG       |         5 |
    |         4 |   65 | 40      | PG       |         6 |
    |         5 |   65 | 45      | SF       |         7 |
    |         6 |   65 | 46      | PG       |         8 |
    +-----------+------+---------+----------+-----------+

[Jim R]

This isn't working on what is coming from the form.  I don't see how it could, since I'm not uploading a .csv file.

 

 

[Jim R]

		$players = " INSERT IGNORE INTO a_players
           (nameFirst,nameLast,feet,inches,schoolID,grade,level,varsity)
    SELECT nameFirst
         , nameLast
         , feet
         , inches
         , school_id
         , grade
         , level
         , varsity
    FROM temp_csv;";
		
		
		
		
		$roster = "INSERT INTO a_rosters(schoolID, playerID, uniform, varsity, season) 
							SELECT t.schoolID,
									t.playerID
									t.uniform,
									t.varsity,
									t.season
							FROM temp_csv t
							JOIN
							a_players p
								ON t.nameFirst = p.nameFirst
								AND t.nameLast = p.nameLast
								AND t.schoolID = p.schoolID";	

 

This is what I have, just renaming the table names.  It didn't input the single entry I submitted.

 

Edited December 16, 2020 by Jim R

[Jim R]

What we had before was working, other than not getting the playerID of players already entered in a_players.   I didn't necessarily know how to make the insert / select query align with the bind_param.  

 

 

Edited December 16, 2020 by Jim R

[Jim R]

That would be this code:

$roster = $con->prepare("INSERT INTO a_rosters(schoolID, playerID, uniform, varsity, season) VALUES (?,?,?,?,?)");
 		$roster->bind_param('sssss', $schoolID, $playerID, $uniform, $varsity, $season);