Blank Value on PrepareSQL

Instead of :journal_id you need to use $journal_id

Thank you !!!!!! I didn't realise we could do that with this API.
Here is functioing code for anyone needing database API example with TRANSACTIONS and debugging (delete the echos once the code works)
`$record = $button->getCurrentRecord();
$journal_id = $record["id"];
$journal_type = "R" . $record["journal_type"];
$ID = $record["user_id"];
$md5 = substr(uniqid('', true) . uniqid('', true), 0, 32);

$rs = DB::Query("select year, period from current_period where user_id= ".$ID);
$data = $rs->fetchAssoc();
$current_year = $data['year'];
$current_period = $data['period'];

// Delete allocations

$rs3 = DB::Query("SELECT allocation_id FROM ledger WHERE journal_id = '".$journal_id."' AND user_id = ".$ID);
$data3 = array();
while ($row3=$rs3->fetchAssoc()) {
$data3[] = $row3;
}

foreach ($data3 as $row3) {
$allocation_id = $row3["allocation_id"];
$data3 = array();
$keyvalues3 = array();
$keyvalues3["user_id"] = $ID;
$keyvalues3["allocation_id"] = $allocation_id;
$data3["allocated"] = "";
$data3["allocation_id"] = "";
DB::Update("ledger", $data3, $keyvalues3);
}
// End Delete Allocations

// Reverse journal and use reversing code as journal_type

$set = DB::PrepareSQL("
SET @financial_year = $current_year;
SET @financial_period = '$current_period';
SET @journal_id1 = '$journal_id';
SET @user_id1 = $ID;
SET @journal_type = '$journal_type';
SET @allocated = '';
SET @vat_return = NULL;
SET @allocate = 0;
SET @allocation_id = '';
");

$sql = DB::PrepareSQL("INSERT INTO ledger (
date_document, type, section, nominal, value, vat, description, reference, company,
journal_id, user_id, financial_year, financial_period, journal_type, allocated, vat_return, allocate, allocation_id, reference2, reference3, attachment
)
SELECT
date_document, type, section, nominal, -1 * value, vat, description, reference, company,
@journal_id1, @user_id1, @financial_year, @financial_period, @journal_type, @allocated, @vat_return, @allocate, @allocation_id, reference2, reference3, attachment
FROM
ledger
WHERE
journal_id = '".$journal_id."' AND user_id = :user_id;
");

// End Reverse journal and use reversing code as journal_type

// Reverse customer invoices

$set2 = DB::PrepareSQL("SET @journal_id2 = '$journal_id';
SET @user_id2 = $ID;");

$sql2 = DB::PrepareSQL("INSERT INTO customer_invoices (
user_id, company, customer, invoice_number, quantity, item, net, vat,
each_value, item_code, invoice_date, transaction_date, journal_id, po_number
)
SELECT
@user_id2, company, customer, CONCAT('C', invoice_number), -1 quantity, item, -1 net, -1 * vat,
each_value, item_code, invoice_date, transaction_date, @journal_id2, po_number
FROM customer_invoices
WHERE
journal_id = '".$journal_id."' AND user_id = :user_id;
");
// End Reverse customer invoices
// Post Transaction
echo $set;
echo $sql;
echo "END OF 1 START 2";
echo $set2;
echo $sql2;

try{

DB::Exec ("START TRANSACTION;");
DB::Exec ($set);
DB::Exec ($sql);
DB::Exec ($set2);
DB::Exec ($sql2);
DB::Exec ("COMMIT;");

}catch (Exception $e) {

DB::Exec ("ROLLBACK;");
die("Transaction failed: " . $e->getMessage()); // or exit()
}

//End Post Transaction`

This is weird. The $sql and $sql2 print perfect MYSQL code that does exactly as expected in mysql, but the DB::Exec( $sql ); and DB::Exec( $sql2 ); lines do not run.
I've removed transactions for now, while I troubleshoot this. I don't understand what is needed with the DB::Exec code to make valid mysql code run.
`$record = $button->getCurrentRecord();
$journal_id = $record["id"];
$journal_type = "R" . $record["journal_type"];
$ID = $record["user_id"];
$md5 = substr(uniqid('', true) . uniqid('', true), 0, 32);

$rs = DB::Query("select year, period from current_period where user_id= ".$ID);
$data = $rs->fetchAssoc();
$current_year = $data['year'];
$current_period = $data['period'];

// Delete allocations

$rs3 = DB::Query("SELECT allocation_id FROM ledger WHERE journal_id = '".$journal_id."' AND user_id = ".$ID);
$data3 = array();
while ($row3=$rs3->fetchAssoc()) {
$data3[] = $row3;
}

foreach ($data3 as $row3) {
$allocation_id = $row3["allocation_id"];
$data3 = array();
$keyvalues3 = array();
$keyvalues3["user_id"] = $ID;
$keyvalues3["allocation_id"] = $allocation_id;
$data3["allocated"] = "";
$data3["allocation_id"] = "";
DB::Update("ledger", $data3, $keyvalues3);
}
// End Delete Allocations

// Reverse journal and use reversing code as journal_type

$sql = DB::PrepareSQL("
SET @financial_year = $current_year;
SET @financial_period = '$current_period';
SET @journal_id1 = '$journal_id';
SET @user_id1 = $ID;
SET @journal_type = '$journal_type';
SET @allocated = '';
SET @vat_return = NULL;
SET @allocate = 0;
SET @allocation_id = '';
INSERT INTO ledger (
date_document, type, section, nominal, value, vat, description, reference, company,
journal_id, user_id, financial_year, financial_period, journal_type, allocated, vat_return, allocate, allocation_id, reference2, reference3, attachment
)
SELECT
date_document, type, section, nominal, -1 * value, vat, description, reference, company,
@journal_id1, @user_id1, @financial_year, @financial_period, @journal_type, @allocated, @vat_return, @allocate, @allocation_id, reference2, reference3, attachment
FROM
ledger
WHERE
journal_id = '".$journal_id."' AND user_id = :user_id;
");

DB::Exec( $sql );
// Does not execute the $sql

// End Reverse journal and use reversing code as journal_type

// Reverse customer invoices

$sql2 = DB::PrepareSQL("
SET @journal_id2 = '$journal_id';
SET @user_id2 = $ID;
INSERT INTO customer_invoices (
user_id, company, customer, invoice_number, quantity, item, net, vat,
each_value, item_code, invoice_date, transaction_date, journal_id, po_number
)
SELECT
@user_id2, company, customer, CONCAT('C', invoice_number), -1 quantity, item, -1 net, -1 * vat,
each_value, item_code, invoice_date, transaction_date, @journal_id2, po_number
FROM customer_invoices
WHERE
journal_id = '".$journal_id."' AND user_id = :user_id;
");
DB::Exec( $sql2 );
// Does not execute the $sql2

// End Reverse customer invoices

// Test SQL parameters in MYSQL

echo $sql;
// Prints perfect code that works in mysql
echo "END OF 1 START 2";
echo $sql2;
// Prints perfect code that works in mysql

// End Test SQL parameters in MYSQL
`

FINAL CODE THAT WORKS - TRANSACTIONS
I've never worked with transactions so this was a learning experience. Things to consider:

• Where the transaction starts segregation of mysql commands One part I still dont understand is why some variables are '".$variable". and not :variable Delete the echos once tested - this was a godsend because it sped up knowing what issues to troubleshoot. because of the speed of INSERT into, I have 6 queries that need to optimised to use this method. This is about 10-30 times faster than SELECT and INSERT
  $record = $button->getCurrentRecord();
  $journal_id = $record["id"];
  $journal_type = "R" . $record["journal_type"];
  $ID = $record["user_id"];
  $md5 = substr(uniqid('', true) . uniqid('', true), 0, 32);
  $rs = DB::Query("select year, period from current_period where user_id= ".$ID);
  $data = $rs->fetchAssoc();
  $current_year = $data['year'];
  $current_period = $data['period'];
  // Delete allocations
  $rs3 = DB::Query("SELECT allocation_id FROM ledger WHERE journal_id = '".$journal_id."' AND user_id = ".$ID);
  $data3 = array();
  while ($row3=$rs3->fetchAssoc()) {
  $data3[] = $row3;
  }
  foreach ($data3 as $row3) {
  $allocation_id = $row3["allocation_id"];
  $data3 = array();
  $keyvalues3 = array();
  $keyvalues3["user_id"] = $ID;
  $keyvalues3["allocation_id"] = $allocation_id;
  $data3["allocated"] = "";
  $data3["allocation_id"] = "";
  DB::Update("ledger", $data3, $keyvalues3);
  }
  // End Delete Allocations
  // Reverse journal and use reversing code as journal_type
  $set1 = DB::PrepareSQL("SET @financial_year = $current_year;");
  $set2 = DB::PrepareSQL("SET @financial_period = '$current_period';");
  $set3 = DB::PrepareSQL("SET @journal_id1 = '$journal_id';");
  $set4 = DB::PrepareSQL("SET @user_id1 = $ID;");
  $set5 = DB::PrepareSQL("SET @journal_type = '$journal_type';");
  $set6 = DB::PrepareSQL("SET @allocated = '';");
  $set7 = DB::PrepareSQL("SET @vat_return = NULL;");
  $set8 = DB::PrepareSQL("SET @allocate = 0;");
  $set9 = DB::PrepareSQL("SET @allocation_id = '';");
  $sql = DB::PrepareSQL("INSERT INTO ledger (
  date_document, type, section, nominal, value, vat, description, reference, company,
  journal_id, user_id, financial_year, financial_period, journal_type, allocated, vat_return, allocate, allocation_id, reference2, reference3, attachment
  )
  SELECT
  date_document, type, section, nominal, -1 value, vat, description, reference, company,
  @journal_id1, @user_id1, @financial_year, @financial_period, @journal_type, @allocated, @vat_return, @allocate, @allocation_id, reference2, reference3, attachment
  FROM
  ledger
  WHERE
  journal_id = '".$journal_id."' AND user_id = :user_id;
  ");
  // End Reverse journal and use reversing code as journal_type
  // Reverse customer invoices
  $set21 = DB::PrepareSQL("SET @journal_id2 = '$journal_id';");
  $set22 = DB::PrepareSQL("SET @user_id2 = $ID;");
  $sql2 = DB::PrepareSQL("INSERT INTO customer_invoices (
  user_id, company, customer, invoice_number, quantity, item, net, vat,
  each_value, item_code, invoice_date, transaction_date, journal_id, po_number
  )
  SELECT
  @user_id2, company, customer, CONCAT('C', invoice_number), -1 <em> quantity, item, -1 </em> net, -1 vat,
  each_value, item_code, invoice_date, transaction_date, @journal_id2, po_number
  FROM customer_invoices
  WHERE
  journal_id = '".$journal_id."' AND user_id = :user_id;
  ");
  // End Reverse customer invoices
  // Test SQL parameters in MYSQL
  echo $set1;
  echo $set2;
  echo $set3;
  echo $set4;
  echo $set5;
  echo $set6;
  echo $set7;
  echo $set8;
  echo $set9;
  echo $sql;
  echo $set21;
  echo $set22;
  echo $sql2;
  // End Test SQL parameters in MYSQL
  // Run Transaction
  DB::Exec ("START TRANSACTION;");
  try{
  DB::Exec( $set1 );
  DB::Exec( $set2 );
  DB::Exec( $set3 );
  DB::Exec( $set4 );
  DB::Exec( $set5 );
  DB::Exec( $set6 );
  DB::Exec( $set7 );
  DB::Exec( $set8 );
  DB::Exec( $set9 );
  DB::Exec( $sql );
  DB::Exec( $set21 );
  DB::Exec( $set22 );
  DB::Exec( $sql2 );
  DB::Exec ("COMMIT;");
  }catch (Exception $e) {
  DB::Exec ("ROLLBACK;");
  die("Transaction failed: " . $e->getMessage()); // or exit()
  }
  // End Run Transaction

Reposted code for easy reading
`$record = $button->getCurrentRecord();
$journal_id = $record["id"];
$journal_type = "R" . $record["journal_type"];
$ID = $record["user_id"];
$md5 = substr(uniqid('', true) . uniqid('', true), 0, 32);

$rs = DB::Query("select year, period from current_period where user_id= ".$ID);
$data = $rs->fetchAssoc();
$current_year = $data['year'];
$current_period = $data['period'];

// Delete allocations

$rs3 = DB::Query("SELECT allocation_id FROM ledger WHERE journal_id = '".$journal_id."' AND user_id = ".$ID);
$data3 = array();
while ($row3=$rs3->fetchAssoc()) {
$data3[] = $row3;
}

foreach ($data3 as $row3) {
$allocation_id = $row3["allocation_id"];
$data3 = array();
$keyvalues3 = array();
$keyvalues3["user_id"] = $ID;
$keyvalues3["allocation_id"] = $allocation_id;
$data3["allocated"] = "";
$data3["allocation_id"] = "";
DB::Update("ledger", $data3, $keyvalues3);
}
// End Delete Allocations

// Reverse journal and use reversing code as journal_type

$set1 = DB::PrepareSQL("SET @financial_year = $current_year;");
$set2 = DB::PrepareSQL("SET @financial_period = '$current_period';");
$set3 = DB::PrepareSQL("SET @journal_id1 = '$journal_id';");
$set4 = DB::PrepareSQL("SET @user_id1 = $ID;");
$set5 = DB::PrepareSQL("SET @journal_type = '$journal_type';");
$set6 = DB::PrepareSQL("SET @allocated = '';");
$set7 = DB::PrepareSQL("SET @vat_return = NULL;");
$set8 = DB::PrepareSQL("SET @allocate = 0;");
$set9 = DB::PrepareSQL("SET @allocation_id = '';");

$sql = DB::PrepareSQL("INSERT INTO ledger (
date_document, type, section, nominal, value, vat, description, reference, company,
journal_id, user_id, financial_year, financial_period, journal_type, allocated, vat_return, allocate, allocation_id, reference2, reference3, attachment
)
SELECT
date_document, type, section, nominal, -1 * value, vat, description, reference, company,
@journal_id1, @user_id1, @financial_year, @financial_period, @journal_type, @allocated, @vat_return, @allocate, @allocation_id, reference2, reference3, attachment
FROM
ledger
WHERE
journal_id = '".$journal_id."' AND user_id = :user_id;
");

// End Reverse journal and use reversing code as journal_type

// Reverse customer invoices

$set21 = DB::PrepareSQL("SET @journal_id2 = '$journal_id';");
$set22 = DB::PrepareSQL("SET @user_id2 = $ID;");

$sql2 = DB::PrepareSQL("INSERT INTO customer_invoices (
user_id, company, customer, invoice_number, quantity, item, net, vat,
each_value, item_code, invoice_date, transaction_date, journal_id, po_number
)
SELECT
@user_id2, company, customer, CONCAT('C', invoice_number), -1 quantity, item, -1 net, -1 * vat,
each_value, item_code, invoice_date, transaction_date, @journal_id2, po_number
FROM customer_invoices
WHERE
journal_id = '".$journal_id."' AND user_id = :user_id;
");

// End Reverse customer invoices

// Test SQL parameters in MYSQL

echo $set1;
echo $set2;
echo $set3;
echo $set4;
echo $set5;
echo $set6;
echo $set7;
echo $set8;
echo $set9;
echo $sql;
echo $set21;
echo $set22;
echo $sql2;
// End Test SQL parameters in MYSQL

// Run Transaction

DB::Exec ("START TRANSACTION;");

try{

DB::Exec( $set1 );
DB::Exec( $set2 );
DB::Exec( $set3 );
DB::Exec( $set4 );
DB::Exec( $set5 );
DB::Exec( $set6 );
DB::Exec( $set7 );
DB::Exec( $set8 );
DB::Exec( $set9 );
DB::Exec( $sql );

DB::Exec( $set21 );
DB::Exec( $set22 );
DB::Exec( $sql2 );

DB::Exec ("COMMIT;");

}catch (Exception $e) {

DB::Exec ("ROLLBACK;");
die("Transaction failed: " . $e->getMessage()); // or exit()
}
// End Run Transaction`