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

[Jim R]

I have, otherwise I wouldn't have known this.  

3 hours ago, Jim R said:

Evidently mixing name and positional placeholders is frowned upon.  

 

 

I have tried both bind_param and bindparam.  They both yield the same error.  I have tested the query on my database with values typed in.  It produces what it should.  I know the values are being passed to the variables because they're working through the other queries. 

I'm showing you what i've tried, and where the current code sits.

Also, keep in mind, this path was your suggestion in the beginning of this topic.  It worked, except for what I found I needed, which was the previously established ids. 

 

I have since amended one of the queries to involve a Duplicate Key Update.  Easy enough. So I'm not without learning. This one seems to evade us. 

Edited December 17, 2020 by Jim R

[Jim R]

Here is the total code as it stands:

 

	if(isset($_POST['submit'])) {
	
		$schoolID = $_POST['school']; 
		$varsity = $_POST['varsity']; 
		$season = $_POST['season'];
		
	//	This uploads it into the Rosters table
				
		$player = $con->prepare("INSERT INTO a_players(schoolID,nameFirst,nameLast,feet,inches,grade,position,varsity) 
		VALUES (?,?,?,?,?,?,?,?)
		ON DUPLICATE KEY UPDATE
		feet = ?,
		inches = ?,
		varsity = ?
		");
		$player->bind_param('sssssssssss',$schoolID,$fname,$lname,$feet,$inches,$grade,$position,$varsity,$feet,$inches,$varsity);
	
	
		$pid = $con->prepare("SELECT id FROM a_players WHERE nameFirst = ? AND nameLast = ? AND schoolID = ?");
		$pid->bind_param('sss',$fname,$lname,$schoolID);
		 	
 		$roster = $con->prepare("INSERT INTO a_rosters(schoolID, playerID, uniform, varsity, season) VALUES (?,?,?,?,?)");
 		$roster->bind_param('sssss', $schoolID, $playerID, $uniform, $varsity, $season);


		foreach ($_POST['uniform'] as $k => $uniform)  {
			$fname = $_POST['nameFirst'][$k];
			$lname = $_POST['nameLast'][$k];
			$feet = $_POST['feet'][$k];
			$inches = $_POST['inches'][$k];
			$grade = $_POST['grade'][$k];
			$position = $_POST['position'][$k];
			
            
            
			$player->execute();
				$pid->execute();

				echo $playerID;

			
			$roster->execute();
			

			
			
}  

 

It all works except getting the $playerID.  I'll try a subquery with $roster now. 

[Barand]

It's nothing to do with subqueries.

When you do a SELECT query it returns a resultset. You need to process the result to get the values. (EG fetch a row ). $playerID value won't appear by magic.

[Jim R]

I have tried  $pid->fetch();

As well as fetchAll, fetchColumn.  Same error.  

(Or whatever the syntax is) 

 

Using this as a reference:

https://phpdelusions.net/pdo

Edited December 17, 2020 by Jim R

[Jim R]

$pid = $con->prepare("SELECT id FROM a_players WHERE nameFirst = ? AND nameLast = ? AND schoolID = ?");
		$pid->bind_param('sss',$fname,$lname,$schoolID);


In the FOREACH loop:
	$pid->execute();
				$playerID = $pid->fetchColumn();  <-- line 95

 

Quote

Fatal error: Uncaught Error: Call to undefined method mysqli_stmt::fetchColumn() in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_form_process.php:95 Stack trace: #0 {main} thrown in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_form_process.php on line 95

 

[Barand]

21 minutes ago, Jim R said:

Using this as a reference:

https://phpdelusions.net/pdo

Why are you using a reference to PDO when you have a mysqli connection? fetchColumn() is a PDO method, mysqli uses different methods. Again, I refer you to the manual. You should try it.

[Jim R]

This is the code you gave me back on page 2 of this topic.  I've used the same format (and tried a couple of others) based on what you sent.  It worked...except as I've tried to get the $playerID.

 

 

[Jim R]

changed fetchColumn() to fetch_assoc().  Same error.

 

The code I've added to this is a direct application to what you suggested from the start.  

[Jim R]

This is working to get the $playerID

$pid = $con->prepare("SELECT id FROM a_players WHERE nameFirst = ? AND nameLast = ? AND schoolID = ?");
		$pid->bind_param('sss',$fname,$lname,$schoolID);


In the FOREACH loop

$pid->execute();
$pid->bind_result($playerID);
$pid->fetch();
				

 

But now, it's not inputting into the roster table, a query which has been unchanged.  

[Jim R]

I should say it worked enough to get and print the id in question and didn't throw an error. 

Edited December 18, 2020 by Jim R

[Barand]

Try

#In the FOREACH loop

    $pid->execute();
    $row = $pid->fetch_assoc();
    $player_id = $row['id'];

 

[Jim R]

Quote

Fatal error: Uncaught Error: Call to undefined method mysqli_stmt::fetch_assoc() in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_form_process.php:95 Stack trace: #0 {main} thrown in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_form_process.php on line 95

Line 95  ==> $row = $pid->fetch_assoc();

 

I have an echo in there too, so I know it's finding the id.  It's not pushing to the $roster query.  

[Barand]

Sorry. This precisely why I hate mysqli - it's the result object that has the fetch_assoc() method but we are using a mysqli statement object with a different set of methods (in PDO they're the same animal). Try

In the FOREACH loop

    $pid->execute();
    $pid->bind_result($player_id);
    $row = $pid->fetch();

 

[Jim R]

That yielded what I had earlier, at least visually.  It produced the row in a_players (table).  It echoed the newly created id, but it did not produce an id in  a_rosters.  

 

That version didn't even produce a new row in a_rosters.  What I had was doing that, just without passing the $ playerID.

[Barand]

OK, now it's your turn again.

[Jim R]

What's $row producing in what you typed?  Why wouldn't that be a variable being passed to the next query?

 

Not fully sure how it works, but if we're binding a result to $playerID, shouldn't that value be passed to the next query?

Edited December 18, 2020 by Jim R

[Barand]

4 minutes ago, Jim R said:

What's $row producing in what you typed?

According to the manual (your best friend)...

[Jim R]

But it didn't work.  😊

 

Edited December 18, 2020 by Jim R

[Jim R]

I echoed out what you suggested above:

	$pid->execute();
	$pid->bind_result($playerID);
	$row = $pid->fetch();
				
	echo 'playerID: ' . $playerID .' value of row: '. $row;

 

Quote

playerID: 890 value of row: 1

 

Edited December 18, 2020 by Jim R

[Barand]

Put us out of the our misery, the suspense is too much.

Is that player_id correct or not?

[Jim R]

Yes

[Barand]

Looking at your recent code you have schooID and varsity in both the player table and the roster table - why?

Didn't you once have a "team" column in the roster table?

Perhaps it might be an idea for you now to disclose the structures of those two tables.

[Jim R]

team = schoolID  I've made all the changes accordingly, and that's not been a problem at all.  

I have varsity in both tables because there will be times I call on a_players and only needing their varsity status off of a_rosters.  

 

a_players

 

a_rosters

 

However, hold for a minute.  I tested it out with multiple players, and it didn't really work.  As best as I can tell it produced the following:

in a_players:

• player 1: id, fname, name, feet, inches, schoolID, grade, varsity
• player 2:  nothing

in a_rosters:

• player 1: nothing
• player 2: schoolID, uniform, player ID of player 1, varsity, season

WTF?

 

 

Edited December 18, 2020 by Jim R

[Barand]

16 minutes ago, Jim R said:

I have varsity in both tables because there will be times I call on a_players and only needing their varsity status off of a_rosters.

1 ) So sometimes a player's varsity status in the roster table can be different from their varsity status in their player record?

2 ) Will there be a mix of players from different schools playing in a school's team?

[Jim R]

Just now, Barand said:

1 ) So sometimes a player's varsity status in the roster table can be different from their varsity status in their player record?

2 ) Will there be a mix of players from different schools playing in a school's team?

1)  They will be the same, but there are times when I will call the list of players from a_players.  It seems easier to have their varsity status in that table than having to join a_rosters just to get it.  (Duplicate key update to handle that as kids get older.)  

2)  There will not be players from different schools playing on a school's team, just players from that school.