Planetarion Forums

CaShY · 16 Dec 2004, 16:14

Ok proper pain in the arse this

anyone see whats up with

Code:

SQL :
LOAD DATA LOCAL INFILE '/home/prize/public_html/admin/data/data1103210151' INTO TABLE `results` FIELDS TERMINATED BY ',' ESCAPED BY '\' LINES TERMINATED BY '\r\n' (`userid` , `network` , `profit`) 

ERROR :
Could not query DB: 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 '\r\n' (`userid` , `network` , `profit`)' at line 1

I have also tryed

Code:

SQL :
LOAD DATA INFILE '/home/prize/public_html/admin/data/data1103210220' INTO TABLE `results` FIELDS TERMINATED BY ',' ESCAPED BY '\' LINES TERMINATED BY '\r\n' (`userid` , `network` , `profit`) 


ERROR :  
Could not query DB: 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 '\r\n' (`userid` , `network` , `profit`)' at line 1

i have been looking at it for about an hr now and can't figure anything out.

The file does exsist and the sql as the same as it is in the manual.

Thanks for any Help

pablissimo · 16 Dec 2004, 16:29

Escape the backslash in the ESCAPED BY clause?

eg

Code:

LOAD DATA LOCAL INFILE '/home/prize/public_html/admin/data/data1103210151' INTO TABLE `results` 
FIELDS TERMINATED BY ',' ESCAPED BY '\\' 
LINES TERMINATED BY '\r\n' (`userid` , `network` , `profit`)

Edit:
Change in bold

CaShY · 16 Dec 2004, 16:29

also the php code is

Code:

<?php

require 'functions.php';
require 'admin_navbar.html';
require 'options.php';

db_open();
	if (is_uploaded_file($_FILES['csv']['tmp_name']))
	{
    	$file = $_FILES['csv']['tmp_name'];
        $name = "data";
        $name .= time();
        if (move_uploaded_file($_FILES['csv']['tmp_name'],'/home/prize/public_html/admin/data/'.$name.".csv")) {
        $file = "/home/prize/public_html/admin/data/".$name.".csv";
		echo 'File Uploaded!<BR><BR>';

        $sql =  "LOAD DATA INFILE '$file' INTO TABLE `results` FIELDS TERMINATED BY ',' ESCAPED BY '\\' LINES TERMINATED BY '\\r\\n' ".
        	 "(`userid` , `network` , `profit`)";
             echo "$sql<br>";
	    $result = db_query($sql);
	    if ($result)
        {
	        $time = time();
            echo "loaded";
	        $datefix = db_query("UPDATE results SET date = '$time' where date = -1");
	    }  else {
        echo "Failed";
        }
        }
        else
        {
        echo "failed";
        }
	}
	else
	{
	?>
	    <form action="admin_results.php" method="post"  enctype="multipart/form-data"><br>
	    CSV File: <input type=file name="csv"><br>
	    <br>
	    <input type=Submit name=Upload value='Upload'>

	<?php
	}
db_close();
?>

The functions all work fine.

pablissimo · 16 Dec 2004, 16:36

Code:

        $sql =  "LOAD DATA INFILE '$file' INTO TABLE `results` FIELDS TERMINATED BY ',' ESCAPED BY '\\' LINES TERMINATED BY '\\r\\n' ".
        	 "(`userid` , `network` , `profit`)";

to

Code:

        $sql =  "LOAD DATA INFILE '$file' INTO TABLE `results` FIELDS TERMINATED BY ',' ESCAPED BY '\\\\' LINES TERMINATED BY '\\r\\n' ".
        	 "(`userid` , `network` , `profit`)";

CaShY · 16 Dec 2004, 16:49

Code:

Could not query DB: Access denied for user: 'prize@localhost' (Using password: YES)

now

is werid cause i am using the top level password that allows me access to all the databases, not just the one which the tables in.

and in phpmyadmin

it allows me to run that query

CaShY · 16 Dec 2004, 16:52

Code:

LOAD DATA LOCAL INFILE '/home/prize/public_html/admin/data/data1103212200.csv' INTO TABLE `results` FIELDS TERMINATED BY ',' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' (`userid` , `network` , `profit`)
Could not query DB: The used command is not allowed with this MySQL version

guess i'll have to write a php phaser and do it the hard way

lol

pablissimo · 16 Dec 2004, 17:35

http://forums.devshed.com/archive/t-1904

Third reply, might be of some use I guess, though it doesn't quite match what's going wrong in yours.