SQL syntax to insert consecutive non-null records

I have an input page where users may add their comments for various images which are stored in test_tbl_files.
Those comments are passed to a processor page where the file name of the image (textarea named “fnn”)  is stored in a record along with the comments for that image (textarea named “tnn”).
The relevant input page code is:

$sql = "SELECT *
        FROM test_tbl_files
        ORDER BY test_tbl_files.sort ASC";

$results = mysqli_query($cxn, $sql);

$f = 'f';

$t = 't';

WHILE($row=mysqli_fetch_assoc($results))
   {
            echo "<br />\n";
            echo "<br />\n";
            echo "<table align='center'>\n";
            echo "<tr align='center'>\n";
            echo "<td align='center'>\n";
            echo "<img src=\"".$row['folder'].$row['file']."\"/><textarea name='$f$row[sort]' id='$f$row[sort]' style='display:none'>$row[file]</textarea>\n";
            echo "</td>\n";
            echo "</tr>\n";
            echo "<tr align='center'>\n";
            echo "<td align='center'>\n";
            echo "<textarea name='$t$row[sort]' id='$t$row[sort]' rows='1' style='height:96px;width:1024px' placeholder='Add your comments for the above image' onKeyUp='textLimit(this, 1000);' ></textarea>\n";
            echo "</td>\n";
            echo "</tr>\n";                        
            echo "</table>\n";
   }

The input page seems to work fine in that using my code I can pass the values to my “hard-coded” processor page, except that:
1.      I am also passing null values for the “tnn” fields (which is permissible because the user may not choose to enter data for a particular image) but if that is the case then the record should not be stored.
2.      Each month the quantity of images varies therefore I have to re-code the processor page to accommodate more or less records in the test_tbl_files table.
The current relevant processor page code is:

$eastern = date ("Y-m-d H:i:s");

$sql = "INSERT INTO test_tbl_comments
(eastern,file,comment) VALUES ('$eastern','$_POST[f01]','$_POST[t01]'), ('$eastern','$_POST[f02]','$_POST[t02]'), ('$eastern','$_POST[f03]','$_POST[t03]'),
('$eastern','$_POST[f04]','$_POST[t04]'),('$eastern','$_POST[f05]','$_POST[t05]'),('$eastern','$_POST[f06]','$_POST[t06]'),('$eastern','$_POST[f07]','$_POST[t07]'),('$eastern','$_POST[f08]','$_POST[t08]'),('$eastern','$_POST[f09]','$_POST[t09]'),('$eastern','$_POST[f10]','$_POST[t10]'),('$eastern','$_POST[f11]','$_POST[t11]'),('$eastern','$_POST[f12]','$_POST[t12]')";

$result_set = mysqli_query($cxn, $sql);

For the processor page I am trying to create, in pseudocode:
$sql = "INSERT INTO test_tbl_comments
(eastern,file,comment) VALUES ('$eastern','$_POST[fnn]','$_POST[tnn]')
WHERE length of tnn is >0

I believe my logic is correct but I’ve spent hours trying to figure-out the syntax to do this.
Might someone offer help?

Thanks.
lepirtleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tel2Commented:
Hi lepirtle,

> For the processor page I am trying to create, in pseudocode:
> $sql = "INSERT INTO test_tbl_comments
> (eastern,file,comment) VALUES ('$eastern','$_POST[fnn]','$_POST[tnn]')
> WHERE length of tnn is >0


I don't know much PHP, but unless I've missed something, I would have thought you'd have to write the "WHERE..." part in PHP, not SQL.  Something like this:
if (length($_POST[tnn]) > 0)
{
  $sql = "INSERT INTO test_tbl_comments
    (eastern, file, comment) VALUES ('$eastern', '$_POST[fnn]', '$_POST[tnn]')"
  # Now execute the SQL
}
0
lepirtleAuthor Commented:
Hi tel2,
Thanks for that information. I would like to give it a try but the syntax to loop through each of the  fnn and tnn variables is the other part of the problem in that I need to "loop" through the array of possible "fnn" and "tnn" variables. And I am stumped as to the syntax to do that.
0
tel2Commented:
Sorry lepirtle, but my PHP is pretty basic, so I'll have to leave that for someone else to answer.
0
Chris StanyonWebDevCommented:
Firstly, you can only insert 1 record at a time into your database, so you need to do it in a loop. To make this easier, you name your HTML form elements using an array syntax:

image[xx][filename]
image[xx][comment]

Where xx is your ID from the database - $row['sort'].

Now you would be able to loop through the POST records like so:
foreach ($_POST['image'] as $image):
    // you now have $image['filename'] and $image['comment']
    if ($image['comment'] != ""):
        //insert the record into your DB
        $sql = sprintf( "INSERT INTO test_tbl_comments (file, comment) VALUES ('%s', '%s')", $image['filename'], $image['comment'] );
        // execute your query
    endif;
endforeach;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lepirtleAuthor Commented:
Hi tel2 and Chris,
tel2: I appreciate your quick reply and honesty about you php knowledge. Very admirable and I thank you for it.
Chris: Thank you for your example and the excellent comments which helps me understand the logic. Though I haven't tested your code I will certainly use your suggestions. Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.