While trying to load a large array into a database I keep getting the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's Completed, Mark, .5hr', '', '>3/25/08, Climate control computer shows ' at line 138

I did some research into the field that the code was going through that produces the error and came arross this text:

HVAC - Too Hot/Too Cold:>3/25/08, Climate control computer shows warm temps in building. Adjusted OA dampers on RTU's
Completed, Mark, .5hr

So, if I am correct in what the sql error is saying, this is an issue with the single quote in the phrase "RTU's"
If I'm wrong please point me in the right direction. 🙂

Any help would be greatly appreciated as I'm at a loss.

    Heres my code in a php bracket. Wouldn't all fit in one post 🙂 or maybe I'm just dumb. . .

    . . .
    for ($z = 0; $z <= $number; $z++)
    {
    	$X = time();
    	for ($y = 0; $y <= 95; $y++)
    	set_time_limit(30);	
    	//didn't work
    	$WO[$z][$y] = filter_var($WO[$z][$y], FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_LOW);
    	//didn't work
    	$WO[$z][$y] = mysql_real_escape_string($WO[$z][$y]);
    	//didn't work
    	$WO[$z][$y] = addcslashes($WO[$z][$y], '\'');
    	$WO[$z][$y] = addcslashes($WO[$z][$y], '\"');
    	//didn't work
    	$WO[$z][$y] = trim($WO[$z][$y],"\x22\x27");
    	//didn't work
    	if (get_magic_quotes_gpc())
    		$WO[$z][$y] = stripslashes($WO[$z][$y]);
    	else if (!get_magic_quotes_gpc())
    		$WO[$z][$y] = addslashes($WO[$z][$y]);
    	$X2 = time();
    	$X3 = $X3 + ($X2 - $X1);
    }
    echo "Finished filtering array. . .<br /><br /><br />";
    echo "Time taken: " . date('i:s', $X3) . "<br /><br /><br />";
    for ($x = 0; $x <= $number; $x++)
    {
    	$t1 = time();	
    	mysql_query("INSERT INTO WorkOrder (WorkOrderNumber, 
    									CustomerOrderNumber, 
    									ReleaseContractNumber, 
    									AssignedVendor, 
    									AssignedVendorSite, 
    									AssignedEmployee, 
    									VendorPrimaryAssign, 
    									AssignedName, 
    									Priority, 
    									Status, 
    									OrderType, 
    									OrderTypeAlt, 
    									OrderCategory, 
    									PhoneFix, 
    									PhoneFixAssn, 
    									RequestedBy, 
    									RequestedFrom, 
    									ConfirmedDate, 
    									ConfirmedBy, 
    									DateEntered, 
    									DateDispatchIsAllowed, 
    									DateDispatched, 
    									DateFaxed, 
    									DateWorkIsAllowed, 
    									DateNextArrival, 
    									DateNextArrivalSite, 
    									DateTargetDispatch, 
    									DateTargetResponse, 
    									DateTargetResponseSite, 
    									DateTargetClose, 
    									DateEstimatedComplete, 
    									DateComplete, 
    									DateClosed, 
    									Timezone, 
    									Arrival, 
    									Departure, 
    									OvertimeAllowed, 
    									TravelAllowed, 
    									ApprovalCode, 
    									ApprovedBy, 
    									Dispatcher, 
    									ProblemDescription, 
    									ProblemFound, 
    									CompletionDescription, 
    									GradeHD, 
    									GradeCust, 
    									BidAmount, 
    									BidNotExceed, 
    									BidAuthCode, 
    									ActualCost, 
    									BilledAmount, 
    									LaborHours, 
    									DatePosted, 
    									PeriodPosted, 
    									Reference, 
    									Reference2, 
    									ParentWorkOrder, 
    									ParentRelationship, 
    									PMaintCycleSeq, 
    									CallLogNumber, 
    									CallerSeq, 
    									LocationPhone, 
    									LocationDetails, 
    									EnteredBy, 
    									CompletedBy, 
    									RecallCounter, 
    									TripCounter, 
    									InternalDivision, 
    									InternalCostCenter, 
    									InternalZone, 
    									BatchNumber, 
    									UnbatchedEmp, 
    									EmailAddress, 
    									EmailStatus, 
    									AutoUpdateStatus, 
    									TechName, 
    									TechMinutes, 
    									TechHoldMinutes, 
    									TechRequired, 
    									TechReasonNotReq, 
    									TechComplete, 
    									TechNotes_LD, 
    									DateModified, 
    									DateEnteredSite, 
    									DateTargetCloseSite, 
    									DateCompleteSite, 
    									ArrivalSite, 
    									DepartureSite, 
    									SpclHndCode, 
    									FSRTechName, 
    									DateDispatchedSite, 
    									DateFaxedSite, 
    									SiteContactName, 
    									SiteContactPhone, 
    									Source, 
    									Escalation) 
    	VALUES ('" . $WO[$x][0] . "',
    			'" . $WO[$x][1] . "',
    			'$WO[$x][2]',
    			'$WO[$x][3]',
    			'$WO[$x][4]',
    			'$WO[$x][5]',
    			'$WO[$x][6]',
    			'" . $WO[$x][7] . "',
    			'" . $WO[$x][8] . "',
    			'" . $WO[$x][9] . "',
    			'" . $WO[$x][10] . "',
    			'" . $WO[$x][11] . "',
    			'" . $WO[$x][12] . "',
    			'$WO[$x][13]',
    			'$WO[$x][14]',
    			'" . $WO[$x][15] . "',
    			'" . $WO[$x][16] . "',
    			'$WO[$x][17]',
    			'" . $WO[$x][18] . "',
    			'$WO[$x][19]',
    			'$WO[$x][20]',
    			'$WO[$x][21]',
    			'$WO[$x][22]',
    			'$WO[$x][23]',
    			'$WO[$x][24]',
    			'$WO[$x][25]',
    			'$WO[$x][26]',
    			'$WO[$x][27]',
    			'$WO[$x][28]',
    			'$WO[$x][29]',
    			'$WO[$x][30]',
    			'$WO[$x][31]',
    			'$WO[$x][32]',
    			'" . $WO[$x][33] . "',
    			'$WO[$x][34]',
    			'$WO[$x][35]',
    			'$WO[$x][36]',
    			'" . $WO[$x][37] . "',
    			'$WO[$x][38]',
    			'$WO[$x][39]',
    			'$WO[$x][40]',
    			'" . $WO[$x][41] . "',
    			'" . $WO[$x][42] . "',
    			'" . $WO[$x][43] . "',
    			'$WO[$x][44]',
    			'$WO[$x][45]',
    			'$WO[$x][46]',
    			'$WO[$x][47]',
    			'" . $WO[$x][48] . "',
    			'$WO[$x][49]',
    			'$WO[$x][50]',
    			'$WO[$x][51]',
    			'$WO[$x][52]',
    			'$WO[$x][53]',
    			'" . $WO[$x][54] . "',
    			'" . $WO[$x][55] . "',
    			'" . $WO[$x][56] . "',
    			'" . $WO[$x][57] . "',
    			'$WO[$x][58]',
    			'$WO[$x][59]',
    			'$WO[$x][60]',
    			'" . $WO[$x][61] . "',
    			'" . $WO[$x][62] . "',
    			'" . $WO[$x][63] . "',
    			'" . $WO[$x][64] . "',
    			'$WO[$x][65]',
    			'$WO[$x][66]',
    			'$WO[$x][67]',
    			'$WO[$x][68]',
    			'" . $WO[$x][69] . "',
    			'" . $WO[$x][70] . "',
    			'$WO[$x][71]',
    			'" . $WO[$x][72] . "',
    			'" . $WO[$x][73] . "',
    			'$WO[$x][74]',
    			'" . $WO[$x][75] . "',
    			'$WO[$x][76]',
    			'$WO[$x][77]',
    			'$WO[$x][78]',
    			'" . $WO[$x][79] . "',
    			'$WO[$x][80]',
    			'$WO[$x][81]',
    			'$WO[$x][82]',
    			'$WO[$x][83]',
    			'$WO[$x][84]',
    			'$WO[$x][85]',
    			'$WO[$x][86]',
    			'$WO[$x][87]',
    			'" . $WO[$x][88] . "',
    			'" . $WO[$x][89] . "',
    			'$WO[$x][90]',
    			'$WO[$x][91]',
    			'" . $WO[$x][92] . "',
    			'" . $WO[$x][93] . "',
    			'" . $WO[$x][94] . "',
    			'" . $WO[$x][95] . "')") OR die(mysql_error());
    	$t2 = time();
    	$t3 = $t3 + ($t2 - $t1);
    }
    echo "Database populated. . .<br /><br /><br />";
    echo "Time taken: " . date('i:s', $t3);
    . . .

      [man]mysql_real_escape_string[/man]. Yes, I know it's there among all the other attempts and back-and-forth escaping/unescaping and so on, but it is the correct route. Did you escape all the fields? And did you escape them only to unescape them again?
      Or use [man]PDO[/man].

        Sorry, I guess I forgot to mention that I did not use all of those escape functions at the same time, as I used one that did not work, I commented it out and tried another. I just left them uncommented so they would stand out more I guess. As for PDO, I have no idea what that is and I'm still trying to learn objects.

          You could try it this way to escape everything and for less typing. It seems you already know the number of values that are being posted, therefore just put the values into a loop and escape them from there.

          for ($z = 0; $z <= $number; $z++)
          {
              $X = time();
              for ($y = 0; $y <= 95; $y++)
              set_time_limit(30);    
          //didn't work $WO[$z][$y] = filter_var($WO[$z][$y], FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_LOW); //didn't work $WO[$z][$y] = mysql_real_escape_string($WO[$z][$y]); //didn't work $WO[$z][$y] = addcslashes($WO[$z][$y], '\''); $WO[$z][$y] = addcslashes($WO[$z][$y], '\"'); //didn't work $WO[$z][$y] = trim($WO[$z][$y],"\x22\x27"); //didn't work if (get_magic_quotes_gpc()) $WO[$z][$y] = stripslashes($WO[$z][$y]); else if (!get_magic_quotes_gpc()) $WO[$z][$y] = addslashes($WO[$z][$y]); $X2 = time(); $X3 = $X3 + ($X2 - $X1); } echo "Finished filtering array. . .<br /><br /><br />"; echo "Time taken: " . date('i:s', $X3) . "<br /><br /><br />"; for ($x = 0; $x <= $number; $x++) { $t1 = time(); $query = "INSERT INTO WorkOrder (WorkOrderNumber, CustomerOrderNumber, ReleaseContractNumber, AssignedVendor, AssignedVendorSite, AssignedEmployee, VendorPrimaryAssign, AssignedName, Priority, Status, OrderType, OrderTypeAlt, OrderCategory, PhoneFix, PhoneFixAssn, RequestedBy, RequestedFrom, ConfirmedDate, ConfirmedBy, DateEntered, DateDispatchIsAllowed, DateDispatched, DateFaxed, DateWorkIsAllowed, DateNextArrival, DateNextArrivalSite, DateTargetDispatch, DateTargetResponse, DateTargetResponseSite, DateTargetClose, DateEstimatedComplete, DateComplete, DateClosed, Timezone, Arrival, Departure, OvertimeAllowed, TravelAllowed, ApprovalCode, ApprovedBy, Dispatcher, ProblemDescription, ProblemFound, CompletionDescription, GradeHD, GradeCust, BidAmount, BidNotExceed, BidAuthCode, ActualCost, BilledAmount, LaborHours, DatePosted, PeriodPosted, Reference, Reference2, ParentWorkOrder, ParentRelationship, PMaintCycleSeq, CallLogNumber, CallerSeq, LocationPhone, LocationDetails, EnteredBy, CompletedBy, RecallCounter, TripCounter, InternalDivision, InternalCostCenter, InternalZone, BatchNumber, UnbatchedEmp, EmailAddress, EmailStatus, AutoUpdateStatus, TechName, TechMinutes, TechHoldMinutes, TechRequired, TechReasonNotReq, TechComplete, TechNotes_LD, DateModified, DateEnteredSite, DateTargetCloseSite, DateCompleteSite, ArrivalSite, DepartureSite, SpclHndCode, FSRTechName, DateDispatchedSite, DateFaxedSite, SiteContactName, SiteContactPhone, Source, Escalation) VALUES ("; for($a=0; $a<96; $a++){ if($a == 0){ $query .= "'" . mysql_real_escape_string($WO[$x][$a]) . "'"; }else{ $query .= ", '" . mysql_real_escape_string($WO[$x][$a]) . "'"; } } $query .= ")"; $result = mysql_query($query) or die(mysql_error()); $t2 = time(); $t3 = $t3 + ($t2 - $t1); } echo "Database populated. . .<br /><br /><br />"; echo "Time taken: " . date('i:s', $t3);

          *Note: The script has not been tested.

            Magfersile wrote:

            I forgot to mention that I did not use all of those escape functions at the same time, as I used one that did not work, I commented it out and tried another.

            Yes, well, I kind of figured that.

            I just left them uncommented so they would stand out more I guess.

            Just kind of made it hard to pick out what should there and what shouldn't (some of those attempts don't do any escaping, and some remove it).

            One rule of thumb in programming is that if you're doing the same thing over and over and over then you're doing it wrong. There may be something with the all-over-the-place quoting and concatenation and interpolation you're doing in the query itself.

            $query = "Insert into WorkOrder(WorOrderNumber.....) Values ";
            foreach($WO as &$work_order)
            {
            	$work_order = array_map('mysql_real_escape_string', $work_order);
            	// Now put quotes around every single value, join them into one big comma-separated string,
            	// and put parentheses around them so they can go into the Insert statement.
            	$work_order = "('" . join("','", $work_order) . "')";
            }
            // To speed up the db interaction a bit, we'll insert all the records in a single statement instead of several dozen.
            $query = $query . join(",\n", $WO);
            

            Echo that and see if it makes sense.

              There may be something with the all-over-the-place quoting and concatenation and interpolation you're doing in the query itself.

              The reason for this is because the data that is going into varchar fields in the database get inputted as 'Array' instead of the contents of the array, and while there's probably a better way to fix it, what I was doing was

              '" . $WO[$x][0] . "',
              //instead of
              '$WO[$x][0]',

              Which seemed to fix it, save for the quotes, and running mysql_real_escape_string() didn't seem to fix the error message I was getting while trying to insert it into the database.

              Am I headed down a dead end here? and by that I mean is there a better way to insert a large xml file into a database while properly escaping quotes?

                Man I feel dumb, but I figured out why mysql wasn't liking my insert statement.
                This code:

                for ($z = 0; $z <= $number; $z++) 
                { 
                    $X = time(); 
                    for ($y = 0; $y <= 95; $y++) 
                    set_time_limit(30);     
                //didn't work $WO[$z][$y] = filter_var($WO[$z][$y], FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_LOW); //didn't work $WO[$z][$y] = mysql_real_escape_string($WO[$z][$y]); //didn't work $WO[$z][$y] = addcslashes($WO[$z][$y], '\''); $WO[$z][$y] = addcslashes($WO[$z][$y], '\"'); //didn't work $WO[$z][$y] = trim($WO[$z][$y],"\x22\x27"); //didn't work if (get_magic_quotes_gpc()) $WO[$z][$y] = stripslashes($WO[$z][$y]); else if (!get_magic_quotes_gpc()) $WO[$z][$y] = addslashes($WO[$z][$y]); $X2 = time(); $X3 = $X3 + ($X2 - $X1); }

                Should be:

                for ($z = 0; $z <= $number; $z++) 
                { 
                    $X = time(); 
                    for ($y = 0; $y <= 95; $y++) 
                    {  // might need these
                    set_time_limit(30);     
                //didn't work $WO[$z][$y] = filter_var($WO[$z][$y], FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_LOW); //didn't work $WO[$z][$y] = mysql_real_escape_string($WO[$z][$y]); //didn't work $WO[$z][$y] = addcslashes($WO[$z][$y], '\''); $WO[$z][$y] = addcslashes($WO[$z][$y], '\"'); //didn't work $WO[$z][$y] = trim($WO[$z][$y],"\x22\x27"); //didn't work if (get_magic_quotes_gpc()) $WO[$z][$y] = stripslashes($WO[$z][$y]); else if (!get_magic_quotes_gpc()) $WO[$z][$y] = addslashes($WO[$z][$y]); $X2 = time(); $X3 = $X3 + ($X2 - $X1); } // might need these }

                I forgot to include braces for the second for loop, and that's why the string escape functions seemed not to work because they were never being called. ::bangs head:: Curious though, usually you get an 'UNEXPECTED T_VARIABLE' error message or something if to leave off brackets or paranthesis. Anyone know why this wasn't the case with the for loop brackets?

                  Magfersile wrote:

                  Anyone know why this wasn't the case with the for loop brackets?

                  Sure:

                      for ($y = 0; $y <= 95; $y++)
                      set_time_limit(30);     

                  is a perfectly valid statement.

                    So, it's like an if statement that executes only one line of code?

                      Yes. It's addressed in the first few paragraphs on the if statement page of the manual. An IF executes the next statement. If you want that statement to be more than one line, you must use braces to delineate it. While the braces are optional for single-line statements, I still always use them for consistency and to avoid confusion when scanning the code while debugging or otherwise maintaining it.

                        Write a Reply...