The14thGOD

I have a CMS that has a preview function which is basically a clone of the live data unless someone saves a draft. When they decide they want to make the draft live (published) I'm running into a small problem. I escaped the data when inserted into the draft table, so when I pull that data and put it into a query to update the live table it breaks because the data is not escaped. (magic quotes disabled). Is there an easy way to use an equivalent function of mysql_real_escape_string in a query to escape all the fields or do I just have to resort to going in and adding mysql_real_escape_string to all the data that could potentially have issues? Or an easier way to do it would be fine too =) (This may belong in mysql forums, sorry for posting it in php) Thanks, Justin

nope that didnt work, i got a different error however i found this tutorial: http://www.htmlcenter.com/blog/running-php-scripts-with-cron/ basically if you have Apache handling ur PHP and not CGI, you do: 30 * * * * http://www.example.com/cron.php

I have a cron job going and its finding the right file but it's emailing me back with errors. Syntax errors, but if I run it from the browser it runs fine. Error: /var/path/to/domain/httpdocs/csv_db.php: line 1: ?php : No such file or directory /var/path/to/domain/httpdocs/csv_db.php: line 2: syntax error near unexpected token `(' /var/path/to/domain/httpdocs/csv_db.php: line 2: ` $dbh = mysql_connect("localhost","username","password"); CronJob: 52 * * * * /var/path/to/domain/httpdocs/csv_db.php Start of PHP: <?php $dbh = mysql_connect("localhost","username","password"); mysql_select_db("db"); $date = date("Y-m-d H:i:s");?> Any ideas? Thanks in advance, Justin

with some further tweaking I managed to get it to work. (I forgot to do something on my end, your code was fine =). Thanks again for all of your help. It's much appreciated. Justin

unfortunantly i have to leave for the day, but Ill be back tomorrow. If you can help thats great, if not, thanks for all your help today, really appreciate it! Justin

I cleaned that up a bit too, slightly different, however, it gets the same 2816 rows and now theres single quotes all over in my table. magic_quotes_gpc is off I noticed this though, with the changes i made: <?php $line = trim($fcontents[$i]); $line = str_replace("'","\'",$line); $line = str_replace('"',"'",$line); $arr = explode("|", $line); //, is the delimter if($arr[21] == ''){ $arr[21] = '0000-00-00 00:00:00'; } if($arr[22] == ''){ $arr[22] = '0000-00-00 00:00:00'; } foreach($arr as $k=>$value){ if(empty($value)){ $arr[$k] = 'NULL'; } } ?> The error: (look at the dates, they are formatted right) INSERT INTO mytable VALUES (id,2,'A','timezone','status','name','zip','email','phone','ext ','address','','state','county','city','state','zip','CH','AD7587',NULL,500,2006-12-14 00:00:00,2009-10-29 00:00:00,'M','2009-10-29 17:10:17') When I copied it into sql in phpmyadmin and posted it i go this (look at the dates, not formatted): INSERT INTO mytable VALUES ( id, 2, 'A', 'timezone', 'status', 'name', 'zip', 'email', 'phone', 'ext ', 'address', '', 'country', 'county', 'city', 'state', 'zip', 'CH', 'AD7587', NULL , 500, 2006 -12 -1400 :00 :00, 2009 -10 -2900 :00 :00, 'M', '2009-10-29 17:03:31' ) or look here: 2006 -12 -1400 :00 :00, 2009 -10 -2900 :00 :00 Is MSSQL like inserting invisible type characters (sort of like how word does when you paste into a text area for a CMS)?

I'm wondering if this line is breaking it: $arr[$k] = "'".$arr[$k]."'"; I know theres usually problems with single quotes inside of double quotes, so like i can never do echo "$row['image_path']" but can do echo "$row[image_path]" I also swapped the code, however it still went through w/o triggering an error.

oh boy..just noticed, in the database, there are now double quotes (") all over the place here's the code now: <?php ini_set("display_errors", "1"); error_reporting(E_ALL); $fcontents = file ('./My.csv'); mysql_query("TRUNCATE tablename"); $date = date("Y-m-d H:i:s"); for($i=0; $i<sizeof($fcontents); $i++) { $line = trim($fcontents[$i]); $arr = explode("|", $line); //, is the delimter if($arr[21] == ''){ $arr[21] = '0000-00-00 00:00:00'; } if($arr[22] == ''){ $arr[22] = '0000-00-00 00:00:00'; } foreach($arr as $k=>$value){ if(empty($value)){ $arr[$k] = 'NULL'; } $arr[$k] = str_replace('"','',$value); $arr[$k] = mysql_real_escape_string($value); $arr[$k] = "'".$arr[$k]."'"; } $data = implode(',',$arr); //$data = preg_replace('^([0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2})$','\'\\1\'',$data); $sql = "INSERT INTO tablename VALUES (".$data.','."'$date') "; mysql_query($sql); if(mysql_error()) { echo mysql_error() ."<br>\n"; echo $sql; echo '<br /><br />'; echo $data; break; } } echo $i; ?>

Ya, I just dont know how to do it without going through each line lol. The only thing that echo's is $i and thats all the way at the end of the script after all the inserting takes place. And if it does error it should break out of the loop. Hmmm...puzzling indeed.

ok, after some troubleshooting (missed a '.' somewhere) it now runs through the script. There's 2823 rows of content however, only 2816 make it through. No errors. Any ideas? Thanks again for all of the help!

how do you do the NULL thing? I've never understood null and reading about it makes my head spin in circles. Most of the data in the db has null set to 'yes'. with a combination of that for loop and this line: $sql = "INSERT INTO tablename VALUES ("mysql_real_escape_string($data).','."'$date') "; I managed to get 306 rows in and no error : / (theres a total of 2883 rows). There's no MySQL error or php error so I'm not sure what happens that it just stops. I just get a white page (which I would expect from a completion)

That's what I was thinking too, but those are all over in the doc in the same spots but (now here's a confusing part) it only stops on the line below which is almost the end of the document. (there's over 2000 lines or something like that) However, when I check the database, there are only 9 rows. From the CSV: ID|1|"A"|""|"STATUS"|"NAME"|""|""|"PHONE"|"EXT "|"ADDRESS"|""|"COUNTRY"|"COUNTY"|"CITY"|"STATE"|"ZIP"|"AI"|""||||2009-10-26 00:00:00|"M" Sorry for the long delay in response, was in a meeting. Thanks again for any and all help, Justin

So for the last couple of days I have been trying to import this CSV outputted by a MSSQL into a MySQL database. This is being done by a PHP script that we want to run daily (cron job). The client has their own db that they get from somewhere else and they will upload it to the server where this script is. However I've been plagued with errors, is there something I need to do from a MSSQL server to MySQL server? Here's the PHP code: <?php ini_set ("display_errors", "1"); error_reporting(E_ALL); //CONNECTION INFO HIDDEN $fcontents = file ('./MY.csv'); mysql_query("TRUNCATE table_name"); $date = date("Y-m-d H:i:s"); for($i=0; $i<sizeof($fcontents); $i++) { $line = trim($fcontents[$i]); $arr = explode("|", $line); //, is the delimter if($arr[21] == ''){ $arr[21] = '0000-00-00 00:00:00'; } if($arr[22] == ''){ $arr[22] = '0000-00-00 00:00:00'; } foreach($arr as $value){ if($value == ''){ $value = "'1'"; } $value = mysql_real_escape_string($value); } $arr[21] = '\''.$arr[21].'\''; $arr[22] = '\''.$arr[22].'\''; $data = implode(',',$arr); $data = str_replace('"',"'",$data); //$data = preg_replace('^([0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2})$','\'\\1\'',$data); $sql = "INSERT INTO table_name VALUES ($data".','."'$date') "; mysql_query($sql); if(mysql_error()) { echo mysql_error() ."<br>\n"; echo $sql; echo '<br /><br />'; echo $data; break; } } echo $i; ?> 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 ','0000-00-00 00:00:00','2009-10-29 00:00:00','M','2009-10-29 13:27:02')' at line 1 INSERT INTO table VALUES (ID,1,'A','','STATUS','NAME','','','PHONE','EXT ','ADDRESS','','COUNTRY','CITY','COUNTY','STATE','ZIP','AI','',,,'0000-00-00 00:00:00','2009-10-29 00:00:00','M','2009-10-29 13:27:02') Sensitive data removed. I thought it was due to the date being blank so thats why i have code that sets it to 0000-00-00 00:00:00 however that didn't fix it, i then added the single quotes (') around the field because it was breaking worse then I think. (I've tried a lot of things so I'm sure I missed something). Any ideas/help is much appreciated. Thanks, Justin

ah crap....missed that one...lol looks like theres another error though, I think the CSV file is just too messed up... thanks for noticing that!

I'm updating a database from a CSV file, for some reason the database isn't putting quotes around the dates, but it breaks if the data doesn't have quotes around it. Now I originally just targeted those fields in an array and added the quotes (e.g. $arr[22] = '\''.$arr[22].'\'' and that worked until it got to a field with no date in it. That broke that so I had to go back to trying to just find a regex to add the quotes (and hope that the insert works if there is no date and no quotes). Here's the regex I wrote (I'm by no means an expert on this) and it doesn't work so any help would be great; <?php $data = preg_replace('^([0-9]{4}-[0-9]{2}-[0-9] [0-9]{2}:[0-9]{2}:[0-9]{2})^','\'\\1\'',$data); ?> All dates (except ones that don't exist) follow that pattern since it's stored in a MySQL db. Thanks for any and all help, Justin

hmm...there seems to be 25 cols of data in the query...but only 24 are in the database...

this looks sloppy, and it is, but I think it's what ur mainly asking for? int smallint varchar null varchar null varchar null varchar varchar null varchar null varchar null char null varchar null varchar null varchar null varchar null varchar null char null char null char null varchar null decimal(9,0) null decimal(9,0) null datetime null datetime null datetime

That's what I thought at first but the CSV is being exported by phpMyAdmin, shouldn't that of put quotes around the dates? edit* I tried putting quotes around the date that I made and I still have the same error. I thought maybe my date was different or something, so thats why I tried this.

I'm basically doing a truncate followed by a re-import of information, this will be set up as a cron job later using a CSV file, here's the 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 '00:00:00,2009-10-26 00:00:00,'M',2009-10-26 14:04:26)' at line 1 INSERT INTO Justin_Demographic VALUES(11111,2,'A','CMD','STATUS','NAME ','11111','EMAIL','PHONE','EXT ','ADDRESS','','US','CITY','COUNTY','STATE','ZIP','AB','ABC',0,500,2006-12-14 00:00:00,2009-10-26 00:00:00,'M',2009-10-26 14:04:26) Here's the PHP code to make it happen: <?php ini_set ("display_errors", "1"); error_reporting(E_ALL); $dbh = mysql_connect("localhost","user","pass"); mysql_select_db("db"); $fcontents = file ('./My_Demog.csv'); mysql_query("TRUNCATE Justin_Demographic"); $date = date("Y-m-d H:i:s"); for($i=0; $i<sizeof($fcontents); $i++) { $line = trim($fcontents[$i]); $arr = explode("|", $line); //, is the delimter $data = implode(',',$arr); $data = str_replace('"',"'",$data); $sql = "INSERT INTO Justin_Demographic VALUES ($data".','."$date) "; mysql_query($sql); if(mysql_error()) { echo mysql_error() ."<br>\n"; echo $sql; echo '<br /><br />'; echo $data; break; } } echo $i; ?> Sensitive data removed for obvious reasons. Thanks for any and all help, Justin

sigh, answer was sittin in front of my face, got it to work. thanks! justin

I'm fed up. I had issues with code that I have working on a different site that didn't work on my current site. I found a way to fix that but now I'm running into the same error on another page that I can't think of a way around: 'Parse error: syntax error, unexpected T_VARIABLE, expecting ']' in /my/path/to/file on line 75' Here's the original code: (ine 75 is the line with the link in it.) <?php $adquery = "SELECT * FROM ad_space WHERE id=1"; $adresult = mysql_query($adquery); $adrow = mysql_fetch_assoc($adresult); for($i=1;$i<6;$i++){ if($row['link'.$i] == '') { $row['link'.$i] = '#'; } echo "<a href=\"$row[link$i]\" target=\"_blank\"><img src=\"$row[image$i]\" alt=\"$i\" /></a>"; } ?> I changed it to this hoping it would work but it didnt, values are blank. <?php $adquery = "SELECT * FROM ad_space WHERE id=1"; $adresult = mysql_query($adquery); $adrow = mysql_fetch_assoc($adresult); for($i=1;$i<6;$i++){ $var = "link".$i; $var2 = "image".$i; if($row['link'.$i] == '') { $row['link'.$i] = '#'; } echo "<a href=\"$row[$var]\" target=\"_blank\"><img src=\"$row[$var2]\" alt=\"$i\" /></a>"; } ?> As always, any help is appreciated. Thanks, Justin

after adding my email address i got the following: /bin/sh: -c: line 0: unexpected EOF while looking for matching ``' /bin/sh: -c: line 1: syntax error: unexpected end of file

I also have the time set up to be : 10 * * * 5 so every 10minutes on friday right? edit* every 10 minute of every hour, so once an hour on the 10 mark

I tried in this in the admin panel and it doesnt seem to be working, any idea why? I am on bluehost and put it in the advanced cron job scheduler: mysqldump -uUSERNAME -pPASSWORD DATABASE | gzip -9 -c > /MY/PATH/db_backups/backup-.`date +%d-%m-%Y`.sql.gz Thanks for any and all help

thanks! i wish i could dive deeper into PHP however I'm the only developer and get thrown on a lot of html/css/flash jobs :/ Justin