related concat. ?? for creating variable for loop

Anon

(no one seems to have seen this attachment to previous thread so will repost here).
In a mysql "kids" table I want each kid to have their
own record. I want their record to contain that kid's unique
(autoincremented id), their name (entered in through a form), and the id
of their father (last column - the value of which is tied into the id in
the father table). The input form the father fills out has fields for up
to 5 kids - fields are named kidname1, kidname2, kidname3, etc. There is
also a field to enter the total number_kids (so I will know how many
kids' names to expect.
I want to build a loop (below) to store the kids' names that are
entered. Every time I go through the loop for another kid (up to
number_kids),I want to say to build that kidname field from the string
"$kidname" and concatinate the number, then find this resulting string
variable's entered value and store that name in the database. So if I'm
through the loop the third time I want to construct the variable name to
say "$kidname3" and then evaluate it as "Sam" and store that value. Will
this code do that?

For ($counter = 1, $counter <= $number_kids, $counter++ ) {
$kidname = '$kidname'
$kidname .= $counter
$insertstmt = "insert into kids (kidid, kidname, memberid) values (null,
$kidname, last_insert_id()) ;

open connection to db and insert, etc.
}

By the way, if I open more than one connection in my program, do I need
to
"mysql_free_result($sql_resultX);" each time, in maybe reverse order to
how I opened them? Do I need to free one before I open another?

Anon

You're taking the wrong approach in the database design itself,
which in turn is making your task harder. Instead of having
a fixed number of columns in the table, you either need a separate
table for children:

create table parent
   (
   id int primary_key auto_increment,
   name text,
   ...
  &nbsp😉;

create table child
   (
   id int primary key auto_increment,
   parent_id int, -- references parent.id
   name text,
   ...
  &nbsp😉;

Now you can insert as many or as few rows for children as appropriate,
and you just use the value from parent.id to set the value of 'parent_id'
as you insert the child records. And there's no need for a separate
'count' field in the parent record; just say:

  select count(child.id) from parent, child where parent.id = child.parent.id;

Anon

Have built those two tables exactly as Kirk suggested already. Think my point maybe was missed. The trouble is the parent needs to enter their kids, one to a field, through the form, and the number of kids entered will vary from parent to parent. Then the script needs to get those values (the no. of which will vary) and write then into the database. So, in the method that I was trying, I named each field kid1, kid2, etc. Then I wanted this loop (below) to get kid1's name and write it to his/her record in the kids table, take kid2's value and write it as another record into the kids table, etc., up to the no. of the kids' fields that were completed. (Later one I'll do a join of the two tables to retrieve all the kids' records associated with a certain parent.)

Code I have again:

Anon

Ah. I was getting the fields in the form and the columns in the database
a bit confused. Sorry.

A better way to deal with the form is to name the kids-names
fields as an array:

Then you code can say:

$parent_id = mysql_insert_id(); // from the insert into parent's table

if (is_array( $kid_name ))
  {
  reset $kid_name;
  while (list($no,$name) = each($kid_name))
    {
    $qry = "insert into kids ( kidname, memberid) values"
      . " ($name, $parent_id)";
    $result = mysql_query( $qry );
    ...
    }
  }

Note you do not want or need to specify the kidid column in the insert;
leaving it inspecified will allow mysql to insert the default (autoincrement)
value.