SQL syntax to insert consecutive non-null records

lepirtle used Ask the Experts™
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';

            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]'),

$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?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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


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.
Sorry lepirtle, but my PHP is pretty basic, so I'll have to leave that for someone else to answer.
Most Valuable Expert 2018
Distinguished Expert 2018
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:


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

Open in new window


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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial