Pulling data from a MS SQL Server dB into an HTML form?

[BFP]

Hi.

I am using a CMS-based website that was built on top of Apache, MySQL, and PHP (i.e. Drupal) and I would like to build a bunch of form-based pages that integrate with a pre-existing Microsoft SQL Server database that contains customer data.

Thus, a typical scenario might be as follows: user wishes to retrieve customer data, so they start by using a search form to search by company name. The SQL server is queried and returns a list of company names. They then select the company and the relevant company info is presented. They then change a variable and save and the record for that company is updated.

My questions are as follows:

1) Do I need to have my host add a PHP module to support Microsoft SQL queries?
2) What would the general pseudo code for the example given above look like?
3) Can the type of interaction described above be performed with standard SQL queries?
4) Given that my site and the SQL server are in different physical locations and that minimal data will be passed, will my site suffer from any noticeable slowdowns? If yes, should I look at methods to pre-fetch or cache data?

I really appreciate a shove in the right direction!

It goes without saying that any code snippets, tutorials, web sites, etc. that would help me in this endeavor would be MOST appreciated.

Thanks in advanced,

BFP

 

[vacunita]

First you need to make sure the mssql library is installed on your PHP server.

http://www.php.net/manual/en/book.mssql.php

2. Its going to be very long to detail here.

Basically its

1. Make search form, take submitted value and run query, display results in another form for user to choose from.
2. Take selected value form second form, run yet another query and get Data to be displayed based on selected value.
3. Make a third form to display the data in editable fields. Get all values and run an insert or update query to make the changes in DB.

This of course all needs to be interspersed with validations and confirmations.


3 Short answer Yes. Its not difficult but it is involved, and will take a while to get all parts working.

4. By the amounts of data being transferred between your server and the DB server probably not noticeably. But you may want keep the data requested to a minimal. For example for the query that will display a list of Companies based on the user's search you would only need to request the name of the company and maybe a unique ID to run the following query, no need to query the entire set of fields if they aren't going to be used. You can query the complete set of fields when the final selection is made.


The PHP manual should be your first step when starting a PHP project.

If you plan on doing this in only one PHP file, as a guide I would code the basic skeleton for the interaction, and then once the basic logistic flow is established you can start filling in the sections with all the retrieval code and what not.

You just need to mock it up with links if you want to just to get the flow right, and the different form submissions correctly identified.

As a mock up:

<?PHP
<form action="processpage.php" method=POST>
<input type=text name="search term">
<input type=submit>

if(search form submitted){
  run query and display results here.
}


if(company_selection submitted)
{
  run query and get company info and display
}

if(company info update submitted)
{
  run update query here
}


?>

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[BFP]

Vacunita,

Thank you very much for your detailed response!

This was exactly what I needed to get started.

Regards,

BFP

 

[vacunita]

Glad I could help. If you get stuck or have any problems don't hesitate to come back and ask.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.