PHP and Mysql

Anon

I'm pretty new to coding php. I bought the book PHP:fast&easy and I've taken off from there. This question has a little to do with php and a little to do with databases. Right now I am building a page that with ask the user for their city, state and the first six digits of their phone number.

The problem I have is that the current list of numbers I have is in Microsoft Access. I need to get it into Mysql somehow, and there are over 1000 entries so it would be impossible to put them all in by hand. Does anyone have experience with transfering databases from Access to Mysql? And if that's no good, does anyone know if you can code php to access...um...Access databases?

Thanks for the help.

Anon

probably the fastest, easiest method is to use Access to save the table as CSV (comma separated values) or tab-delimited or something and then use phpmyadmin to get it into your mySQL database.

jsagara

Do you know ASP at all? If not, it shouldn't be too hard to figure out what to do for this:

Write an ASP page that queries your Access database for the info. Open a file, and for each record, write an INSERT statement into the file (e.g., INSERT INTO table VALUES(...)). If you do everything properly, then you can use this as a dump file for MySQL.

The hardest part will be writing the ASP script. Once you get the dump file, the rest is cake from there.

kidestranged

Here's the way I always do it.

export the existing file:
1 - open up access
2 - open up the table that you want to export.
3 - under the file menu select export.
4 - under file type, select 'text files.'
5 - hit export all
6 - hit ok to everything which will seperate the fields by commas and enclose them in quotes.
7 - select a location and save the file.

transfer the file to the server that runs MYSQL
1 - if you are running MYSQL locally, don't bother. If it's on a server in cyberspace, FTP it to a folder that you'll remember later. Also remember the exact file name (blahblahblah.txt)

importing to mysql:
1 - create a table that has all of the same fields as the exported table in the exact order. Make sure that the field is the right kind and large enough. eg, don't have lastName VARCHAR(5) because long last names will create warnings and data loss during the import.
2 - table example:
create table friends (friend_ID int not null primary key auto_increment, name varchar(20) not null, address varchar(50), age int, etc.)
3 - import the table with this command:
load data infile "/path/to/the/file.txt"
into table nameOfTheTableYouCreated
fields terminated by ','
enclosed by '"'
lines terminated by '\n\r';

(note - if the file is local, the first part of the command should be load data local infile)

good luck.

jsagara

Yep, I like your way better.

apropos

agreed with you...
rather than using load data infile prefer using fgetcsv but both work...

sample using fgetcsv:

$fp = fopen ($userfile,"r");
while ($data = fgetcsv ($fp, 2500, ",")) {
$num = count ($data);
for ($c=0; $c<$num; $c++) {
$fullname = $data[0];
$userid = $data[1];
$password = $data[2];

$sql = "INSERT INTO user (fullname,userid,password) ".
    "VALUES ('$fullname','$userid',password('$password'))";
    $result = mysql_query($sql);

}

}

Anon

I am currently moving a bunch of data from Access to Mysql. I set up a MyODBC to connect to the Mysql server. I can link tables and copy and paste records from the access table right into the Mysql table with just a few clicks. If you have that capability, it works well for me.

Bruce

tasistro

If your access machine is not the same as the mysql machine you will need to grant permition to connect via MyODBC (add a record in the user table in the mysql database)

example
allows machine with ip 192.168.0.12 to connect remotely
(field in order as defined in table)
192.168.0.12
root

Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y

Create a mysql dbase structure identical to the access. Link the tables through odbc just as Bruce said. Open the original access dbase, select data, copy and paste. Done in a second. Has worke fine with our data.

Saludos
Gerardo