Databases

[occasion]

To develop my database, I installed Apache & MYSQL on my computer. Since I am still developing the site, I frequently recreate, and change the table structure using the following commands after deleting the database.

 

mysqladmin -u username -ppassword create databasename

mysql -u username -ppassword<filename.sql

mysqlimport -L -h localhost -u username -ppassword --fields-terminated-by="|" databasename c:/directory/tables/filename.txt

 

...

 

Is it possible for me to use this method of table construction on the server?

 

Thanks

[TCH-Bruce]

I think you could use the phpAdmin functions cPanel to accomplish this.

[TweezerMan]

These are all commands that are run from a shell prompt. You would not be able to run these commands directly on the server, as TCH does not provide shell access.

 

...I frequently recreate, and change the table structure using the following commands after deleting the database.

<{POST_SNAPBACK}>

Rather than deleting and recreating your database, you might want to run a query to drop all of the database tables instead. This would leave your database intact and you would not have to create it again.

 

mysqladmin -u username -ppassword create databasename

<{POST_SNAPBACK}>

This command creates a database. You would have to do this from the "MySQL Databases" page in CPanel (you cannot create a database in phpMyAdmin).

 

mysql -u username -ppassword<filename.sql

<{POST_SNAPBACK}>

You can use phpMyAdmin to import and run queries stored in a file such as this. On the left side of the phpMyAdmin page, click the middle icon ("SQL") to open a query popup window. There is a "Import Files" tab in this popup window where you can specify a file to be uploaded and imported into your MySQL database.

 

mysqlimport -L -h localhost -u username -ppassword --fields-terminated-by="|" databasename c:/directory/tables/filename.txt

<{POST_SNAPBACK}>

This command would need to be run as a LOAD DATA INFILE query in phpMyAdmin.

 

Hope this helps...

[occasion]

Thanks for the input. I'll review the information and experiment a little. Databases can be confusing, but are essential. Thanks again.

[occasion]

I created the database using cpanel.

 

Created a user with permissions.

 

Created an sql to create tables in the database

 

Example of code in sql:

 

Create Table tablename(

field1_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

field2_id SMALLINT UNSIGNED,

field3 VARCHAR(200)

);

 

Imported file and executed it

 

All tables were created perfectly.

 

Then I tried to add data to the table by creating a file contaning the following code.

 

LOAD DATA LOCAL INFILE 'c:dir\on\my\computer.txt' INTO TABLE logonname_databasename.tableName FIELDS TERMINATED BY '|';

 

 

Save file and imported it.

 

No error message in red appeared. Rather the following statements

 

Inserted rows: -1 (Query took 0.0070 sec)

SQL-query: LOAD DATA LOCAL INFILE 'c:dir\on\my\computer.txt' INTO TABLE logonname_databasename.tableName FIELDS TERMINATED BY '|'

 

No data was added to the table and yet no error message appeared.

 

I checked and verified that 'c:dir\on\my\computer.txt' exist and contains data.

 

Any suggestions on where I am going astray.

[occasion]

I created the database using cpanel.

 

Created a user with permissions.

 

Created an sql to create tables in the database

 

Example of code in sql:

 

Create Table tablename(

field1_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

field2_id SMALLINT UNSIGNED,

field3 VARCHAR(200)

);

 

Imported file and executed it

 

All tables were created perfectly.

 

Then I tried to add data to the table by creating the following sql.

 

LOAD DATA LOCAL INFILE 'c:dir\on\my\computer.txt' INTO TABLE logonname_databasename.tableName FIELDS TERMINATED BY '|';

 

Imported file and executed it

 

No error message in red appeared. Rather the following statements

 

Inserted rows: -1 (Query took 0.0070 sec)

SQL-query: LOAD DATA LOCAL INFILE 'c:dir\on\my\computer.txt' INTO TABLE logonname_databasename.tableName FIELDS TERMINATED BY '|'

 

No data was added to the table and yet no error message appeared.

 

I checked and verified that 'c:dir\on\my\computer.txt' exist and contains data.

 

Any suggestions on where I am going astray.

[TCH-Thomas]

I have merged your two identical topics/posts.

 

Please remember that there is no need to double post!

[TweezerMan]

From the MySQL documentation on LOAD DATA INFILE:

If the LOCAL keyword is specified, it is interpreted with respect to the client end of the connection:

When using phpMyAdmin, the 'client' end of the connection is the TCH server, as phpMyAdmin runs on the TCH server. You'd need to upload the file to the server, then run the LOAD DATA INFILE query specifying a server path to the file (/home/cpanelName/someDir/computer.txt).

 

To run a LOAD DATA INFILE query and be able to specify a file on your machine, you'd need to have a MySQL client running on your machine (such as phpMyAdmin installed on locally on your web server, or MySQL Query Browser), plus enable remote MySQL access on your account.