--- Tedit kap <[EMAIL PROTECTED]> wrote: > Here are a few beginner questions on the same issue, as I am starting on my > first database: > > 1-what is the most common method of updating a database? (sending query from > web page, login to your database account and do it in your control panel > there, import from somewhere, or other etc...) > > 2-In relation to question #1, I have been looking around the web to see what > people are doing to get an idea, but I didn't see anything mentioned about > excel and then importing this to mysql. Wouldn't this be much more simpler? > Just prepare something in excel and import to mysql I mean, instead of > dealing with queries? > > 3-Similarly, to backup the database what is the best way? Is excel a good > way, if not what is? > > 4-If excel is indeed a good way to update and backup, can anybody refer me to > somewhere for further info? I saw some programs that you buy for this when I > googled it. Or what else is the best approach? > > Thanks > > Matt
1 - It depends on the purpose of your database design. If you were loading in information about zip codes in the U.S. you'd want to be able to process the entire table of data at once, perhaps from a file. If you were collecting data from a user, it would be done by inserting or updating data supplied through a web form. 2 - Excel files are, by default, binary encoded and not a practical medium for importing into MySQL. Excel data can be exported into a standard format that can be imported, with some effort. For example Excel can generate text files in either tab-delimited or comma-separated values format (CSV). Each has problems in terms of importing data. For example, the tab-delimited doesn't work well when the data contains newlines. CSV files are tedious to work with when they have quotation marks. Look at the MySQL documentation for LOAD DATA INFILE. The phpMyAdmin web application can be helpful in importing data. A tab-delimited file generated from PHP can be a useful way to get raw data into Excel. 3 - Command-line access to your server (through SSH) makes administering a server with MySQL easier. For example, the mysqldump command can export either a single table or an entire database to a human-readable plain-text file. The file will contain not only the CREATE TABLE statements used to define the table but also the INSERT statements necessary to recreate the table. Reading one of these files into a MySQL connection will effectively create the table and load the data. On small tables this is no big deal since the operation can be performed quickly. On large tables with indexing it is sometimes necessary to turn off the indexing while inserting the rows and perform the indexing as a final step. Many people use mysqldump in conjunction with a cron job to save a copy of the database. You may want to arrange this so that you have multiple files, perhaps one per database table. This way, if you need to restore a table, you have an easier time. Slogging through multi-megabyte text files can be hard for most text editors. 4 - Excel is not a good way to do a backup. You can't easily keep track of the data types for each field. Although you can get data into Excel by exporting a tab-delimited file, getting it back cleanly is rarely perfect. James Keeline
