Jump to content

The14thGOD

Members
 View Profile  See their activity

  • Posts

    256

  • Joined

  • Last visited

    Never

 Content Type 

Posts posted by The14thGOD

    [SOLVED] mysql_real_escape_string or equiv in sql?

    in PHP Coding Help

    Posted

    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

    [SOLVED] Cronjob returns syntax error

    in PHP Coding Help

    Posted

    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

    [SOLVED] MSSQL to MySQL

    in Microsoft SQL - MSSQL

    Posted

    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)?

    [SOLVED] MSSQL to MySQL

    in Microsoft SQL - MSSQL

    Posted

    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.

    [SOLVED] MSSQL to MySQL

    in Microsoft SQL - MSSQL

    Posted

    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;
?>

    [SOLVED] MSSQL to MySQL

    in Microsoft SQL - MSSQL

    Posted

    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.

    [SOLVED] MSSQL to MySQL

    in Microsoft SQL - MSSQL

    Posted

    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)

    [SOLVED] MSSQL to MySQL

    in Microsoft SQL - MSSQL

    Posted

    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

    [SOLVED] MSSQL to MySQL

    in Microsoft SQL - MSSQL

    Posted

    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

    preg_replace mysql date

    in Regex Help

    Posted

    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

    error in query, can't find it...

    in MySQL Help

    Posted

    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

    error in query, can't find it...

    in MySQL Help

    Posted

    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.

    error in query, can't find it...

    in MySQL Help

    Posted

    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

    [SOLVED] no idea what to do...

    in PHP Coding Help

    Posted

    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

    Something wrong with this cron job?

    in Miscellaneous

    Posted

    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

×
×
  • 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.