richei Posted November 24, 2013 Share Posted November 24, 2013 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. Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted November 24, 2013 Share Posted November 24, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
richei Posted November 24, 2013 Author Share Posted November 24, 2013 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. Quote Link to comment Share on other sites More sharing options...
Solution Ch0cu3r Posted November 24, 2013 Solution Share Posted November 24, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
richei Posted November 24, 2013 Author Share Posted November 24, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
richei Posted November 24, 2013 Author Share Posted November 24, 2013 $value = "STR_TO_TIME('".$value."', '%m/%d/%Y')"; entered 0000-00-00 as the start date and 0.0027322404371 as the end date. Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted November 24, 2013 Share Posted November 24, 2013 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. Quote Link to comment Share on other sites More sharing options...
richei Posted November 24, 2013 Author Share Posted November 24, 2013 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. Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted November 24, 2013 Share Posted November 24, 2013 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. Quote Link to comment Share on other sites More sharing options...
richei Posted November 24, 2013 Author Share Posted November 24, 2013 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. Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted November 24, 2013 Share Posted November 24, 2013 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. Quote Link to comment Share on other sites More sharing options...
richei Posted November 24, 2013 Author Share Posted November 24, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.