Working around the limitation

no remote access to MySQL

[grandpa]

I'm gradually moving my scripts to localhost and off the web server. For the most part, I can use tables on localhost for all my processing.. for the most part. I have a Top Widgets table on the web server that gets updated from customer orders, and provides my visitors with the current top selling items. For obvious reason this table needs to be maintained on the host server. And, of course, the host does not allow remote access to MySQL without a dedicated IP, which I don't have.

So my question is this: Is there a preferred method for keeping the remote table up to date?

I've toyed around with creating a php script, placing it on the host, and calling it with parms from localhost. This works, with drawbacks. First, I could be updating hundreds of records, so the parm becomes unmanagable. Or I could call the remote script hundreds of times - not a viable option. Maybe I need to learn how to pass an array when I post to the script?

I'm considering a flat file which would be ftp'd to the host, then run a script at the host. Before I get too far into this I'd like to consider any other options.

Thanks

[pontifex]

hi there, if you have Linux locally as well, it would be quite easy, but I am sure it can be done with WSH (windows scripting host) as well.

On linux it would look like:

1.)
mysqldump --add-drop-trable -uUSERNAME -pPASSWORD database TABLEOFTOPS >upfile.sql

=> this dumps your mysqltable into a textfile, the "add-drop-table" adds a DROP command to the beginning, be careful with that!

2.)
scp upfile.sql www.yourserver.com:

=> this would secure copy your file to your server, which only works without password request, if you have a public/private key pair created.

3.)
ssh www.yourserver.com "mysql database </root/upfile.sql"

=> your table gets loaded into the DB

I am sure here is a Windows crack, who could do it in DOS scripting or WSH with the use of WinSCP or simliar progs.

Take a look at WinSCP, I guess it has a command line mode, which would enable you to script on windows basically the same way I described!

Cheers,
P!

[grandpa]

Thanks. I don't really have a problem with transport, there's an ftp connection just a mouse click away. I do have a windoze box, and I looked over the WinSCP docs a bit; it doesn't seem to offer me anything I can't already do.

Table drops or truncation are not allowed on the web site, as in - I won't do it on purpose. But, I may have a solution. I can generate a dynamic script as new orders are processed locally, then upload my completed script - which would simply contain the completed insert or update operations. Thusly:

<?php
$sql = "INSERT INTO table VALUES ('ABC', '123')";
$result = mysql_query($sql);
$sql = "UPDATE table2 SET a = 'B' WHERE c = 'D'";
$result = mysql_query($sql);
?>

Once the script is created, using any name I choose, I can provide a temporary link so it can be executed from localhost.
<a href="http://my.domain.com/anynameichoose.php">Click Here</a>

I can see where this whole process could be made to run very cleanly from localhost, using a table to keep track of file names and script calls. Each newly generated script would contain the code to delete the previous script still on the web server. Have I missed anything?

[mincklerstraat]

I may have missed something here, but I'm sorta thinking, could you save yourself some time and just make a dump of this table on localhost, and then run it on phpmyadmin on your host? You know, dump = 'show create', add the 'if table exists drop' option, it will be dropped / regenerated in no time. Give you time for programming other stuff.

[AlexK]

Do you have ssh login to the server? If so, it is possible to do port-forwarding (secure tunnel) from your Windows box to the server.

I do this from Windows (using PuTTY + plink) to a Linux server:

Windows localhost => server localhost

If you can work with mysql on your home box you can port-forward from Windows localhost:3360 => server localhost:3360 (if I got the port numbers correct!) I do the secure tunnel with rsync (port 873) and http (port 80). Once set up it is a doddle.

[grandpa]

minck, I could. But that doesn't fit into the 'mouse-click' approach that I'm taking. So less programming is probably not an option. (I'll have to dig into js for some other stuff, and I don't look forward to coding a page full of js! )

I've about got it completed. There's already an order batch system, so I'll call my program from there. It will take the order numbers and generate a script to run on the host server, complete with php tags and includes. The only click-less part will be uploading the script via ftp. We already upload the orders that way - that will stop and we'll upload the script instead.
Besides my top widgets I need to make shipping and tracking information available on the web site. The shipping notifications can all be done from the localhost, with the exception of updating tables on the web host. Another version of my program can create a script for that task as well.

Adding a small table to control it - for displaying links, sanity checking - won't add much overhead.

...ssh login to the server

That comes with the $50/mo package. I only have the $5/mo package.

A secure tunnel would be nice, but that goes to the root of the problem, which is, I can't.