Accomodating a new datatype in an old script

[richei]

I had to change a datatype to accommodate a script change (done here) and while i know what i have to do, mysql is telling me i can't do it because of implode.  The script is below

 

if($companyTbl == "venzo_app_sales" || $companyTbl == "venzo_itunes_sales") {
        
        /*for each record in the report...*/
        for($i = 1; $i < count($fcontents); $i++) {
            $line = $fcontents[$i];
            $arr = explode("\t", $line);
       
            /*santitise the values*/
            foreach($arr as $key => $value) {

                $arr[$key] = trim(mysql_real_escape_string($value));
                if(strlen($arr[$key]) == 10) {
                    $arr[$key] = "STR_TO_TIME(".$arr[$key].", '%m/%d/%Y')";
                }
            }    
    
            if(strtolower($arr[0]) != "total") {
                /*number of fields in the DB exceeds number of fields in the record*/
                if($sqlFieldNum > count($arr)) {
                    $error[$i] = true;
                    for($a = 0; $a < count($sqlFields); $a++) {
                        echo "{$a}) MYSQL: {$a} => {$sqlFields[$a]} - Report: {$a} => {$arr[$a]}<br>";
                    }
                    echo "# of fields in the table (" . $sqlFieldNum . ") is greater than the # of fields in the report (" . count($arr) . ")!<br />";
                   
                    /*number of fields in the record exceeds number of fields in the DB*/
                } else if($sqlFieldNum < count($arr)) {
                    $error[$i] = true;
                    for($a = 0; $a < count($arr); $a++) {
                        echo "{$a}) MYSQL: {$a} => {$sqlFields[$a]} - Report: {$a} => {$arr[$a]}<br />";
                    }
                    echo "# of fields in the report (" . count($arr) . ") is greater than the # of fields in the table (" . $sqlFieldNum . ")!<br />";
                }
          
                /*if there is no error insert the record into the table else continue onto the next record*/
                if(isset($error[$i]) != true) {
                
                    $sql = "INSERT INTO {$companyTbl}(" . implode(', ', $sqlFields) . ") VALUES ('" . implode("', '", $arr) . "')";
                    $ins = mysql_query($sql) or die(mysql_error());
                
                    if($ins) {
                        copy($report, $path."/".$filen);
                    }
                } else {
                    echo "Record {$i} not inserted!";
                }
            }
        }
    }

  The problem is that implode is adding quotes around each thing, which wasn't a problem before since they were all either int or varchar, but i had to change the start and end dates to DATE and am using

 

if(strlen($arr[$key]) == 10) {
  $arr[$key] = "STR_TO_TIME(".$arr[$key].", '%m/%d/%Y')";
}

 

And this is error i get is - 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 '%d/%Y')', 'STR_TO_DATE(11/03/2012, '%m/%d/%Y')', '', 'USVGG1289912', 'USVGG12899' at line 1

 

I don't know how to tell implode to not to put quotes around the mysql functions, or if its even possible  Does anyone have any suggestions. I'm kinda screwed until i get this fixed.

[Ch0cu3r]

Change how you implode.

 

So change

                $arr[$key] = trim(mysql_real_escape_string($value));
                if(strlen($arr[$key]) == 10) {
                    $arr[$key] = "STR_TO_TIME(".$arr[$key].", '%m/%d/%Y')";
                } 

to

                $value = trim(mysql_real_escape_string($value));
                if(strlen($value) == 10) {
                    $arr[$key] = "STR_TO_TIME('".$value."', '%m/%d/%Y')";
                }
                else
                {
                     $arr[$key] = "'$value'";
                } 

and change the implode to

implode(", ", $arr)

Edited November 24, 2013 by Ch0cu3r

[richei]

That fixed one problem, but now it tells me that start_date can not be null.  I did a print_r on $arr and this is what it printed out (well, one of many)

Array(    [0] => STR_TO_DATE(09/30/2012, '%m/%d/%Y')    [1] => 11/03/2012    [2] =>     [3] => USVGG1289912    [4] => USVGG1289912    [5] => 1    [6] => 1.29    [7] => 1.29    [8] => AUD    [9] => S    [10] => 519308022    [11] => Chocolate Bar    [12] => The Question (feat. Mac Miller & Lil Wayne) [Ringtone]    [13] => VG Group    [14] =>     [15] => PR    [16] =>     [17] => AU    [18] =>     [19] =>     [20] => 2.19    [21] => AUD)

Array(    [0] => STR_TO_DATE(09/30/2012, '%m/%d/%Y')    [1] => STR_TO_DATE(11/03/2012, '%m/%d/%Y')    [2] =>     [3] => USVGG1289912    [4] => USVGG1289912    [5] => 1    [6] => 1.29    [7] => 1.29    [8] => AUD    [9] => S    [10] => 519308022    [11] => Chocolate Bar    [12] => The Question (feat. Mac Miller & Lil Wayne) [Ringtone]    [13] => VG Group    [14] =>     [15] => PR    [16] =>     [17] => AU    [18] =>     [19] =>     [20] => 2.19    [21] => AUD)

Not really sure why the first one is different, but it is. I just realized that apple gave me reports for 2012 instead of 2013, so i'm kinda glad this didn't work.

[Ch0cu3r]

it might be because the dates are not quoted in the str_to_date function.

 

Change the foreach loop to

/*santitise the values*/
foreach($arr as $key => $value) {

    $value = trim(mysql_real_escape_string($value));
    if($key == 0 || $key == 1) { // only apply this to the first ($arr[0]) and second ($arr[1) item in $arr
        $value = "STR_TO_TIME('".$value."', '%m/%d/%Y')"; // wrap date in quotes
    } else {
        $value = "'$value'";
    }

    $arr[$key] = $value;
}

 

Not really sure why the first one is different, but it is.

Because you are using print_r within the foreach loop probably.

Edited November 24, 2013 by Ch0cu3r

[richei]

Chorus, i'll try yours, see what i get

 

Had to do it another way, and fix an quote escape problem, but i got it fixed.

 

Thank you for the help!

Edited November 24, 2013 by richei

[richei]

$value = "STR_TO_TIME('".$value."', '%m/%d/%Y')"; entered 0000-00-00 as the start date and 0.0027322404371 as the end date.

[Ch0cu3r]

What is the data type you are using for the start and end date columns? They both should be set to DATE

 

 

0000-00-00 as the start date and 0.0027322404371 as the end date.

That to me suggests you are not implementing my code correctly.

 

Can you post your actual code you are using now.

[richei]

I copied and pasted what I needed, the datatype is set to DATE.

 

if($companyTbl == "venzo_app_sales" || $companyTbl == "venzo_itunes_sales") {
  
  /*for each record in the report...*/
  for($i = 1; $i < count($fcontents)-3; $i++) {
   $line = $fcontents[$i];
   $arr = explode("\t", $line);
      
   /*santitise the values*/
   foreach($arr as $key => $value) {
    $arr[$key] = trim(mysql_real_escape_string($value));
    //if(strlen($arr[$key]) == 10) {
    if($key == 0 || $key == 1) {
     $value = "STR_TO_TIME('".$value."', '%m/%d/%Y')";
     //$arr[$key] = date('Y-m-d', strtotime($arr[$key]));
     //$arr[$key] = "'$arr[$key]'";
    } else {
     $arr[$key] = "'".trim(mysql_real_escape_string($value))."'";
    }
   }
   
   if(strtolower($arr[0]) != "total") {
    /*number of fields in the DB exceeds number of fields in the record*/
    if($sqlFieldNum > count($arr)) {
     $error[$i] = true;
     for($a = 0; $a < count($sqlFields); $a++) {
      echo "{$a}) MYSQL: {$a} => {$sqlFields[$a]} - Report: {$a} => {$arr[$a]}<br>";
     }
     echo "# of fields in the table (" . $sqlFieldNum . ") is greater than the # of fields in the report (" . count($arr) . ")!<br />";
             
     /*number of fields in the record exceeds number of fields in the DB*/
    } else if($sqlFieldNum < count($arr)) {
     $error[$i] = true;
     for($a = 0; $a < count($arr); $a++) {
      echo "{$a}) MYSQL: {$a} => {$sqlFields[$a]} - Report: {$a} => {$arr[$a]}<br />";
     }
     echo "# of fields in the report (" . count($arr) . ") is greater than the # of fields in the table (" . $sqlFieldNum . ")!<br />";
    }
         
    /*if there is no error insert the record into the table else continue onto the next record*/
    if(isset($error[$i]) != true) {
    
     //$sql = "INSERT INTO {$companyTbl}(" . implode(', ', $sqlFields) . ") VALUES ('" . implode("', '", $arr) . "')";
     $sql = "INSERT INTO {$companyTbl}(" . implode(', ', $sqlFields) . ") VALUES (".implode(", ", $arr).")";
     //echo $sql."<br />";
     $ins = mysql_query($sql) or die(mysql_error());
    
     if($ins) {
      copy($report, $path."/".$filen);
     }
    } else {
     echo "Record {$i} not inserted!";
    }
   }
  }
 }

I just finished deleting all the bad records and uploaded the reports again.

[Ch0cu3r]

You coded the foreach loop wrong. Use the foreach loop I posted in reply #4

 

Notice where I use the $arr[$key] and $value variables.

[richei]

I saw that, didn't know if it was part of the problem, so i changed to something i knew worked.  Anyways, i switched TIME to DATE and its back to inserting dates as they should be.  Also had to change the datatype on the end date to date, thought i had already done it.

[Ch0cu3r]

 

i switched TIME to DATE and its back to inserting dates as they should be.  Also had to change the datatype on the end date to date, thought i had already done it.

So they was never set to DATE datatype. That contradicts what you said before. 

[richei]

Yes and no.  The start date was set to Date to add in something else, and i had done the end date conversion on another table.  But i forgot to do it on the one i was working with. Probably would help if i wasn't dealing with a cold right now.

 

i was using date() and strtotime() to change the date format and it was inserting properly, but messed up both columns when i added in the STR_TO_TIME(). 

Edited November 24, 2013 by richei