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

[Jim R]

I'm wanting coaches/teams to input their roster directly onto the site.  

Right now it's just three columns:

uniform, nameLast,nameFirst

 

I'm using jQuery to add and remove the fields, (once I'm done styling, the form will be a row for each name), and that part appears to be functioning quite well.  When it's all done, teams will input anywhere from 10-20 players into the form.  It's sent to a .php for processing.

 

I was envisioning the data would come out in rows - uniform, nameFirst, nameLast.  Imploding it with *comma* separators, then figuring out how to terminate each line with a |.  

 

Instead it's coming out in arrays based on the field.  (That makes sense to me as I see the code.)

So it's coming out...

3,5,10

Joe, Tim, Steve

Johnson, Thomas, Smith

 

I just have this so I could make sure I was at least getting the expected data.  

	$uniform = implode(",",$_POST['uniform']);
	$fname = implode(",",$_POST['nameFirst']);
	$lname = implode(",",$_POST['nameLast']);
	

	echo $uniform;
	echo $fname;
	echo $lname;

 

How do I get it to read:

3,Joe,Johnson

5,Tim,Thomas

10,Steve,Smith

 

 

 

Here is the form HTML

<form role="form" action="/wp-content/plugins/csi_stats/csi_roster_process.php" method="post">
    <div id="myRepeatingFields">
        <div class="entry input-group col-xs-3">
            <input class="form-control" name="uniform[]" type="text" placeholder="Uni #" />
            <input class="form-control" name="nameFirst[]" type="text" placeholder="First Name" />
            <input class="form-control" name="nameLast[]" type="text" placeholder="Last Name" />
                
                <span class="input-group-btn">
                    <button type="button" class="btn btn-success btn-lg btn-add">
                        <span class="glyphicon glyphicon-plus" aria-hidden="true"></span>
                    </button>
                </span>
                
        </div>
    </div>
    
 <input type="submit" value="Send Roster" name="submit">
</form>

Here is the jQuery in case you're curious

$(function()
{
    $(document).on('click', '.btn-add', function(e)
    {
        e.preventDefault();
        var controlForm = $('#myRepeatingFields:first'),
            currentEntry = $(this).parents('.entry:first'),
            newEntry = $(currentEntry.clone()).appendTo(controlForm);
        newEntry.find('input').val('');
        controlForm.find('.entry:not(:last) .btn-add')
            .removeClass('btn-add').addClass('btn-remove')
            .removeClass('btn-success').addClass('btn-danger')
            .html('');
    }).on('click', '.btn-remove', function(e)
    {
        e.preventDefault();
        $(this).parents('.entry:first').remove();
        return false;
    });
});

 

[Barand]

Something like this?...

<?php
$data = [];
foreach ($_GET['uniform'] as $k => $uniform)  {
    $fname = $_GET['nameFirst'][$k];
    $lname = $_GET['nameLast'][$k];
    $data[$k] = [$uniform, $fname, $lastname];
}
?>

 

Edited November 21, 2020 by Barand

[Barand]

Alternatively, a change to your input naming...

<?php
if (isset($_GET['data']))  {
    echo '<pre>', print_r($_GET, 1), '</pre>';
}
?>
<form>
<input class="form-control" name="data[1][uniform]" type="text" placeholder="Uni #" />
<input class="form-control" name="data[1][nameFirst]" type="text" placeholder="First Name" />
<input class="form-control" name="data[1][nameLast]" type="text" placeholder="Last Name" />
<br>
<input class="form-control" name="data[2][uniform]" type="text" placeholder="Uni #" />
<input class="form-control" name="data[2][nameFirst]" type="text" placeholder="First Name" />
<input class="form-control" name="data[2][nameLast]" type="text" placeholder="Last Name" />
<br>
<input class="form-control" name="data[3][uniform]" type="text" placeholder="Uni #" />
<input class="form-control" name="data[3][nameFirst]" type="text" placeholder="First Name" />
<input class="form-control" name="data[3][nameLast]" type="text" placeholder="Last Name" />
<br>
<input type='submit' name='btnSub' value='Submit'>
</form>                

giving

 GET = Array
(
    [data] => Array
        (
            [1] => Array
                (
                    [uniform] => 1
                    [nameFirst] => aaa
                    [nameLast] => bbb
                )

            [2] => Array
                (
                    [uniform] => 2
                    [nameFirst] => ccc
                    [nameLast] => ddd
                )

            [3] => Array
                (
                    [uniform] => 3
                    [nameFirst] => eee
                    [nameLast] => fff
                )

        )

    [btnSub] => Submit
)

               

[Jim R]

How do I echo that out to see what's it's producing?  Echoing $data just produced 'array', which I'm sure you would figure. 

I tried this outside the loop:

$data = implode(",",$data[$k]);
echo $data;

 

It produced the last set of entries 10,Steve,Smith.

I put it inside the loop, and it threw an error.

 

 

Ultimately, I don't need to see it on screen, just insert it into a table.  

[Jim R]

2 minutes ago, Barand said:

Alternatively, a change to your input naming...

<?php
if (isset($_GET['data']))  {
    echo '<pre>', print_r($_GET, 1), '</pre>';
}
?>
<form>
<input class="form-control" name="data[1][uniform]" type="text" placeholder="Uni #" />
<input class="form-control" name="data[1][nameFirst]" type="text" placeholder="First Name" />
<input class="form-control" name="data[1][nameLast]" type="text" placeholder="Last Name" />
<br>
<input class="form-control" name="data[2][uniform]" type="text" placeholder="Uni #" />
<input class="form-control" name="data[2][nameFirst]" type="text" placeholder="First Name" />
<input class="form-control" name="data[2][nameLast]" type="text" placeholder="Last Name" />
<br>
<input class="form-control" name="data[3][uniform]" type="text" placeholder="Uni #" />
<input class="form-control" name="data[3][nameFirst]" type="text" placeholder="First Name" />
<input class="form-control" name="data[3][nameLast]" type="text" placeholder="Last Name" />
<br>
<input type='submit' name='btnSub' value='Submit'>
</form>                

giving

 GET = Array
(
    [data] => Array
        (
            [1] => Array
                (
                    [uniform] => 1
                    [nameFirst] => aaa
                    [nameLast] => bbb
                )

            [2] => Array
                (
                    [uniform] => 2
                    [nameFirst] => ccc
                    [nameLast] => ddd
                )

            [3] => Array
                (
                    [uniform] => 3
                    [nameFirst] => eee
                    [nameLast] => fff
                )

        )

    [btnSub] => Submit
)

               

 

I need for the User (coaches) to be able to add rows.  Teams don't have a set number of players.  

[Barand]

4 minutes ago, Jim R said:

Teams don't have a set number of players.  

So?

To insert

$stmt = $pdo->prepare("INSERT INTO mytable (uniform, nameFirst, nameLast) 
                        VALUES (:uniform, :nameFirst, :nameLast)");
foreach ($_POST['data'] as $data) {
    $stmt->execute($data);
}               

 

[Jim R]

13 minutes ago, Barand said:

So?

Seems if I have unused rows, it will cause issues, or worse, I won't have enough rows.  

 

 

The above query is getting this error:

Quote

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

 

[Barand]

4 hours ago, Jim R said:

Seems if I have unused rows, it will cause issues, or worse, I won't have enough rows.  

Had you consider starting with an index of 0, then, whenever you add a new player, add 1 to get the index of the new array? This way you can have as few or many as you like.

As for your error, have you just blindly copy/pasted my coded without regard to whatever variable contains your mysql connection - or even if yours is pdo or mysqli?

[Jim R]

38 minutes ago, Barand said:

Had you consider starting with an index of 0, then, whenever you add a new player, add 1 to get the index of the new array? This way you can have as few or many as you like.

I had not.  I'm not sure what you mean by it.  I notice when I see the print_r of the array that the first one is 0.  

 

39 minutes ago, Barand said:

As for your error, have you just blindly copy/pasted my coded without regard to whatever variable contains your mysql connection - or even if yours is pdo or mysqli?

Not really.  I tried it various ways, including with your previous FOREACH loop.  

Mine is mysqli, but I thought may the pdo-prepare's use was relative to my problem.  

[Barand]

2 minutes ago, Jim R said:

Mine is mysqli, but I thought may the pdo-prepare's use was relative to my problem.

It was relevant to your problem - if you were using PDO (Sorry, not clairvoyant)

Any code given are examples, not necessarily writing your code for you. Rather that you should emulate, not copy. The onus is on you read them, see what they are doing and apply to your situation.

[Jim R]

1 hour ago, Barand said:

It was relevant to your problem - if you were using PDO (Sorry, not clairvoyant)

Any code given are examples, not necessarily writing your code for you. Rather that you should emulate, not copy. The onus is on you read them, see what they are doing and apply to your situation.

I know, and I did.  Each option was giving me the same error.  I don't know what the error is really telling me, so I don't know what to change.  

I don't use pdo, so I read pdo-prepare as preparing to put data I haven't gotten yet into a data table.  Then I read the FOREACH loop as getting the $_POST data, which was executed with the $stmt->execute($data);

When that didn't work, I put the FOREACH loop you provided earlier.  That didn't work.  Then I put query in the loop.  That didn't work.  

Just telling you what my thinking was.  Elementary...for sure, but that's why I'm here.  I don't start posts until after I search here and Stackoverflow.  

 

The initial FOREACH loop that you posted, sort of produced what I was looking for. 

I'm trying to get data from a repeatable field form into a data table. I thought I was doing well to know the implode the array.

Edited November 22, 2020 by Jim R

[Jim R]

Back to the issue...

 

This is what you gave me in your first response:

$data = [];
foreach ($_POST['uniform'] as $k => $uniform)  {
    $fname = $_POST['nameFirst'][$k];
    $lname = $_POST['nameLast'][$k];
        
    
    $data[$k] = [$uniform, $fname, $lname];

}

After the loop, not thinking I needed it in the loop, I added...

$data = implode(",",$data[$k]);
echo $data;

It echoed the last entry:  10,Steve,Smith   => close to what's needed

So I placed that inside the loop but got this error:

Warning: implode(): Invalid arguments passed in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_process.php on line 43

 

Changed the $data to $row, and it just produces "Array,Array,Array".

Edited November 22, 2020 by Jim R

[Barand]

$data = [];
foreach ($_POST['uniform'] as $k => $uniform)  {
    $fname = $_POST['nameFirst'][$k];
    $lname = $_POST['nameLast'][$k];
        
    
    $data[$k] = [$uniform, $fname, $lname];

}

// to view the $data
echo '<pre>' . print_r[$data, 1] . '</pre>';

// insert each $data element into your table (as shown)
foreach ($data as $rec) {
    // insert record
}

 

[Jim R]

Parse error: syntax error, unexpected ',', expecting ']' in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_process.php on line 46

Line 46 is :: echo '<pre>' . print_r[$data, 1] . '</pre>';

 

I haven't added the loop yet.

Edited November 22, 2020 by Jim R

[kicken]

42 minutes ago, Jim R said:

Line 46 is :: echo '<pre>' . print_r[$data, 1] . '</pre>';

Simple typo, should be:

echo '<pre>' . print_r($data, 1) . '</pre>';

 

[Barand]

Using your original form, and mysqli ...

if ($_SERVER['REQUEST_METHOD']=='POST') {
    $stmt = $db->prepare("INSERT INTO player(uniform, nameFirst, nameLast) VALUES (?,?,?)");
    $stmt->bind_param('sss', $uniform, $fname, $lname);
    
    foreach ($_POST['uniform'] as $k => $uniform)  {
        $fname = $_POST['nameFirst'][$k];
        $lname = $_POST['nameLast'][$k];
        $stmt->execute();
    }

}

 

[Jim R]

I tried this INSERT inside the loop:

foreach ($data as $rec) {


    // insert record
	
	$query = "INSERT INTO a_rosters (uniform,nameFirst,nameLast)
 		VALUES ('$uniform','$fname','$lname')";

It didn't insert anything.  

I even tried these values:  '$rec[$uniform]', '$rec[$fname]','$rec[$lname]

 

Is the below in lieu of all of your previous code?  I did that, changing the table name, but it gave me the UNDEFINED error again.

14 hours ago, Barand said:

Using your original form, and mysqli ...

if ($_SERVER['REQUEST_METHOD']=='POST') {
    $stmt = $db->prepare("INSERT INTO player(uniform, nameFirst, nameLast) VALUES (?,?,?)");
    $stmt->bind_param('sss', $uniform, $fname, $lname);
    
    foreach ($_POST['uniform'] as $k => $uniform)  {
        $fname = $_POST['nameFirst'][$k];
        $lname = $_POST['nameLast'][$k];
        $stmt->execute();
    }

}

 

 

 

[Barand]

In an attempt to remove confusion, here is a whole script.

<?php
include 'db_inc.php';          // use your own
$db = myConnect('jimr');       // mysqli connection

if ($_SERVER['REQUEST_METHOD']=='POST') {
    $stmt = $db->prepare("INSERT INTO player(uniform, nameFirst, nameLast) VALUES (?,?,?)");
    $stmt->bind_param('sss', $uniform, $fname, $lname);
    
    foreach ($_POST['uniform'] as $k => $uniform)  {
        $fname = $_POST['nameFirst'][$k];
        $lname = $_POST['nameLast'][$k];
        $stmt->execute();
    }
    header("Location:#");
    exit;
}
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-language" content="en">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.15.1/css/all.css">
<title>Example</title>
<meta name="creation-date" content="11/23/2020">
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.0/jquery.min.js"></script>
<script type='text/javascript'>
    $().ready( function() {
        $("#add-player").click( function(e) {
            e.preventDefault()
            $("#myRepeatingFields").append('<div class="entry input-group col-xs-3">' +
                    '<input class="form-control" name="uniform[]" type="text" placeholder="Uni #" />&nbsp;' +
                    '<input class="form-control" name="nameFirst[]" type="text" placeholder="First Name" />&nbsp;' +
                    '<input class="form-control" name="nameLast[]" type="text" placeholder="Last Name" />' +
                    '</div>');
        })
    })
</script>

</head>
<body>

<form action="" method="post">
    <div id="myRepeatingFields">
        <div class="entry input-group col-xs-3">
            <input class="form-control" name="uniform[]" type="text" placeholder="Uni #" />
            <input class="form-control" name="nameFirst[]" type="text" placeholder="First Name" />
            <input class="form-control" name="nameLast[]" type="text" placeholder="Last Name" />
        </div>
    </div>
    
    <button id='add-player' title='add new player entry'><i class='fas fa-plus-circle'></i></button>
    <input type="submit" value="Send Roster" name="submit">
</form>

</body>
</html>

The table used is...

CREATE TABLE `player` (
  `player_id` int(11) NOT NULL AUTO_INCREMENT,
  `uniform` varchar(45) DEFAULT NULL,
  `nameFirst` varchar(45) DEFAULT NULL,
  `nameLast` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`player_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

[Jim R]

Thank you for that.

 

I already had the connection setup and the database set up.  

 

That code is still throwing this error:

Fatal error: Uncaught Error: Call to a member function prepare() on null in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_process.php:29 Stack trace: #0 {main} thrown in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_process.php on line 14

 

($db = myConnect('####');

 

include("/path to... /con.php");
$db = myConnect('#####'); 



	if ($_SERVER['REQUEST_METHOD']=='POST') {
		$stmt = $db->prepare("INSERT INTO a_rosters(uniform, nameFirst, nameLast) VALUES (?,?,?)");
		$stmt->bind_param('sss', $uniform, $fname, $lname);
	
		foreach ($_POST['uniform'] as $k => $uniform)  {
			$fname = $_POST['nameFirst'][$k];
			$lname = $_POST['nameLast'][$k];
			$stmt->execute();
		}  
	}

 

Here is the connection file I use for all my queries...

$con = mysqli_connect("localhost","username","password", "csi_wp");

// Check connection
if (mysqli_connect_errno())
  {
//echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

Edited November 23, 2020 by Jim R

[Barand]

If you read my code you would see I store my database connection in the variable $db. You are storing yours in $con.

Therefore you need to use $con where I have used $db, such as in $db->prepare() 🙄

Edited November 23, 2020 by Barand

[Jim R]

7 minutes ago, Barand said:

If you read my code you would see I store my database connection in the variable $db. You are storing yours in $con.

Therefore you need to use $con where I have used $db, such as in $db->prepare() 🙄

Simple mistake.  

That was the issue then for most of this.  

[Jim R]

Memory refresh:  I'm this code handles the User typing in a roster of players, using a form with repeated fields.  It would be any number of players from 1 to 12.

 

Apply  the code from above, using input from the same form, I want a separate INSERT with just the first and last name into an additional data table as 'name' and 'slug'.    

 

	$stmt = $con->prepare("INSERT INTO wp_terms(concat(nameFirst,' ',nameLast) as name, concat(lower(nameFirst),'-',lower(nameLast)) as slug) VALUES (?,?)");
		$stmt->bind_param('ss', $name, $slug);
	
		foreach ($_POST['name'] as $k => $name)  {
			
			$slug = $_POST['slug'][$k];
						
			$stmt->execute();
		}  

 

This is the error I received:

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:30 Stack trace: #0 {main} thrown in /home2/csi/public_html/wp-content/plugins/csi_stats/csi_roster_form_process.php on line 30

 

 

[Barand]

INSERT INTO wp_terms (X, Y) .....

X and Y should be column names, not the data you want to put in them.

[Jim R]

Do I not have that as name and slug?

[Barand]

Not in that query.

BTW, why create yet another table which duplicates name data you already hold? You can quite easily get the names and slugs in those formats by querying the data  you already hold. You are just wasting space, time and effort.