|
How to
use your
offline database with MySQL
- Read our MySQL
introduction
- Export data from
MS Access or FileMaker Pro
- Import data into
MySQL
- Congratulations!
1.
Before you start, read our
introduction to MySQL
Click here to read it. It's imperative that your
MySQL table matches exactly the field order of your export file from your local
database (being Microsoft Access or FileMaker Pro). You may want to also evaluate
the best field types once you're at it.
2. Export data from MS Access
or FileMaker Pro
The following instructions will try to help you in exporting the data from your
application. Check your database manual for more detailed information. The goal
is to export the relevant data in a comma-separated text format.
In Microsoft Access:
Select the table or query you want to export and click Export...
from the File menu. In the dialog box that will appear
choose the location where you want to save your export file, the file name, and
choose Text Files in the Save
as type: drop-down list. Click the Save
button to bring up the Export Text Wizard. Click
the Next button and make sure the delimiter is
Comma and the Text Qualifier
is ". Click Next and verify
the file path and name, then click Finish.
In FileMaker Pro:
Browse the records you want to export. You can use Find,
Omit or Omit Multiple
to create restrict the records you want to export. Sort the records in the order
you want them exported. In Browse Mode, choose
Import/Export from the File
menu and then choose Export Records. In the dialog box that will appear choose the
location where you want to save your export file, the file name, and choose
Comma-Separated Text in the Type:
drop-down list. Click the Save
button to bring up the Export Field Order dialog
box. Here choose the fields you wish to export and their order as needed. Select
a format option for the fields and then click Export.
3. Import data
into MySQL
Now use your preferred
FTP software to create a folder in public_html on your
web server and call it db_upload. Then upload the text
export file to this folder in ASCII format. Now create
a text file on your preferred text editor and copy and paste the following PHP code
(where
vsxxxxx
is your user name,
12345
is your password,
vsxxxxxDB
is your database name,
table_name is your MySQL table name and
export_file_name.txt
is the name you gave to your export text file):
<script language="php">
# MySQL database User ID, Password and
DB name
$sql_id = "vsxxxxx";
$sql_pwd = "12345";
$sql_db = "vsxxxxxDB";
# Connect to the database
mysql_connect('','$sql_id','$sql_pwd');
# Delete the current content of the
table
$result = mysql_db_query('$sql_id',"DELETE FROM table_name")
or die ("Invalid DELETE query");
# Optimize the current table (recover
empty space)
$result = mysql_db_query('$sql_id',"OPTIMIZE TABLE table_name")
or die ("Invalid OPTIMIZE query");
# Load local comma separated, fields
enclosed by quotes text database - File has to be in the same directory of this
file
$result = mysql_db_query('$sql_id',"LOAD DATA LOCAL INFILE 'export_file_name.txt'
INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED
BY '\"'") or die ("Invalid DATA LOAD query");
# Get how many records are present in
the table now
$result = mysql_db_query('$sql_id',"SELECT * from table_name")
or die ("Invalid SELECT query");
$rows_count = mysql_num_rows($result);
echo "Records: $rows_count"; mysql_free_result($result);
</script>
Save the file with the name
import.php3 and upload it to the
db_upload folder on the web server in ASCII format.
Now open your preferred web browser,
and go to: http://your-domain.com/db_upload/import.php3 (change the domain accordingly)
After a few seconds a page showing how many records were imported should appear.
Congratulations! You have just
imported your offline database
into your online MySQL database.
You are now ready to design the pages to
retrieve this data and use it. For help
on setting up this system, to make a one-click publishing system, or for all your
PHP/MySQL development needs, contact me contact me via our
online form.
|
