Move local mssql database to webhosted MYSQL

I'm a dektop developer and don't know much about how webhosting work... So please help me here.

currently my app works with MSSQL database which is installed locally on the same machine. Now I need to go wider and let multiple apps work with the same database over the Internet. I have a webhosting with CPanel in it and MyS开发者_StackOverflow社区QL database.

Please tell me how I can access tables in that MySQL database from my another computers? Select and update records in that table. Do I have to implement some functionality using PHP to make such requests?

Please advise..

Exporting raw MS-SQL dumps will probably not work if you try to import into mySQL because there are some differences in syntax. There are commercial programs available that will help you migrate data, otherwise it might be better to code a PHP program to export from MSSQL to mySQL.

But your question sounds more like you're asking how to access databases remotely. . PHP can do this natively. When you create your link to the database you specify the host. For example: $databaseConnection = new mysqli('username','password','host_ip_address');

If you're coming from MSSQL you may hit some snares.

To allow remote connections on MS-SQL you use the "surface area" configuration tool. You'll probably find that your mySQL database server is already configured to allow remote connections, if not you'll have to take that up with your host (sounds like you don't have your own box).

The next trick is to remember that usernames in mySQL look like this: 'user'@'host'.

cPanel hosting usually has PHPmyAdmin installed. Open it up and look at the user table in the mysql database (the actual database named mysql running on the MySQL server).

You'll see a host column. So if your 'root' user is only set to 'localhost' you won't be able to login as that user from another machine. The wildcard symbol is %. You can read this up here - http://dev.mysql.com/doc/refman/5.5/en/adding-users.html

Of course the same rules apply to mySQL when it comes to users. Don't use your root account to access sub-databases, rather assign new users which only have the required permissions. Also consider using non-predictable usernames. That doesn't really answer your question (sorry) but it's worth mentioning while you're looking at the user table.

So to finally answer your question: 1) login to cPanel and create your database 'foo'

2) Run SQL command:

GRANT ALL PRIVILEGES ON foo.* TO 'secretuser'@'%' IDENTIFIED BY 'longpassword'

Then PHP code would look something like this:

$host = 'ipaddress or hostname';
$mysqli = new mysqli('secretuser','longpassword',$host,'foo');
// look in php.net for error handling
$query = "SELECT * FROM tablename LIMIT 0,10";
$result = $mysqli->query($query);

The reason I put a limit there is because you're probably used to "SELECT TOP 10"

You would have to export the data on your computer into a raw SQL file, and then using a the PHPMyAdmin control panel you could import it. Assuming you are also using PHPMyAdmin on your own machine there is an 'Export' tab along the top. If you click on that you will have the option to export all your tables to a .sql file.

From there you then need to access PHPMyAdmin via CPanel. Once in you can then hit the 'Import' tab and upload your file to get all the data into the database. To interact with the database online you would need to use the inbuilt functions in PHP - the MySQL Reference on PHP.net is very useful. you would then be able to access the database from any computer using the PHPMyAdmin control panel.