Jump to content

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


Recommended Posts

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

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. 

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

 

Link to post
Share on other sites

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.  

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

Link to post
Share on other sites

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

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

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

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

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.

Link to post
Share on other sites

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_playersimage.png.9ac2414b2776f8854881693cc7adc377.png

 

a_rosters

image.png.6be293f3e71f114f08d480ed3ef9ddcc.png

 

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

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

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.