Need insight for SMS/Database connectivity

[ronniestamps]

I'm trying to finally implement an idea I've had for years now and I need to be pointed in the right direction. Here's the scenario:

A registered user sends a specific text delimited SMS to my website/server. The server processes this specific text as a database query that matches specific tables within the database. That data is then sent back to the registered user's cell phone in the form of plain text.

What database would be best to use? (The data is currently in MySQL but can be switched if need be)
What programming language would be best suited to handle the SMS, query and response?
Should a dedicated SMS gateway server be setup or use an existing service?

The information within the database is the core of my existing website, so the database needs to be flexible in that it will be used for web and SMS connectivity. I'm not asking anyone to do this for me, I just need a little direction. However, I'd love some help making this a reality ;)

Thanks in advance,
Ronnie

[AbuAaminah]

Hi Ronnie,

Developing an application like your intended one is very easy.

Step 1: Choose your database

You should choose whatever your comfortable with as it won't make a difference. The only thing which you have to ensure is correct indexing to ensure the querying is as fast as possible.

Step 2: Prepare the querying aspect of the script

You need to put together a script which will parse an http query string and query the database. Simply put, you need to be able to feed a dummy message into the script through the URL and then parse the message, validate it and then query the database.

Step 3: Enable sms delivery

Once you've extracted your required data, you need to put the message sending part in place. At this stage, you should still be using dummy data fed through the url.

Step 4: Enable sms reception

Once your application is working with dummy data, you need to get your incoming messages to be forwarded to your script using the same parameter that you've been using so far. Previously, your application was working with dummy data, but now it will be working with true data.

As for how you actual messaging, the fastest, least challenging and (usually) most affordable route is to use an existing service provider.

I hope that helps and am happy to keep helping till your application goes live.

[ronniestamps]

Thank you very much for your response and offer to help, much appreciated. With projects like this, every question answered leads to another question. I'm a graphic designer with a fair amount of experience hacking/modifying code (PHP, Javascript and Perl) but not writing from scratch. So, if you will, here are a few more questions I have:

1. What bridge (term?) do I use to accept the initial SMS?
2. Should I use PHP and MySQL since that is what my current website is based on?
3. Is PHP even robust enough to handle such a task with SMS?

Quick stats to shed more light: the database hovers around 1Gb right now and will increase by about 5 - 20Mb every 3 months or so, for all intensive purposes... infinitely. Initial SMS would contain approximately 40 - 80 characters of text. Response SMS would contain approximately 400 characters of text. Demand, according to my knowledge/experience and the so far fairly limited statistical research I've done, would be at peak for just the state of California (where I'm from) around 500 queries per day between the hours of 8am and 6pm. This number can easily grow to 10,000+ in the same time period. Does this change anything? Might it change which company I choose to host the SMS bridge? Say... a company located in an area with nearly opposite work hours to take advantage of lower traffic, translating into a faster querying?

Thanks again,
Ronnie

[AbuAaminah]

Thank you very much for your response and offer to help

My pleasure.

Should I use PHP and MySQL since that is what my current website is based on?

*Should* you... ideally not. *Can* you... yes you can.

To explain... in an ideal situation, you want to separate the messaging logic from the querying logic. That's to say:

1. Message is received and enters into an inbound buffer (e.g. database).
2. Process X polls the inbound buffer for new messages.
3. Process X queries the main database as per your business logic and creates the response message.
4. Process X adds the response message to an outbound buffer (e.g. database).
5. Process Y queries the outbound buffer for messages waiting to be sent and sends them.

Ideally, your application would be multi-threaded so it could do that lots of times simultaneously.

In practice, however, such a set-up is time consuming, expensive and requires extensive programming skills.

In my opinion though, stick to PHP. Your service seems to be very simple and PHP will do perfectly fine.

What bridge (term?) do I use to accept the initial SMS?

I'm guessing here, that you can get set up to receive SMS. If you're fine with PHP, your best bet is to use an existing sms service provider who will provide you with a selection of connectivity interfaces.

I would recommend one that can offer an HTTP interface mainly because (1) i'm sure you've got experience handling http forms and (2) you can simulate the mobile originated sms by simplying entering dummy messages into your browser's address bar.

Is PHP even robust enough to handle such a task with SMS?

Yes, it is. Honestly speaking, your application is a lot simpler than you think.

The only thing you should really be concerned about is the querying side of things. Run a typical query on the database and see how long it takes? If it takes too long, then look at different ways of optimising it.

If you have any more questions, feel free to ask.

[ronniestamps]

Ok, time spent querying the database is not an issue. A single user would have no problem waiting a few minutes (up to about 5 minutes) for the entire process (I know because in the past I would have been an end user). If at all possible, could you give me some ideas as to which service(s) I should use? I'm not trying to set anything in stone, just have a working model. So, let's establish that I am going to use PHP and MySQL, since that's what is already in place. If it makes a difference, which I doubt it does, I am using Joomla! 1.0.15 as the CMS for the existing site and have my own virtual server. Yes, the scope of this is confidential, but if need be I can and will divulge more specifics via PM. I understand your professional obligation constraints as I have been and am somewhat under similar constraints. I will respect that no matter what. Thank you very much for your help thus far. Also, if you could give me approximate costs (to your knowledge) this project will be even closer to reality... I need to know just how much I am biting off so that I can properly chew it, or at least know if I need others to help.

Much, much gratitude!

[AbuAaminah]

time spent querying the database is not an issue. A single user would have no problem waiting a few minutes (up to about 5 minutes) for the entire process

How long does the query take?

Not only do you have to consider the time which the mobile user waits for his response, but your PHP setup should not time out. As you are on a virtual server, you should be able to increase the allowed processing time using max_execution_time.

If at all possible, could you give me some ideas as to which service(s) I should use?

I will PM you my recommendation.

If it makes a difference, which I doubt it does, I am using Joomla! 1.0.15 as the CMS for the existing site

It won't make a difference as long as you have direct access to your database.

Also, if you could give me approximate costs (to your knowledge)

Most of your service is set-up. The only part left is getting connected and the cost of messaging. These very much depend on which provider you go with. Again, i will PM you my recommendations.

If there's anything else I can help with, just let me know.

[palace]

Hi AbuAaminah

I have been a developer for considerable number of years but in what probably could be termed as for web and the desktop. I am of late finding areas where there is a requirement for access to information through the phone. I was wondering where i can get reading material on the architecture for this kind of SMS /Database scenario.

I am wondering whether there is something special that i need to do my web output/input forms so that the database can be accessed through SMS ?