SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Made with a Mac! philm's Avatar
    Join Date
    Sep 2001
    Location
    Portsmouth, UK
    Posts
    735
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Access -> CSV -> MySQL -> Website

    Hi all, i have a new project for a client. A small part of it i was hoping to discuss with you all, to make sure i wasn't heading for disaster. Just to clarify i've got the famous sitepoint book, and have created quite a few little PHP/MySQL 'toys' but am far from an expert, hence this post

    The client has their DB in Microsoft Access, and requires some sort of customer login/tracking on their website, however they have no wish to go the full e-commerce route. I have yet to receive my copy of Access so i am taking my best 'guess' at what i think it can do We will recieve part of their DB to play with while developing.

    The basic plan, rightly or wrongly, is to export certain 'KEY' fields (order details), from Access to a CSV file. I would only need a few fields along the lines of [order ref] [customer id] [description] [status] [complete]. I would also need the client to add [username] and [password] fields to their customer list. I was hoping i could export all this info as one CSV file, but i think it may have to be 2 CSV files. 1 for customers which has USR and PASS for PHP sessions, and the 2nd containing the order details which i can display on their 'Order Status' screen once they have logged in.

    The client would then upload the CSV file(s) to a pre-determined directory on the web server.

    To my (limited ) knowledge, i don't think it would be too difficult (with the help of all you guyz or an existing script ) to create a PHP script where the client goes to an admin page, clicks a button, 'UPDATE ORDER STATUS' or similar, and the CSV file(s) are read in, and converted to INSERT statements, then inserted into the SQL DB.

    The customer order details when viewed on the website would now be upto date. This would happen maybe once or twice a day.

    If i cant have the [username] and [password] in the above CSV file i would just create a new button for the client to click 'UPDATE CLIENT LIST' or similar, which would do the same thing but on a different table which would drive the PHP session logins. This would happen only when they add a new customer to their Access DB, not that often as they tend to have a core of customers who order lots

    I know the above solution is far from 'Normal', but i don't want to have to upload the whole .mdb file 2/3 times a day, just enough details to enable their customers to login and check their orders.

    Thanks for you time, thoughts and comments, oh yeh, no laughing at my 'noobi' ignorance

  2. #2
    SitePoint Guru marcel's Avatar
    Join Date
    Nov 2000
    Posts
    920
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is not a script. It's affordable software. It's worth a test.

    Get the Access 2 Mysql conversion tool from
    convert-in.com . They have seaveral solutions and the demo versions create great results.

    You might have to reestablish your auto increment values.

    I am not affiliated with this software in anyway.

  3. #3
    Made with a Mac! philm's Avatar
    Join Date
    Sep 2001
    Location
    Portsmouth, UK
    Posts
    735
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey thanks, looks like that'll be a great a help

    I have one little question though....

    MySQL dump file

    Access-to-MySQL allows users to perform deferred conversion and get more control over the process. Following this way, the program stores the contents of the source database into a local dump file instead of moving it to MySQL server directly. The resulting file contains MySQL statements to create all tables and to fill them with the data. You will be able to import this file into existing MySQL database via MySQL client as follows:

    mysql.exe --host=... --user=... --password=... -vvf {MySQL database name} < {dump file name}
    I presume the above mysql.exe 'command' can only be executed if you have your own webserver? I'm not sure if i'll be able to this.

    So all i need now is to source the piece of code that takes the dump file and inserts it into the tables, like the feature of php_MyAdmin where u browse for a texfile on your hardrive.

    Cool
    Last edited by philm; Feb 21, 2002 at 04:38.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •