Jump to content

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


Jim R
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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.  

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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 |
+--------------+--------------+

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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 by Barand
  • Like 1
Link to comment
Share on other sites

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

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.

  • Like 1
Link to comment
Share on other sites

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)

image.png.94121ccc7467355353187ae3abc3da9b.png

 

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.  

 

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

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.

 

 

Link to comment
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.

 Share

×
×
  • 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.