Populate Database

Nickosssss

does anybody know how to populate a database from a text file? With using script and NO loada database in file command....if yes please send me a sample if u have

thanks

devinemke

what is the exact formatting of the text file? show an example.

kakki

as a general answer to your question:
i did one using the bash .... like

#!/bin/bash
find /data --name "*.txt" | sed "s/^/INSERT INTO textfiles( name ) VALUES ( '/g" | sed
"s/$/' );/g" > inserts.sql
mysql < inserts.sql

the first command finds any files named *.txt in the directory( and subdir ),
the second command adds an "INSERT INTO ...." before the findings of each file
the 3rd command adds the ending for the insert-statement

Nickosssss

well i ve tried to do it with the LOAD DATA INFILE but only on MySQL which inserts the values to the table

but when i execute the query through a PHP file does not do it.

this is the code

.........
else if (isset($_POST['bt4']))
{
$popStud =
"LOAD DATA INFILE stud.txt INTO TABLE student FIELDS TERMINATED BY '|' ESCAPED BY '\' LINES TERMINATED BY '\r\n'";

	$res_pop = $db->query($popStud);						
}

kakki

well.....

do you have the legitimate rights( "FILE"-privilege) for this statements?
i just scanned the documentation, they have this "local" paragraph there ....
maybe your file is not accessable from the db-server???

Nickosssss

to tell u the truth i dont really knwo about those rights? do u know how can i check them?

kakki

check the GRANT section in the manual :

http://dev.mysql.com/doc/mysql/en/grant.html

there you will see all the privileges
( its the same with the operating system, you have folders you can browse or delete in and others where you dont have the rigt to do this )

Nickosssss

oh ok mate thanks a lot...i ll check it...🙂

Nickosssss

do u know if the path for the txt file needs to be set to the PHP directory or to the MySQL directory?

kakki

according to the mysql-manual
- you don't need to specify the "LOCAL" - thing in the load statement
- When locating files on the server host, the server uses the following rules:
* If an absolute pathname is given, the server uses the pathname as is.

so i think you don't need to specify anything ( except for the full pathname for the txt-file )