Reserved word issue

PHP
1

Reserved word inside mysql query exact in array index # 2 the word is each how to fix that

<?php
//gets the data from WO1_WorkOrderMaster table from sage
function get_data_from_sage_table_workordermaster(){
    $conn=odbc_connect("NEWSAGE", "user", "pass");
    if (!$conn) {
        exit("Connection Failed: " . $conn);
    }
    $sql="select * from WO1_WorkOrderMaster";

    $result=odbc_exec($conn,$sql)or die(exit("Error en odbc_exec"));

    $data = array();
    while (odbc_fetch_row($result)) {
        $data[]=array('WorkOrder'=> odbc_result ($result, "WorkOrder"),
                      'ItemBillDescription'=> odbc_result ($result, "ItemBillDescription"),
                      'ItemUM'=> odbc_result ($result, "ItemUM"),
                      'BillRevision'=> odbc_result ($result, "BillRevision"),
                      'BillOptions'=> odbc_result ($result, "BillOptions"),
                      'EffectiveDate'=> odbc_result ($result, "EffectiveDate"),
                      'RoutingNumber'=> odbc_result ($result, "RoutingNumber"),
                      'IssueMethod'=> odbc_result ($result, "IssueMethod"),
                      'PrntPickList'=> odbc_result ($result, "PrntPickList"),
                      'UserDefinedField1'=> odbc_result ($result, "UserDefinedField1"),
                      'UserDefinedField2'=> odbc_result ($result, "UserDefinedField2"),
                      'UserDefinedField3'=> odbc_result ($result, "UserDefinedField3"),
                      'UserDefinedField4'=> odbc_result ($result, "UserDefinedField4"),
                      'PlannerCode'=> odbc_result ($result, "PlannerCode"),
                      'CopyFromTemplate'=> odbc_result ($result, "CopyFromTemplate"),
                      'PrntLabels'=> odbc_result ($result, "PrntLabels"),
                      'ExplodeSubassemblies'=> odbc_result ($result, "ExplodeSubassemblies"),
                      'CostCmplMthd'=> odbc_result ($result, "CostCmplMthd"),
                      'AutoIssueMatlTran'=> odbc_result ($result, "AutoIssueMatlTran"),
                      'AutoGenLabTran'=> odbc_result ($result, "AutoGenLabTran"),
                      'AutoGenOutsProcTran'=> odbc_result ($result, "AutoGenOutsProcTran"),
                      'DateClosed'=> odbc_result ($result, "DateClosed"),
                      'MakeFor'=> odbc_result ($result, "MakeFor"),
                      'MakeForOrderNumber'=> odbc_result ($result, "MakeForOrderNumber"),
                      'SOLineIndex'=> odbc_result ($result, "SOLineIndex"),
                      'ParentWhse'=> odbc_result ($result, "ParentWhse"),
                      'DefaultComponentWhse'=> odbc_result ($result, "DefaultComponentWhse"),
                      'Status'=> odbc_result ($result, "Status"),
                      'WODate'=> odbc_result ($result, "WODate"),
                      'WODueDate'=> odbc_result ($result, "WODueDate"),
                      'SchedRelDate'=> odbc_result ($result, "SchedRelDate"),
                      'ActualProdStartDate'=> odbc_result ($result, "ActualProdStartDate"),
                      'LastActivityDate'=> odbc_result ($result, "LastActivityDate"),
                      'LastReschedDate'=> odbc_result ($result, "LastReschedDate"),
                      'PriorDueDate'=> odbc_result ($result, "PriorDueDate"),
                      'UserWhoLastResched'=> odbc_result ($result, "UserWhoLastResched"),
                      'OnHold'=> odbc_result ($result, "OnHold"),
                      'StatusComment'=> odbc_result ($result, "StatusComment"),
                      'TravelerPrinted'=> odbc_result ($result, "TravelerPrinted"),
                      'LastStepComplete'=> odbc_result ($result, "LastStepComplete"),
                      'SubstepPrefix'=> odbc_result ($result, "SubstepPrefix"),
                      'SubstepSuffix'=> odbc_result ($result, "SubstepSuffix"),
                      'CustomerDivision'=> odbc_result ($result, "CustomerDivision"),
                      'SOCustNumber'=> odbc_result ($result, "SOCustNumber"),
                      'ItemBillNumber'=> odbc_result ($result, "ItemBillNumber"),
                      'FreezeIt'=> odbc_result ($result, "FreezeIt"),
                      'MatIssueMethod'=> odbc_result ($result, "MatIssueMethod"),
                      'LabIssueMethod'=> odbc_result ($result, "LabIssueMethod"),
                      'ScheduleRevised'=> odbc_result ($result, "ScheduleRevised"),
                      'LastStatus'=> odbc_result ($result, "LastStatus"),
                      'TieredItemsCostCalculationMeth'=> odbc_result ($result, "TieredItemsCostCalculationMeth"),
                      'LinkToFirstDetailRecord'=> odbc_result ($result, "LinkToFirstDetailRecord"),
                      'UMConversion'=> odbc_result ($result, "UMConversion"),
                      'QtyOrdered'=> odbc_result ($result, "QtyOrdered"),
                      'YieldPercentage'=> odbc_result ($result, "YieldPercentage"),
                      'QtyPlanned'=> odbc_result ($result, "QtyPlanned"),
                      'QtyComplete'=> odbc_result ($result, "QtyComplete"),
                      'QtyTransferred'=> odbc_result ($result, "QtyTransferred"),
                      'QtyScrapped'=> odbc_result ($result, "QtyScrapped"),
                      'CalcLeadTime'=> odbc_result ($result, "CalcLeadTime"),
                      'QtyCompletedToday'=> odbc_result ($result, "QtyCompletedToday"),
                      'StdLaborCost'=> odbc_result ($result, "StdLaborCost"),
                      'ActualLaborCost'=> odbc_result ($result, "ActualLaborCost"),
                      'StdMaterialCost'=> odbc_result ($result, "StdMaterialCost"),
                      'ActualMaterialCost'=> odbc_result ($result, "ActualMaterialCost"),
                      'StdOutsideProcessCost'=> odbc_result ($result, "StdOutsideProcessCost"),
                      'ActualOutsideProcessCost'=> odbc_result ($result, "ActualOutsideProcessCost"),
                      'StdFixedOverheadCost'=> odbc_result ($result, "StdFixedOverheadCost"),
                      'ActualFixedOverheadCost'=> odbc_result ($result, "ActualFixedOverheadCost"),
                      'StdVariableOverheadCost'=> odbc_result ($result, "StdVariableOverheadCost"),
                      'ActualVariableOvhdCost'=> odbc_result ($result, "ActualVariableOvhdCost"),
                      'TieredBudgetedCost'=> odbc_result ($result, "TieredBudgetedCost"),
                      'TieredStdCost'=> odbc_result ($result, "TieredStdCost")
                      );
    }
    return $data;

}


function insert_sage_data_to_workordermaster($sage_data=array()){
  try
    {
    $pdo = new PDO("mysql:host=192.168.0.115; dbname=test", "user", "pass");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    }
    catch (PDOException $e)
    {
      $output = 'Unable to connect to the database server.' .
      $e->getMessage() . ' in ' .
      $e->getFile() . ': ' . $e->getLine();
    }

    foreach($sage_data as $data){
        $workordermaster = sprintf("INSERT INTO workordermaster
          (
          `WorkOrder`,
          `ItemBillDescription`,
          `ItemUM`,     <---- **this = to each **
          `BillRevision`,
          `BillOptions`,
          `EffectiveDate`,
          `RoutingNumber`,
          `IssueMethod`,
          `PrntPickList`,
          `UserDefinedField1`,
          `UserDefinedField2`,
          `UserDefinedField3`,
          `UserDefinedField4`,
          `PlannerCode`,
          `CopyFromTemplate`,
          `PrntLabels`,
          `ExplodeSubassemblies`,
          `CostCmplMthd`,
          `AutoIssueMatlTran`,
          `AutoGenLabTran`,
          `AutoGenOutsProcTran`,
          `DateClosed`,
          `MakeFor`,
          `MakeForOrderNumber`,
          `SOLineIndex`,
          `ParentWhse`,
          `DefaultComponentWhse`,
          `Status`,
          `WODate`,
          `WODueDate`,
          `SchedRelDate`,
          `ActualProdStartDate`,
          `LastActivityDate`,
          `LastReschedDate`,
          `PriorDueDate`,
          `UserWhoLastResched`,
          `OnHold`,
          `StatusComment`,
          `TravelerPrinted`,
          `LastStepComplete`,
          `SubstepPrefix`,
          `SubstepSuffix`,
          `CustomerDivision`,
          `SOCustNumber`,
          `ItemBillNumber`,
          `FreezeIt`,
          `MatIssueMethod`,
          `LabIssueMethod`,
          `ScheduleRevised`,
          `LastStatus`,
          `TieredItemsCostCalculationMeth`,
          `LinkToFirstDetailRecord`,
          `UMConversion`,
          `QtyOrdered`,
          `YieldPercentage`,
          `QtyPlanned`,
          `QtyComplete`,
          `QtyTransferred`,
          `QtyScrapped`,
          `CalcLeadTime`,
          `QtyCompletedToday`,
          `StdLaborCost`,
          `ActualLaborCost`,
          `StdMaterialCost`,
          `ActualMaterialCost`,
          `StdOutsideProcessCost`,
          `ActualOutsideProcessCost`,
          `StdFixedOverheadCost`,
          `ActualFixedOverheadCost`,
          `StdVariableOverheadCost`,
          `ActualVariableOvhdCost`,
          `TieredBudgetedCost`,
          `TieredStdCost`)
        VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                '%s','%s','%s')",
        $data['WorkOrder'],
        $data['ItemBillDescription'],
        $data['ItemUM'],
        $data['BillRevision'],
        $data['BillOptions'],
        $data['EffectiveDate'],
        $data['RoutingNumber'],
        $data['IssueMethod'],
        $data['PrntPickList'],
        $data['UserDefinedField1'],
        $data['UserDefinedField2'],
        $data['UserDefinedField3'],
        $data['UserDefinedField4'],
        $data['PlannerCode'],
        $data['CopyFromTemplate'],
        $data['PrntLabels'],
        $data['ExplodeSubassemblies'],
        $data['CostCmplMthd'],
        $data['AutoIssueMatlTran'],
        $data['AutoGenLabTran'],
        $data['AutoGenOutsProcTran'],
        $data['DateClosed'],
        $data['MakeFor'],
        $data['MakeForOrderNumber'],
        $data['SOLineIndex'],
        $data['ParentWhse'],
        $data['DefaultComponentWhse'],
        $data['Status'],
        $data['WODate'],
        $data['WODueDate'],
        $data['SchedRelDate'],
        $data['ActualProdStartDate'],
        $data['LastActivityDate'],
        $data['LastReschedDate'],
        $data['PriorDueDate'],
        $data['UserWhoLastResched'],
        $data['OnHold'],
        $data['StatusComment'],
        $data['TravelerPrinted'],
        $data['LastStepComplete'],
        $data['SubstepPrefix'],
        $data['SubstepSuffix'],
        $data['CustomerDivision'],
        $data['SOCustNumber'],
        $data['ItemBillNumber'],
        $data['FreezeIt'],
        $data['MatIssueMethod'],
        $data['LabIssueMethod'],
        $data['ScheduleRevised'],
        $data['LastStatus'],
        $data['TieredItemsCostCalculationMeth'],
        $data['LinkToFirstDetailRecord'],
        $data['UMConversion'],
        $data['QtyOrdered'],
        $data['YieldPercentage'],
        $data['QtyPlanned'],
        $data['QtyComplete'],
        $data['QtyTransferred'],
        $data['QtyScrapped'],
        $data['CalcLeadTime'],
        $data['QtyCompletedToday'],
        $data['StdLaborCost'],
        $data['ActualLaborCost'],
        $data['StdMaterialCost'],
        $data['ActualMaterialCost'],
        $data['StdOutsideProcessCost'],
        $data['ActualOutsideProcessCost'],
        $data['StdFixedOverheadCost'],
        $data['ActualFixedOverheadCost'],
        $data['StdVariableOverheadCost'],
        $data['ActualVariableOvhdCost'],
        $data['TieredBudgetedCost'],
        $data['TieredStdCost']);
  $rs_workordermaster = $pdo->query($workordermaster);

    }

}

$drop = Drop_workordermaster();
$sage_data_from_workordermaster = get_data_from_sage_table_workordermaster();
insert_sage_data_to_workordermaster($sage_data_from_workordermaster);
?>
2

Hi @eliasskate_8,

Unfortunately with the information given is a little hard to know where the problem is at.
Are you able to provide the raw SQL for this query?
If it is complaining about a reserved word maybe try wrapping it in back ticks e.g.

`each`

All the best,

Andres

3

Yes, some clarification on exactly what the problem is would help - I don’t see “each” in that query at all, on the line you have highlighted. I suspect you’re saying that in the array of data to be inserted, in the second row there is a reserved word. But as you surround all the column names with back-ticks and the values with quotes, I’m not sure why that matters.

Incidentally, for the use you have there, I would have thought that this whole construction

foreach($sage_data as $data){
        $workordermaster = sprintf("INSERT INTO workordermaster
  ...
  }

would be a prime example of where a prepared statement would be a useful thing to use. You’re looping around an array of values to insert, but the query is always the same. Using sprintf to build a query is all well and good if you’re doing things that a prepared statement cannot, but in this case the list of columns and values is consistent.

It might also get around your reserved word issue, depending on what that works out to be. Do any of your values include a single-quote character? You can see how that would throw out the query entirely using this method, but I would have thought a different error message.

4

hey the reserved word is inside the array in the array[2] is in the values I specified that one but i figure out already just by adding a backslash in ‘'%s’’ to escape the " ’ "

5

Andres_Vaquero ‘each’ is the value for column ItemUM every time that i run the code just coy one row any idea how to fix this one

6

I try prepare however, won’t work i know I can not use those words but in the value i am really confuse
the tokens ‘%s’ print with single-quote or not. i try to use the \ to escape and add a quote but still won’t work

7

Show how you tried it - it should work and it does remove issues with quotes.

8 
$fields = '`' .implode('`,`', $fields) . '`';
$valuess = '"' .implode('","', $valuess) . '"';
$sql = "INSERT INTO workordermaster ($fields) values($valuess)";
$workordermaster = $pdo->prepare($sql);
$result = $workordermaster->execute();


the problem is that a field in the database has " representing Inch and ’ representing foots now is there a way to avoid the quotes and single quotes inside of the values fields

here better example where the inch are representing by double quotes ( " )

9

Yes. Use named placeholders instead of INSERTing the variable directly into the query.

10

hey that works Mittineague thanks a lot you are the best dude

1 Like
11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.