Stuck On The Candy Man. A Database Dilema.

[chop]

This is a real problem that I and my client are struggling with in thinking about the future growth of his company.

 

Please let me know if this, a non-programming issue, isn't appropriate for this forum.

 

I'll keep is as short as possible.

 

XYZ company is involved in the facilitating business alliances and they publish their own quarterly magazine on the subject. Their current website is split between two different hosts. The part they take care of themselves is strictly informational and for non-members. The other part, which requires database support, is for its members and is hosted by the i-gotcha.com (made up) company who's business it is to do exactly that. Apparently, when XYZ first started the site, they didn't want to deal with the db aspect of designing a website and farmed it out.

 

Now they want to expand operations by selling their valuable articles, editorials etc., that they have compiled over the years, on their own "XYZ-media" website. A pricing structure will be designed for members, non-members and according to the type of distribution rights they want.

 

The problem, of course, is that they don't really control their own membership information and so how does a member log into XYZ-media to make a purchase?

 

A temporary solution that one of the managers came up with is that members could log into i-gotcha.com and receive an authorization code (which would include their member "level" which has an effect on what they would pay to purchase something) and then log into XYZ-media with that code.

 

Sounds a little clunky to me.

 

Some talk of a web-service was mentioned but the $1000/mo price tag quote from i-gotcha put a halt to that.

 

I'm not an expert in this type of thing but my thought is that XYZ needs to break loose from i-gotcha as soon as possible. Otherwise, if they want to continue to expand in their services it's never going to be pretty.

 

However, I might be missing something. Is it possible to say, query a database on one URL from another URL just to get 3-4 pieces of data that might be necessary. Even if that's possible, seems like i-gotcha would be taking a security risk ?

 

My thoughts are either a web service ($$) or ditch i-Gotcha asap.

 

Again, I know this is a non-programming question and maybe not appropriate here but it's still an interesting db problem that's probably not all that rare.

 

Thanks for your valuable input.

 

 

[margaux]

I believe it is possible to query a database via a remote host using ssh2_tunnel but you need to ensure you have the correct permissions.

 

My opinion on the larger question you ask is that the company would want to change its web hosting arrangements to ensure it has full control of its assets (files, databases etc)  and is able to change the arrangements as its hosting requirements change without too much hassle. It may be that your client has to go through some short term pain to get to a position that will be more suitable for the longer term.

[HartleySan]

How much access does your company have to the DB (and files stored on the i-gotcha.com server)?

If you can make a backup of all the data, do exactly that and make a local copy, then you can free yourself.

 

If you don't have that level of access, maybe run a basic query like the following on all the tables, output the results to text files, and use that to restructure the data in a local DB that you do control:

SELECT * FROM one-table-at-a-time;

Also, you can connect to external DBs, but it's a bit tricky.

A recommendation that Larry made once for a similar question I asked was to create a PHP interface that takes a query as input and returns results from the DB as output. Connect to that PHP script to run queries. A bit clunky, yes, but an option.

[chop]

We have no direct access to make our own queries from the i-gotcha.com as far as I know. They are simply performing services to members like selling things (mostly business literature, articles), letting them see other members' names, emails, addresses, keeping track of things they buy and so on.

 

I don't know exactly what the agreement is between XYZ and i-gotcha but it could be that i-gotcha might supply XYZ with a CSV file of the table data if they wanted out.

 

XYZ does want to get out from i-gotcha eventually but not until they have something of their own to fall back on. Meanwhile XYZ wants to start XYZ-media on their own server so they can begin to store written and video media that their members will buy. Of course to sell the stuff, they need to know who is a member and who isn't. But that's on i-gotcha's database.

 

An easy solution would be to query i-gotcha's db just to find out if the person logging into XYZ-media is a member and, if so, what level of a member (the pricing structure needs to know that).

 

In time,  XYZ-media can take over more and more of what  i-gotcha does until i-gotch doesn't gotcha anymore and isn't needed. But that will take time, trying to break loose.

 

BTW I'm not sure what you mean in your last paragraph. Can you explain it in terms of the interaction of the XYZ-media and the i-gotcha databases. (XYZ-media needs to know if this person logging in is a member - maybe a query to the email address)

[HartleySan]

In order to better explain what I mean, I need to better understand how the i-gotcha DB is queried in the first place.

Could you please give a concrete example of a query you might run on the i-gotcha DB and how the results are returned?

Thanks.

[chop]

Sorry for being unclear.

The thing is I can't query the i-gotcha db. They are a company that hosts part of or all of a website for people who don't want to deal with making their own db system. That's the problem! It's their db and they don't have to let me have access. So what I am trying to find out is IF i-gotcha  allowed me to query their db to see if John Doe (who is now logging into XYZ-media) is actually a member in XYZ. Is it possible (with the right permissions set) to query a db on one URL from a php script located on a separate URL .

 

I hope this helps.

[HartleySan]

Thanks for that explanation, but I'm still not sure about some things.

Mainly, at what level do you interact with their DB? At some point, there has to be a DB interface that you're dealing with.

Unless they code all the back-end PHP scripts as well based on your specifications (in other words, they're a web development company), at some point, they have to provide you with some sort of interface to allow you to write your own queries and interact with the DB.

Am I wrong?

[chop]

The only thing they provide their customers with is some reports on their data. Yes, that's a query technically but it's run off their own site. It's not the same as querying from your own php script from your own separate URL. I can't query their database from my own script on my own server.

[HartleySan]

Okay.

So let's say, for example, that you wanted to set up a search feature on your website.

How would you go about handling the SELECT query, and what kind of results would you get back for displaying the matching records in the DB?

I'm still confused about where the cutoff is between you and this company.

[chop]

I would have them type in their email and password on the XYZ-media site ( the same email-password combo they use on i-gotcha - remember i-gotcha has the member database for XYZ), then I would want to query i-gotcha's database to see if this person really is a member of XYZ. I would also want to know what level user they are (say 1-3). Assuming that they are an actual member, and they are a level, say 3, I could sell them certain products for prices set for level 3 members. If they are a lower level member or not a member at all, the prices would be different.

 

So the question still stands, can I query someone else's database? How is this done?

[HartleySan]

chop, I appreciate the explanations, but you're still not answering my questions.

What I'm trying to suggest is that you do whatever you have to in order to free yourself from this monster company. And in order to do that, you need a way of automating the process of extracting your data from their DB.

At some point in the process of interacting with their DB, there must be some way you are able to customize what data you have access to and how it is accessed.

As such, there's no reason you can't simply extract all your data from their DB, reformat it as necessary, and then reinsert it into a DB of your choosing, thus giving you control of your data again and the ability to free yourself from this company.

 

The process you'd use to perform a query on an external DB is more or less the same as the process of just extracting all the data and being done with the company altogether, which is why I'm trying to help you do that.

[chop]

Thanks for your patience HS.

 

Maybe I'm having a hard answering your questions because I've never used whatever admin. programs that i-gotcha.com has set up for XYZ. So when you ask me something like: "At some point in the process of interacting with their DB, there must be some way you are able to customize what data you have access to and how it is accessed." It's not easy to answer because I have never done this. I'm really playing this one from an outside position.

 

I suppose if XYZ wanted to bail altogether then maybe i-gotcha would just hand over a few csv files that could be used to populate tables on the new XYZ-media site.

 

Some why doesn't XYZ  get the files, put up a database and forget about i-gotcha?

 

Probably because i-gotcha performs a few sophisticated data functions that XYZ doesn't want to lose and don't want to try do replicate theirself.

 

So, in able to set  up a simple sales site for them on the new XYZ server (XYZ-media) , I would need to access certain information on the i-gotcha.com website (member name, number etc) before I proceed with the sale. I have never (within a php scrip) queried  a remote (someone else's) database to get information that I needed to query a localhost db. So I was just wandering about the procedure ans permissions that I'd have to set up with i-gotch.

 

I apologize for the confusion, I don't know how else to say it.

 

P.S. Since this was first posted, XYZ is no longer considering dumping i-gotcha outright because of the dependency (above) that I have already mentioned.

[HartleySan]

All right.

Well, the short answer to querying an external DB is that you should use cURL.

 

Basically, you use cURL to query a PHP script on the i-gotcha server, and then from the PHP script on the i-gotcha server you query the DB (however that is done). Then, upon getting a response from the DB, you have to organize that data however you see fit, and then use another cURL request to send that data back to the original PHP script on your server, and then reorganize the data again as necessary.

 

Just out of curiosity, what are the exact functions that your company is dependent on i-gotcha for? I have a feeling that they are things that are already available for free or can be very easy replicated by any web hosting provider.

[chop]

They do certain types of data analysis on the information they collect from members though I'm not sure how critical it is to them really. So it's not just data collection and retrieving. Anyway, I thought I had XYZ convinced that they could (and should perhaps) let go of i-gotcha if they want to continue to expand in the way they are planning. Otherwise the same problems will always come back to haunt them. Plus, the i-gotcha site is quite ugly and not well integrated with XYZ's current site.

 

Anyway, thank you. That was exactly what I was looking for. I knew it could be done but didn't know where to start looking. I will find out more about cURL.

 

Margaux, on the second post said:

 

I believe it is possible to query a database via a remote host using ssh2_tunnel but you need to ensure you have the correct permissions.

Is this at all related to cURL?

 

Not critical, just wandering. I appreciate you tolerating me all this time.

[HartleySan]

No, ssh2_tunnel is related to sockets. Actually, a socket might be the more elegant solution, but it might not be possible to implement, and it will likely be harder to code than using cURL if you're not used to using sockets.

[chop]

Okay, we won't go that route.

Consider this case closed (now that we're HOT!)

 

Thanks again for your assistance HS

[HartleySan]

Okay. Glad you got the answers you were looking for.

If you have any other questions related to cURL, etc., just ask.

 

Also, just my advice from what I've read in this topic, but while I think using cURL is a decent temporary solution, I would highly recommend helping your client remove themselves from the clutches of i-gotcha ASAP.