Receiving strange string as $_POST row info on edit.

ztaylor1990

Hello, I'm running into an issue where I'm trying to retrieve the existing value from a column and row in the database on a joined datatables editor, but the row ID issued back is not one I can seem to use to query the database to create a validator. Any suggestions on how to pass the true row value back to the PHP on edit? Much appreciated.

Code is as follows:

session_start();
include_once $_SERVER['DOCUMENT_ROOT'].'/dependencies/conn.php'; 
  $grabName = "SELECT full_name,carrier_name,customer_name,role,customer_display_name,bid_year FROM users  WHERE username ='" . $_SESSION['username'] . "'";
  $results= mysqli_query($conn, $grabName);
  while($row1 = mysqli_fetch_array($results)){
    $_SESSION['fullname'] = $row1['full_name'];
    $_SESSION['carrier_name'] = $row1['carrier_name'];
    $_SESSION['customer_name'] = $row1['customer_name'];
    $_SESSION['customer_display_name'] = $row1['customer_display_name'];
    $_SESSION['role'] = $row1['role'];
    $_SESSION['bid_year'] = $row1['bid_year'];
  }
$full_name =  $_SESSION['fullname'];
$carrier_name =  $_SESSION['carrier_name'];
$customer_name =  $_SESSION['customer_name'];
$customer_display_name =  $_SESSION['customer_display_name'];
$role =  $_SESSION['role'];

$bidyear =  $_SESSION['bid_year'];
$todayDateIso = date('Y-m-d H:i:s'); 
$currentYear = date('Y');
$linkid = $_POST['custom'];

include( "../../Editor-1.9.2/lib/DataTables.php" );


use
  DataTables\Editor,
  DataTables\Editor\Field,
  DataTables\Editor\Format,
  DataTables\Editor\Mjoin,
  DataTables\Editor\Options,
  DataTables\Editor\Upload,
  DataTables\Editor\Validate,
  DataTables\Editor\ValidateOptions;


// Selects the names from the RFP Type table
$sqlrfptype = "SELECT * FROM ".$customer_name."_".$bidyear."_rfp_type WHERE id = ".$linkid." ORDER BY id ASC LIMIT 1";
$resulttype = mysqli_query($conn, $sqlrfptype);   
while ($rowtype = mysqli_fetch_array($resulttype)){   
  $rfpname = $rowtype['name']; 
  $rfptype = $rowtype['type']; 
  $rfpcomment = $rowtype['comment'];
  $rfptablename = $rowtype['rfp_table_name']; 
  $rfpcarriertablename = $rowtype['carrier_table_name']; 
  $rfpheaderstablename = $rowtype['headers_table_name'];
  $rfpselectoptionsname = $rowtype['select_options_name']; 
} 


$sql2 = "SELECT * FROM ".$rfptablename."";
$result = mysqli_query($conn, $sql2);   
$columns = array_keys($result->fetch_assoc());             

$table = $rfptablename;
$carriertable = $rfpcarriertablename;
$sql2 = "SELECT * FROM ".$rfpcarriertablename."";
$result = mysqli_query($conn, $sql2);   
$carrier = array_keys($result->fetch_assoc());  
array_shift($carrier);
$editor = Editor::inst( $db, $carriertable ,array($carriertable.'.col1', $carriertable.'.carrier_name'));
//$editor = Editor::inst( $db, $table, 'col1' );
$editor->where( $carriertable.'.carrier_name', $carrier_name );

foreach ($columns as $i) {
  $editor->field(Field::inst( $table.".".$i ));
}
if (!empty($_POST['action'])){
  if ($_POST['action'] == 'edit'){
    $postDataArray = $_POST['data'];
    print_r($_POST);
    foreach($postDataArray as $key => $value) {
      $valueArray = $value; 
      foreach($valueArray as $key2 => $value2) {
        $columnArray = $value2;
        foreach ($columnArray as $key3 => $value3) {
          $dtRow = $key;
          $postRowNumber = str_replace("row_","",$dtRow);
          $postTable = $key2;
          $postColumn = $key3;
          $postResponseValue = $value3;          
          $select= "SELECT ".$postColumn." FROM ".$postTable." WHERE col1 = ? AND carrier_name = ?";
          $stmt = mysqli_stmt_init($conn);
          if (!mysqli_stmt_prepare($stmt, $select)) {
            // statement preparation failed
            echo "statement prep failed";
          } else {
            mysqli_stmt_bind_param($stmt, "is", $postRowNumber, $carrier_name);
            mysqli_stmt_execute($stmt);
            $result = mysqli_stmt_get_result($stmt);
            if(mysqli_num_rows($result) > 0){
              while ($row = mysqli_fetch_assoc($result)) {
                $previousValue = $row[$postColumn];
              }
            }
          }
        }
      }
    }
  }
}

foreach ($carrier as $i) {
  if(!empty($postColumn) && $i == $postColumn){
    $editor->field(
      Field::inst( $carriertable.".".$i )
      ->validator( function ( $val, $data, $field, $host ) {
        global $previousValue;

        return $val < $previousValue ?
            'Entered value: ('.$val.') must exceed minimum of '.$previousValue :
            true;
    } )
    );
  }
  else{
    $editor->field(Field::inst( $carriertable.".".$i ));
  }
}

$editor->leftJoin( $rfptablename, $rfptablename.'.col1', '=', $rfpcarriertablename.'.col1', 'AND', $rfpcarriertablename.'.carrier_name', '=', $carrier_name );

$editor->process( $_POST );
$editor->json();

The row ID that datatables seems to parse is listed like this:

array(2) { 
  ["data"]=> array(1) { 
    ["row_17d8b20aaGiltner Test"]=> array(1) { 
      ["demo_customer_2020_carrier_list_rd1_point_point"]=> array(1) { 
        ["col5"]=> string(2) "12" 
      } 
    }
  }
  ["action"]=> string(4) "edit"
}

Unfortunately, I use numeric row IDs combined with the $carriername for each user to figure out which table entry assigns to where, so "row_17d8b20aaGiltner Test" doesn't exactly tell me what SQL would be modified before it is modified by the editor call.

Is there a simpler way of figuring out how to pass a true row ID variable?

allan

Hi,

This is related to the fact that you are using a compound key:

array($carriertable.'.col1', $carriertable.'.carrier_name')

The client-side can only have a single value as an id (it is a DOM id as well as being used in Javascript to uniquely identify the row), so the server scripts will combine the different values using a hash.

The Editor class as a pkeyToArray method available which can be used to convert from the combined string into the individual values you'll need - e.g.:

$pkey = $editor->pkeyToArray( $id );

then you should have an array you can access such as $pkey[$carriertable.'.col1'].

Allan