Jump to content

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


Recommended Posts

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

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

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

 

Link to post
Share on other sites

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

 

Link to post
Share on other sites

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.  

Link to post
Share on other sites

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.

Link to post
Share on other sites

 

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

 

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

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

Link to post
Share on other sites

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

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

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

Link to post
Share on other sites

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.  

Link to post
Share on other sites

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

 

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.