Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS SQL import script

Posted on 2013-01-25
4
Medium Priority
?
189 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 39

Accepted Solution

by:
Aaron Tomosky earned 500 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 80

Assisted Solution

by:arnold
arnold earned 500 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 500 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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

885 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