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

[Jim R]

It's so I can create WordPress tags as I go.  I'm using the tag template to auto-generate player profiles.  I'm calling a function toward the top of tag.php (generic name) to put my custom information on there.  Under that will be the normal content a tag.php would present on a WP site.

Doing this will keep me from having to add tags later or update the table periodically.

This is much easier than creating my own profile page, then digging into WP templating and styling it the way I want it.  I've had this way for 5-6 years, but now with coaches entering so many extra players, I'm trying to take care of it all at once.  So I'm not creating anything, just inserting to what is already there.

[Jim R]

44 minutes ago, Barand said:

Not in that query.

I have name and slug in the insert query.  Not sure what you mean by it's not in there.  

Never dealt with PDO syntax before this topic, nor dealt with bind_param.  I was trying to apply your earlier code to this.  

[kicken]

9 minutes ago, Jim R said:

I have name and slug in the insert query.  Not sure what you mean by it's not in there.

Your query syntax is wrong.  The syntax of an INSERT query is

INSERT INTO table (ColumnNameA, ColumnNameB[, ...])
VALUES (ValueOfColumnA, ValueOfColumnB[, ...])

In your prepared query the question-marks represent the values you want to insert into the table.  For the column name you currently have some dynamic expression which isn't right.  Instead you need the names of the columns into which you want to store the name and slug values.

 

[Barand]

15 minutes ago, Jim R said:

Never dealt with PDO syntax before this topic, nor dealt with bind_param.  I was trying to apply your earlier code to this.

SQL is SQL whether you use PDO or MySqli and your SQL syntax is wrong. You should have

INSERT INTO wp_terms(name, slug) VALUES (?,?)

But as I said before, don't create a table duplicating existing data. You already have the names in the roster table (even though they should only be in a player table) so create a VIEW called "wp_terms". You can then use the view just as if it were a real table.

CREATE VIEW wp_terms AS
SELECT DISTINCT 
    CONCAT(nameFirst, ' ', nameLast) as name
  , LOWER(CONCAT(nameFirst, '-', nameLast)) as slug
FROM roster;

Example...

mysql> select * from wp_terms;
+--------------+--------------+
| name         | slug         |
+--------------+--------------+
| Laura Norder | laura-norder |
| Tom DiCanari | tom-dicanari |
+--------------+--------------+

 

[Jim R]

37 minutes ago, Barand said:

But as I said before, don't create a table duplicating existing data. You already have the names in the roster table (even though they should only be in a player table) so create a VIEW called "wp_terms". You can then use the view just as if it were a real table.

It's already created.  It's what WordPress uses.  I've piggy backed on the tag.php page to put my own content on it, pulling the slug off the URL.  I'm just trying to update the wp_terms table as names get entered into the site.  (I'll update the wp_terms_taxonomy table as well in the process, but that's take care of.)

 

[Jim R]

1 hour ago, Barand said:

SQL is SQL whether you use PDO or MySqli and your SQL syntax is wrong. You should have

INSERT INTO wp_terms(name, slug) VALUES (?,?)

So I don't concat in the insert?

Does the concat take place in the foreach loop?  

[Barand]

54 minutes ago, Jim R said:

So I don't concat in the insert?

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 (?,?,?)");
        $stmt1->bind_param('sss', $uniform, $fname, $lname);
    
        $stmt2 = $con->prepare("INSERT INTO wp_terms(name, slug) VALUES (?,?)");
        $stmt2->bind_param('ss', $name, $slug);

        foreach ($_POST['uniform'] as $k => $uniform)  {
            $fname = $_POST['nameFirst'][$k];
            $lname = $_POST['nameLast'][$k];
            $name = "$fname $lname";
            $slug = strtolower("$fname-$lname");
            $stmt1->execute();
            $stmt2->execute();
        }  
    }

To do it in the query you would

        $stmt2 = $con->prepare("INSERT INTO wp_terms(name, slug) 
                                VALUES (concat(?,' ',?), lower(concat(?,'-',?)) );
        $stmt2->bind_param('ssss', $name, $slug, $name, $slug);

 

Edited December 10, 2020 by Barand

[Jim R]

Interesting you led with combining it with the initial query.  While searching for ideas on how a query would look, I considered making it all one query.  Many of the instances said while it made sense to try it just as easy to use a separate query.  

Which do you think is better?   1-12 rows at a time, likely not much of a resource / time difference.  

 

In the first one, wouldn't I still need the [$k] for the array? 

EDIT:  I added this, and it worked. 

 

Why $name and $slug twice in the second example?  One for each concat?  

Edited December 10, 2020 by Jim R

[kicken]

2 hours ago, Jim R said:

Why $name and $slug twice in the second example?  One for each concat?

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 ? and bind the variable multiple times accordingly.

 $stmt2 = $con->prepare("INSERT INTO wp_terms(name, slug) 
                         VALUES (concat(?,' ',?), lower(concat(?,'-',?)) );
                                        ^     ^                ^     ^
                                        |     |                |     |
                             +----------+     |                |     |
                             |      +---------+                |     |
                             |      |      +-------------------+     |
                             |      |      |      +------------------+
                             |      |      |      |
                             v      v      v      v
$stmt2->bind_param('ssss', $name, $slug, $name, $slug);

The variables should probably be $nameFirst / $nameLast instead of $name / $slug as in this example you're combining the names and generating the slug in the query using the lower/concat functions.

[Barand]

14 hours ago, kicken said:

The variables should probably be $nameFirst / $nameLast instead of $name / $slug

Quite right, sorry about that. The bind statement should be

$stmt2->bind_param('ssss', $fname, $lname, $fname, $lname);

 

[Jim R]

I used the top version, very simple to understand...after seeing it of course.  

[Jim R]

I have built on this, as I mentioned above (thinking it would be easier), trying to write a third table.  

In table wp_term_taxonomy, a new row is created when wp_terms adds one.  It tells what kind of "term" was added.  For my purposes, it's always "post-tag", and it links the id from wp_term, which always matches the id in wp_term_taxonomy.  I need to duplicate that term_id as a I add a row.

wp_term_taxonomy columns:  (Only 1294 is what I inserted, but the previous rows shows you what it should be)

 

Here is the code I'm using (built off of what we have above).  Trying to use lastInsertID().

Right now, we're just looking at $stmt3 variables ($taxonomy, $termID)

		$team = $_POST['school']; 
		$level = $_POST['level']; 
		$season = $_POST['season'];
		
	//	This uploads it into the Rosters table
	
		$stmt = $con->prepare("INSERT INTO a_rosters(team, uniform, nameFirst, nameLast, feet, inches, level, season, grade, position) VALUES (?,?,?,?,?,?,?,?,?,?)");
		$stmt->bind_param('ssssssssss', $team, $uniform, $fname, $lname, $feet, $inches, $level, $season, $grade, $position);
		
		$stmt2 = $con->prepare("INSERT INTO wp_terms(name, slug) VALUES (?,?)");
        $stmt2->bind_param('ss', $name, $slug);
        
        $stmt3 = $con->prepare("INSERT INTO wp_term_taxonomy(taxonomy,term_id) VALUE (?,?)");
        $stmt3->bind_param('ss',$taxonomy,$termID);
	
	
		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";
            $termID = $con->lastInsertID();   <---- line 35

			
			$stmt->execute();
			$stmt2->execute();
			$stmt3->execute();

 

Before I worried about $termID, I started with just the $taxonomy part, and it added "post_tag" to row 1294.  I hadn't defined term_id yet, so it added a zero.  I haven't been able get the $termID to recognize the lastInsertID().  I've seen various 'solutions' and tried 3 or 4 of them.  Mostly I get this error...

Quote

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

 

Part of me also wonders, is lastInsertID() getting the id of the current row or the previous row, and would I need to somehow add a +1 to the mix.  

 

 

 

[Jim R]

I figured a little more out:

            $termID = "SELECT LAST_INSERT_ID()";
 

It at least doesn't throw an error.  It input a zero though. 

 

 

Edited December 12, 2020 by Jim R

[Jim R]

Now I tried this:

          $termID = mysqli_insert_id($con);
 

It input a 0, but when I     printf ("New Record has id %d.\n", mysqli_insert_id($con)); it shows the correct ID

[Barand]

you problem with line 35...

$termID = $con->lastInsertID();   <---- line 35

... is that lastInsertID() is PDO method and you are using mysqli. You therefore need

$termID = $con->insert_id;

It won't return the last id inserted by stmt2 until it has actually inserted a record. You are calling it before any inserts have been made. It needs to be called between the execution of stmt2 and the execution of stmt3.

[Jim R]

It's still posting 0 as the term_id

 

	$team = $_POST['school']; 
		$level = $_POST['level']; 
		$season = $_POST['season'];
		
	//	This uploads it into the Rosters table
	
		$stmt = $con->prepare("INSERT INTO a_rosters(team, uniform, nameFirst, nameLast, feet, inches, level, season, grade, position) VALUES (?,?,?,?,?,?,?,?,?,?)");
		$stmt->bind_param('ssssssssss', $team, $uniform, $fname, $lname, $feet, $inches, $level, $season, $grade, $position);
		
		$stmt2 = $con->prepare("INSERT INTO wp_terms(name, slug) VALUES (?,?)");
        $stmt2->bind_param('ss', $name, $slug);
        
        $stmt3 = $con->prepare("INSERT INTO wp_term_taxonomy(taxonomy) VALUE (?)");
        $stmt3->bind_param('s',$taxonomy);
	
		$stmt4 = $con->prepare("UPDATE wp_term_taxonomy SET taxonomy = ? WHERE term_id = 0");
		$stmt4->bind_param ('s',$termID);
	
		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";
            
			
			$stmt->execute();
			$stmt2->execute();
	            $termID = $con->insert_id;
			$stmt3->execute();
			$stmt4->execute();

 

[Jim R]

Oops...hold on.  I had messed around with $stmt4 before you replied, then didn't go back when I got back home to test it. 

Let me apply what you suggested to $stmt3.

[Jim R]

Applied to $stmt3, it worked.

 

Thank you, sir!

[Jim R]

Grrrr...why is this throwing an error?  It's pretty much the same line as above, but I changed the variable name, changed the table it goes into and deleted two variables:

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

Quote

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

 

[Barand]

if $player is a boolean (ie false) then the prepare failed. Check column names etc.

Good to see you're finally putting that data into the player table instead of the roster table.

[Jim R]

21 minutes ago, Barand said:

Good to see you're finally putting that data into the player table instead of the roster table.

It helps eliminate steps later for sure, as I thought of ways to better use the complete list.

 

21 minutes ago, Barand said:

if $player is a boolean (ie false) then the prepare failed. Check column names etc.

 

I double checked that.  But then since you think that's the issue, I triple checked it.  That was the issue.  In my players table, it's schoolID.  I figured it was something easy I overlooked.  

I changed all my columns to schoolID across those tables.  

 

Last step of converting this...getting the playerID from the players table to the rosters table.  I figured that would be the hard part, and I've been reading more on subqueries.  I want to figure that one out.  I'm sure I'll have a question...later.  Hope not.  

 

Taking laptop to dinner. 

 

Edited December 13, 2020 by Jim R

[Jim R]

I can't seem to get a value other than 0.  I've tried various version of subquery, including...

$playerID = $con->query("SELECT max(id) FROM a_players");

 

 I keep going back to what's below.

 

$playerID is what I'm looking for:

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


This is the same FOREACH loop you had above:

            $player->execute();
	            $playerID = $con->insert_id;
			$roster->execute();

No errors.  Just getting a 0.

 

 

[Jim R]

.edit (thought I figured out why it wasn't working, but I was wrong at that too)

Edited December 14, 2020 by Jim R

[Jim R]

Never mind...suddenly it worked.  

Is there a reason it would go from submitting 0's to suddenly working?  

[Jim R]

I have a duplicate key set up on a_players.  Can I wrap everything that comes after the $player query in an IF condition?