Jump to content

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


Recommended Posts

  • Replies 135
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

You could, but it's easier to do it the PHP code if ($_SERVER['REQUEST_METHOD']=='POST') { $stmt1 = $db->prepare("INSERT INTO a_rosters(uniform, nameFirst, nameLast) VALUES (?,?,?)");

Yes, the variables map to the question-mark placeholders.  You need one for each question-mark.  So when you need to place the same value into a query in different places you need to use multiple ? an

Quite right, sorry about that. The bind statement should be $stmt2->bind_param('ssss', $fname, $lname, $fname, $lname);  

Posted Images

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

 

Link to post
Share on other sites

{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.  

Link to post
Share on other sites

 

 

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

Easiest way is a three-step process

image.png.a2d3d9adf943ca5b985d30a112143eaa.png

 

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

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.

Link to post
Share on other sites

 

$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

 

Link to post
Share on other sites
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.  

 

Link to post
Share on other sites
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.

image.png.0d924ea4827b90f0f72aba832c92492b.png

 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. 

Link to post
Share on other sites
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.

image.png.0d924ea4827b90f0f72aba832c92492b.png

 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. 

Link to post
Share on other sites

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

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

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

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

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

 

Link to post
Share on other sites

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.