Jump to content

Accomodating a new datatype in an old script


richei
Go to solution Solved by Ch0cu3r,

Recommended Posts

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.

Link to comment
Share on other sites

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 by Ch0cu3r
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Solution

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 by Ch0cu3r
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by richei
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.