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

[Barand]

1 hour ago, Jim R said:

I didn't necessarily know how to make the insert / select query align with the bind_param.

The insert/select queries don't require parameter binding.

 

1 hour ago, Jim R said:

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

 

From where do you get the value in $playerID

[Jim R]

8 minutes ago, Barand said:

The insert/select queries don't require parameter binding.

 

I'm saying when I figured out I was missing how to get the playerID of previously entered players, I was looking at subqueries and trying to figure out how to do that in what you provided earlier, which was otherwise working very well.  

 

8 minutes ago, Barand said:

From where do you get the value in $playerID

    I was trying this:

 

$player->execute();
       $playerID = $con->query("SELECT id FROM a_players 
          WHERE p.nameFirst = '" .$fname. "' AND p.nameLast = '" .$lname. "' AND schoolID = " .$schoolID. "");
           
$roster->execute();


echo $playerID;  <-- this is here just to see if playerID is being passed.  So far, no.

 I'm not getting any errors.  🙃

 

[Jim R]

I also tried adding fetch():

$playerID = $con->query("SELECT id FROM a_players 
          WHERE p.nameFirst = '" .$fname. "' AND p.nameLast = '" .$lname. "' AND schoolID = " .$schoolID. "")->fetch();

But it threw an error.

Quote

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

 

[Jim R]

I tried this:

$playerID = $con->prepare("SELECT id FROM a_players 
								WHERE p.nameFirst = :fname AND p.nameLast = :lname AND schoolID = :schoolID");

					$playerID->execute(['fname' => $fname, 'lname' => $lname, 'schoolID' => $schoolID]);
					$playerIDget = $playerID->fetch();

Same error.  

 

Does the boolean aspect of that error mean I'm not getting any results?

 

I know it's recognizing the variables because I'm echoing them out.  

[Barand]

I was afraid you might be querying the player table to get the id. That means if you add a dozen players to the roster,

you do 36 queries. (12 insert player, 12 select player, 12 insert roster) Doing it as I suggested does it in 3.

7 minutes ago, Jim R said:

Call to a member function fetch() on boolean in ...

Surely, after 13 years on this site, you know that that error message means the query failed.

[Jim R]

1 minute ago, Barand said:

Doing it as I suggested does it in 3.

But it didn't insert any rows.

 

 

[Jim R]

 

Getting this error now:

Quote

Recoverable fatal error: Object of class mysqli_stmt could not be converted to string in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_form_process.php on line 90

Current code:

	$player = $con->prepare("INSERT INTO a_players(schoolID,nameFirst,nameLast,feet,inches,grade,position,varsity) VALUES (?,?,?,?,?,?,?,?)");
		$player->bind_param('ssssssss',$schoolID,$fname,$lname,$feet,$inches,$grade,$position,$varsity);
	
		$playerID = $con->prepare("SELECT id FROM a_players WHERE nameFirst = '" .$fname. "' AND nameLast = '" .$lname. "' AND schoolID = " .$schoolID. "");
	
		 	
 		$roster = $con->prepare("INSERT INTO a_rosters(schoolID, playerID, uniform, varsity, season) VALUES (?,?,?,?,?)");
 		$roster->bind_param('sssss', $schoolID, $playerID, $uniform, $varsity, $season);


This is in the FOREACH LOOP:

	$player->execute();

      $playerID->execute();

	$roster->execute();   <-- line 90

 

 

Edited December 16, 2020 by Jim R

[Jim R]

29 minutes ago, Jim R said:

But it didn't insert any rows.

 

This is what I had for what you posted, but it didn't insert the data:
(you originally had level and varsity as columns.  It's just varsity.  I changed level to varsity to avoid a future issue)

		
		$players = " INSERT IGNORE INTO a_players
           (nameFirst,nameLast,feet,inches,schoolID,grade,varsity)
    SELECT nameFirst
         , nameLast
         , feet
         , inches
         , school_id
         , grade
         , 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";		
								
									

 

[Barand]

2 hours ago, Jim R said:

This is what I had for what you posted,

No, it isn't what I posted. Look at mine again.

[Jim R]

59 minutes ago, Barand said:

No, it isn't what I posted. Look at mine again.

I had to alter the table names, remove the level column.  

 

Quote

Parse error: syntax error, unexpected 'IGNORE' (T_STRING) in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_form_process.php on line 26

 

		INSERT IGNORE INTO a_players
           (nameFirst,nameLast,feet,inches,schoolID,grade,varsity)
    SELECT nameFirst
         , nameLast
         , feet
         , inches
         , school_id
         , grade
         , varsity
    FROM temp_csv;
		
		
		
		
		INSERT IGNORE 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;	

 

Edited December 16, 2020 by Jim R

[Barand]

The purpose of the last query is to get the roster data from the temp table but the player_id from the player table by matching the players' names.

You are trying to get the player id from the temp table - it ain't there!

[Jim R]

I see now, but that doesn't address the error I received and still received after changing the t to p.

 

 

[Barand]

I have no idea why you are getting a PHP error message on the SQL code, but then from 4,000 miles away it's difficult for me to see the code on your screen.

[Jim R]

include("/path/to/con.php");


if(isset($_POST['submit'])) {
	
		$schoolID = $_POST['school']; 
		$varsity = $_POST['varsity']; 
		$season = $_POST['season'];
		
	//	This uploads it into the Rosters table

	INSERT IGNORE INTO a_players
           (nameFirst,nameLast,feet,inches,schoolID,grade,varsity)
    SELECT nameFirst
         , nameLast
         , feet
         , inches
         , school_id
         , grade
         , varsity
    FROM temp_csv;
		
		
		
		
		INSERT IGNORE INTO a_rosters(schoolID, playerID, uniform, varsity, season) 
							SELECT t.schoolID,
									p.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;		
								
}

 

Edited December 16, 2020 by Jim R

[Barand]

ROFL

I give up.

[Jim R]

9 minutes ago, Barand said:

ROFL

I give up.

I had to change columns to fit, table names.  You said it wasn't what you sent instead of just showing me I had a t instead of a p.  

So I stripped out other code and just applied what you typed. 

 

I don't know why you went away from what was working, when what I need instead of insert_id was any id.  

 

Even when this method wasn't throwing an error, it wasn't inserting the rows.  So I stripped it back just what you typed outside of changing the table names and getting rid of the column level.  

Edited December 16, 2020 by Jim R

[Jim R]

So this works with one exception.  It doesn't get the playerID as needed.
 

	$player = $con->prepare("INSERT INTO a_players(schoolID,nameFirst,nameLast,feet,inches,grade,position,varsity) VALUES (?,?,?,?,?,?,?,?)");
		$player->bind_param('ssssssss',$schoolID,$fname,$lname,$feet,$inches,$grade,$position,$varsity);
	
		 	
 		$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];
			
			$name = "$fname $lname";
            $slug = strtolower("$fname-$lname");
            
            $taxonomy = "post_tag";
            
            
			$player->execute();
			$roster->execute();
		
			echo $playerID; <-- just to check as we go

 

Need to figure out $playerID

[Jim R]

This didn't insert any data, and those variables have been up there the whole time.  Check page 2.

include("/path/to/con.php");


	if(isset($_POST['submit'])) {
	
		$schoolID = $_POST['school']; 
		$varsity = $_POST['varsity']; 
		$season = $_POST['season'];
		
	//	This uploads it into the Rosters table



	$player="INSERT IGNORE INTO a_players
           (nameFirst,nameLast,feet,inches,schoolID,grade,varsity)
    SELECT nameFirst
         , nameLast
         , feet
         , inches
         , school_id
         , grade
         , varsity
    FROM temp_csv";
		
		
		
		
	 $roster="INSERT IGNORE INTO a_rosters(schoolID, playerID, uniform, varsity, season) 
							SELECT t.schoolID,
									p.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";	

 

[Barand]

Good, it seems you've realized that SQL code is not PHP code, but do you really think that defining a string variable will cause the query to be run? It needs to be processed by the DB server.

EG

$con->query($player);

Stop just throwing code at the page and think.

[Jim R]

I"m not really throwing code.  That's just going back to what was working...before I realized I needed to playerID of previously entered players and not just the new ones.

I've tried the $con->query version.  I've tried a lot.  

Not sure why the query isn't providing a value.  Earlier, I accidentally echoed the SELECT with the variables $fname, $lname and $schoolID, and it echoed the correct values.  

It literally printed "SELECT id FROM a_players WHERE nameFirst = 'Joe' AND nameLast = 'Smith' and schoolID = 44"

Right now I have this:

Quote

        $pid = $con->query("SELECT id FROM a_players WHERE nameFirst = :fname AND nameLast = :lname AND schoolID = :schoolID");

Then in the FOREACH loop...

    $pid->execute(['fname' => $fname, 'lname' => $lname, 'schoolID' => $schoolID]); 
                $playerID = $pid->fetch();

boolean error.

[Jim R]

The above is just in lieu of a subquery in the $player statement. 

 

[Barand]

6 hours ago, Jim R said:

 $pid = $con->query("SELECT id FROM a_players WHERE nameFirst = :fname AND nameLast = :lname AND schoolID = :schoolID");

Then in the FOREACH loop...

    $pid->execute(['fname' => $fname, 'lname' => $lname, 'schoolID' => $schoolID]); 
                $playerID = $pid->fetch();

That is PDO code. Isn't your $con a mysqli connection?

[Jim R]

It is, but it has been working for other queries/prepares.  Everything has worked up to this point.  I can set up a PDO connection if you think it will matter.  

	$player = $con->prepare("INSERT INTO a_players(schoolID,nameFirst,nameLast,feet,inches,grade,position,varsity) VALUES (?,?,?,?,?,?,?,?)");
		$player->bind_param('ssssssss',$schoolID,$fname,$lname,$feet,$inches,$grade,$position,$varsity);
	

	
		$pid = $con->query("SELECT id FROM a_players WHERE nameFirst = :fname AND nameLast = :lname AND schoolID = :schoolID");
	
		 	
 		$roster = $con->prepare("INSERT INTO a_rosters(schoolID, playerID, uniform, varsity, season) VALUES (?,?,?,?,?)");
 		$roster->bind_param('sssss', $schoolID, $playerID, $uniform, $varsity, $season);

$player and $roster work for how they're currently written.  Just need to get the playerID out of $player to use in $roster.  

Remember, I had it working to get the most recently added ID.  I had the wrong command but was close.  

[Jim R]

Evidently mixing name and positional placeholders is frowned upon.  

Sitting now with this code:

		$pid = $con->prepare("SELECT id FROM a_players WHERE nameFirst = ? AND nameLast = ? AND schoolID = ?");
		$pid->bindparam($fname,$lname,$schoolID);    <-- line 59

 

With $pid->execute(); in the FOREACH loop

 

Getting this error:

Quote

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

 

[Barand]

You are allowed to read the manual

https://www.php.net/manual/en/mysqli-stmt.bind-param.php