THL Toolbox > Images & Immersive Technologies > Technically Processing Images > Image Processing - Legacy Procedures > Image Processing Steps for Image Database > Import Data To Mysql

Image Processing Steps for Image Database: Import Data To Mysql

Contributor(s): David Newman and Dan Haig

Phase 1: Put data on Local host.

1. Go to the LOCAL version of the database to import the txt file. For some reason I can import from a txt file if I am running the phpMyAdmin on my localhost. If the data isn't UTF8, actually it's not big deal and you can import the data by using the phpMyAdmin on datastore to import right into the MySQL on datastore. But that version of phpMyAdmin does not support UTF8. So if you have UTF8 text then you have to do it this way I think.

2. from tab'd list of column headers, create comma-delimited list of the FIELDNAMES (if lots of fields to contend with, else, do it on the fly). Have it handy.

3. Now, taking the tab-delimited utf-8 file of the records, zap the column headers!

4. In your localhost version of phpMyAdmin go to your localhost MySQL image database. Choose dln4n_media, select the table you want, i.e."images". Then click the Structure tab and at the very bottom choose "Insert data from textfile into table".

5. Browse to your file; do NOT replace table data.

6. Fields terminated by t

7. Put comma-delimited list of columns into "column names".

8. LOAD method is Data.

9. Submit, watch it work.

Phase 2: Export from Localhost the selected records as a .sql file.

Now you'll need a .sql file to upload to datastore, which you will generate from the localhost database that you're still in. It would pay to get the 'shortcut' SQL syntax for doing something like querying: SELECT * FROM `images` WHERE `photographer` LIKE '%(photog's name)%' …and doing a dump of THAT.

Here is the longer but actual way to do this:

1. Go to "images"; click Search; search by 'Photographer', operator = LIKE %(photog's name)% 2. bottom of results page, click Export 3. On export page, select Export "SQL", be sure Structure and Data are selected. Select Save as File. Click Go. Save to disk. 4. NOTE - YOU CAN ONLY UPLOAD 2.5mb of data at a time, regardless if .txt or .sql, via PHP/MyAdmin, into MySQL. That equals about 5000 records with all fields for the image db.

Phase 3: Import the sql file to datastore

1. Open the .sql file in a text editor and get rid of any fields that you may not want. Keep the stuff under "Dumping data for table `(tablename)`". Save the file. 2. Now still within your localhost version of phpMyAdmin click the home icon and go to the version of the database on Datastore. 3. Choose the database and table to work with (ie dln4n_media/images). 4. Click the SQL tab. 5. Under "Location of the Text File", browse for.sql file and click Go.

YOU་ARE DONE!

…but you had better go see if your image collection is now appearing on the live web site.

Provided for unrestricted use by the Tibetan and Himalayan Library