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?
 
Chris StanyonCommented:
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.