PHP and MS Sql

fluiduk

fluiduk

fluiduk

Associate
Joined
13 Nov 2003
Posts
1,567
Location
Manchester
Hi All

I have an app in php that we use to generate our stock/product lists.

We now have a new website in asp, and I need to insert the data from the PHP/Mysql system into an MS Sql database table.

Is there a way to do this from PHP.

I was thinking of a simple loop that reads the data from mysql and writes it to the MS Sql. Also, it needs to overwrite any existing data, however I don't want to empty the table first, as this would result in down time while the data was copying

Does anyone have any tips on the best way to do this?

Thanks
Aaron
 
You could generate the inserts via phpmyadmin and just run the generated insert scripts against the tables in MSSQL.

Failing that, just export them as .csv files from mysql and use DTS/BCP to get them into MSSQL.

I wouldn't even consider writing code for doing something so simple.

:)
 
Mr^B said:
I wouldn't even consider writing code for doing something so simple.

:)
Click to expand...

Lol, trying to automate it all, so don't have to write csvs and import them, as it will be done 4 times a day
 
fluiduk said:
Lol, trying to automate it all, so don't have to write csvs and import them, as it will be done 4 times a day
Click to expand...

I'd still use csv files and DTS, for ease of automation - as automating webpages is a PITA.

If it happens or regular intervals then use Scheduler/CRON to run the csv export and then SQL Scheduler to run the DTS to import them all.

read-write-read-write-read-write to import/export data, especially a lot of data, and extra-especially (new word) a lot of data often, is just nasty.

:)
 
Also.. We need to be able to update the master list table in ms sql upto 4 times a day.

Now each time we update the master list needs to be cleared and all the data reenterd, as some products will have dissapeared and new ones will come in.

Whats the best way of doing this without taking the site down?

Thanks
A
 
fluiduk said:
Ok Mr B,

Will give that a go, not that i have a clue how the DTS stuff works lol

Aaron
Click to expand...

DTS is pretty simple; specify input file name/location and its target table and there's a wizard that does everything else for you. The scheduler is even easier, just pick the schedule for the package and SQL Server Agent does the rest for you.

As for the second part, I wouldn't clear down the table, I would run a series of 3 queries on it, based on a new version of the data; updating, inserting and deleting records as appropriate. Deleting and re-inserting ALL the records whether they have changed or not is just nasty.

:)
 
Ok cool, will do that.

When you say 3 sets of queries, how do you tell it which to delete the old ones? What would you compare against. Would you insert the new data into a staging table, and then use that to perform comparisons?
 
fluiduk said:
Ok cool, will do that.

When you say 3 sets of queries, how do you tell it which to delete the old ones? What would you compare against. Would you insert the new data into a staging table, and then use that to perform comparisons?
Click to expand...

Yup.

:)
 
Heh, no problem.

You can end up doing quite complex regular imports/datafeeds with DTS - SQL 2005 of course lets you import your own .Net assemblies and objects to use as part of the import process - with SQL 2000 you are stuck with VBScript (:eek:) but it's still reasonably powerful, allowing SQL to control things like copying/archiving/renaming the input file, etc - so it ends up being a complete data management system as well as a database.
 
You must log in or register to reply here.
Back
Top Bottom