Separating variables for SQL insert using PHP and JavaScript

Question

A grid table is displayed via PHP/MySQL that has a column for a checkbox that the user will check. The name is "checkMr[]", shown here:

 echo "<tr><td>
 <input type=\"checkbox\" id=\"{$Row[CONTAINER_NUMBER]}\" 
 data-info=\"{$Row[BOL_NUMBER]}\" data-to=\"{$Row[TO_NUMBER]}\" 
 name=\"checkMr[]\" />
 </td>";

As you will notice, there is are attributes for id, data-info, and data-to that are sent to a modal window. Here is the JavaScript that sends the attributes to the modal window:

 <script type="text/javascript">
   $(function()
   {
     $('a').click(function()
     {
       var selectedID = [];
       var selectedBL = [];
       var selectedTO = [];
       $(':checkbox[name="checkMr[]"]:checked').each(function()
       {
         selectedID.push($(this).attr('id'))
         selectedBL.push($(this).attr('data-info'))
         selectedTO.push($(this).attr('data-to'))
       });
       $(".modal-body .containerNumber").val( selectedID );
       $(".modal-body .bolNumber").val( selectedBL );
       $(".modal-body .toNumber").val( selectedTO );
     });
   });
 </script>

So far so good. The modal retrieves the attributes via javascript. I can choose to display them or not. Here is how the modal retrieves the attributes:

 <div id="myModal">
   <div class="modal-body">
   <form action="" method="POST" name="modalForm">
     <input type="hidden" name="containerNumber" class="containerNumber" id="containerNumber" />
     <input type="hidden" name="bolNumber" class="bolNumber" id="bolNumber" />
     <input type="hidden" name="toNumber" class="toNumber" id="toNumber" />
   </form>
   </div>
 </div>

There are additional fields within the form that the user will enter data, I just chose not to display the code. But so far, everything works. There is a submit button that then sends the form data to PHP variables. There is a mysql INSERT statement that then updates the necessary table.

Here is the PHP code (within the modal window):

 <?php
 $bol = $_POST['bolNumber'];    
 $container = $_POST['containerNumber']; 
 $to = $_POST['toNumber'];  

 if(isset($_POST['submit'])){
 $bol = mysql_real_escape_string(stripslashes($bol));
 $container = mysql_real_escape_string(stripslashes($container));
 $to = mysql_real_escape_string(stripslashes($to));

 $sql_query_string = 
   "INSERT INTO myTable (bol, container_num, to_num)
   VALUES ('$bol', '$container', '$to')
 }
    if(mysql_query($sql_query_string)){
      echo ("<script language='javascript'>
             window.alert('Saved')
             </script>");
    }
    else{
      echo ("<script language='javascript'>
             window.alert('Not Saved')
             </script>");
   }
 ?>

All of this works. The user checks a checkbox, the modal window opens, the user fills out additional form fields, hits save, and as long as there are no issues, the appropriate window will pop and say "Saved."

Here is the issue: when the user checks MULTIPLE checkboxes, the modal does indeed retrieve multiple container numbers and I can display it. They seem to be already separated by a comma.

The problem comes when the PHP variables are holding multiple container numbers (or bol numbers). The container numbers need to be separated, and I guess there has to be a way the PHP can automatically create multiple INSERT statements for each container number.

I know the variables need to be placed in an array somehow. And then there has to be a FOR loop that will read each container and separate them if there is a comma.

I just don't know how to do this.

check this stack overflow question for converting your comma delimited list of ids to an array http://stackoverflow.com/questions/5159086/php-split-string. since there are multiple checkboxes per row, you will have multiple arrays in php one for each checkbox type. these arrays will have the same length/size so you can index them all with the same indexer variable to generate your sql string. — Athens Holloway, Nov 12 '13 at 16:25

score 0 · Answer 1 · answered Nov 06 '13 at 16:22

0

When you send array values over HTTP as with [], they will already be arrays in PHP, so you can already iterate over them:

foreach ($_POST['bol'] as $bol) {
    "INSERT INTO bol VALUES ('$bol')";
}

Your queries are vulnerable to injection. You should be using properly parameterized queries with PDO/mysqli

answered Nov 06 '13 at 16:22

Explosion Pills

188,624
52
326
405

Do I use the foreach directly after $sql_query_string ? – HoodCoderMan Nov 06 '13 at 16:28
@JohnBeasley no, it should *surround* it – Explosion Pills Nov 06 '13 at 16:30
I'm sorry, but where do I put the foreach? I put it directly around the $sql_query_string, but that didn't work. Should it go above the if(isset($_POST['submit']))? – HoodCoderMan Nov 06 '13 at 16:35
@JohnBeasley what do you mean "didn't work?" That could mean a million things. Do you get an error? If so, what is it? Do the queries not work? Does only one query work? Is the data incorrect? Please be specific. – Explosion Pills Nov 06 '13 at 16:36
1

I put the foreach starting here: foreach($_POST['bol] as $bol){ $sql_query_string = INSERT ...... I put the closing bracket right before the closing php tag. – HoodCoderMan Nov 06 '13 at 16:37
The query no longer saves the to the table. – HoodCoderMan Nov 06 '13 at 16:40
@JohnBeasley I need to see your entire code. Perhaps you can show me on pastebin or something – Explosion Pills Nov 06 '13 at 16:47

Jared Gotte · Answer 2 · 2013-11-06T18:13:51.580

Assuming the *_NUMBER variables as keys directly below are integers, use:

echo '<tr><td><input type="checkbox" value="'.json_encode(array('CONTAINER_NUMBER' => $Row[CONTAINER_NUMBER], 'BOL_NUMBER' => $Row[BOL_NUMBER], 'TO_NUMBER' => $Row[TO_NUMBER])).'" name="checkMr[]" /></td>';

Then...

$('a#specifyAnchor').click(function() {
  var selectedCollection = [];
  $(':checkbox[name="checkMr[]"]:checked').each(function() {
    selectedCollection.push($(this).val());
  });
  $(".modal-body #checkboxCollections").val( selectedCollection );
});

Then...

<form action="" method="POST" name="modalForm">
  <input type="hidden" name="checkboxCollections" id="checkboxCollections" />

Then...

<?php
$cc = $_POST['checkboxCollections'];    

if (isset($_POST['submit'])) {
  foreach ($cc as $v) {
    $arr = json_decode($v);
    $query = sprintf("INSERT INTO myTable (bol, container_num, to_num) VALUES ('%s', '%s', '%s')", $arr['BOL_NUMBER'], $arr['CONTAINER_NUMBER'], $arr['TO_NUMBER']);
    // If query fails, do this...
    // Else...
  }
}
?>

Some caveats:

Notice the selector I used for your previous $('a').click() function. Do this so your form updates only when a specific link is clicked.
I removed your mysql_real_escape_string functions due to laziness. Make sure your data can be inserted into the table correctly.
Make sure you protect yourself against SQL injection vulnerabilities.
Be sure to test my code. You may have to change some things but understand the big picture here.

I'm not sure I can use this: value="'.json_encode($Row).'" - There are more than just 3 keys. Please advise. I am going to test the rest of the code you provided. — HoodCoderMan, Nov 06 '13 at 17:58
For your `$Row` at the beginning, if the `*_NUMBER` variables are integer keys, then that just screams "bad practice." However, I don't know your exact setup. Regardless, I adjusted my answer. — Jared Gotte, Nov 06 '13 at 18:15

Separating variables for SQL insert using PHP and JavaScript

2 Answers2