Solved

MS SQL import script

Posted on 2013-01-25
4
179 Views
Last Modified: 2013-01-31
Hi,

I was wondering if someone could assit me with this problem -

I have an ERP system that controls the stock levels of my products and want to update this and pricing information so that it matches my website's database.

From my server I have remote access to both databases. The ERP system uses MS SQL but my website uses MYSQL 5.0

My stock items will have the same ID in both databases.
 
I use the following SQL statement on my ERP system -

select
ID,price
from items
where status = 'a';

I basically want to update the results to the same fields on my websites database by scheduling a script to run on an hourly schedule.

The reason why I need help is because i've never really done this thing before with PHP and really not sure where to start!

Any help is appreciated!
0
Comment
Question by:Dan560
4 Comments
 
LVL 38

Accepted Solution

by:
Aaron Tomosky earned 125 total points
ID: 38821390
start with adodb
http://adodb.sourceforge.net/

make connections to both databases
you can return an associative array of results, from the first, then insert/update them to the second.

Obviosuly it's more complex than this but thats the basic idea.
0
 
LVL 77

Assisted Solution

by:arnold
arnold earned 125 total points
ID: 38821416
Export data as CSV, the use bcp utility to import the data. Or the reverse not clear whether you have/maintain a reference.

Is PHP a requirement? Depending on where you are running the script, you may need to install freetds which provides the option for a Linux system to access ms sql.

The process would open a connection to each DB server and then perform a select from one and then insert/update not the other.
0
 
LVL 51

Assisted Solution

by:ahoffmann
ahoffmann earned 125 total points
ID: 38821880
I'd also suggest to avoid PHP for that
simply write two scripts: one for export mssql to a csv file ans one for importing the csv file into mysql, then schedule these scripts

from a security point of view: never ever connect the web server to your internal data, never ...
0
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 125 total points
ID: 38822399
Agree with the comments that suggest to avoid PHP.  It's not necessary, and you would add a long and steep learning curve for yourself if you're not already well-versed in PHP.

In unrelated news, if you want to learn PHP, this book is a pretty good place to start.
http://www.sitepoint.com/books/phpmysql5/
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question