WeberDev.com PHP and MySQL Code

LOG IN
BEGINNER GUIDESPHP CLASSESCODE SEARCHARTICLES SEARCHPHP FORUMSPHP MANUALPHP FUNCTIONS LISTWEB SITE TEMPLATES
Start typing to search for PHP and MySQL Code Snippets and Articles Search


Submit a code Example / Snippet Join us on FaceBook
Submit a code Example / Snippet Submit Your Code
Search Engine Optimization Monitor SEO Monitor
Web Site UpTime Monitor UpTime Monitor
Your Personal Examples List My Favorite Examples
Your Personal Articles List My Favorite Articles
Edit Account Info Update Your Profile
PHP Code Search
Web Development Forums
Learn MySQL Playing Trivia
PHPBB2 Templates
Web Development Resources
Web Development Content
PHPClasses
PHP Editor
PHP Jobs
Vision.To Design
Ajax Tutorials
PHP Programming Help
PHP/MySQL Programming
Webmaster Resources
Webmaster Forum
XML meta language
website builder



Go Back Add a Comment Send this Article to a friend Add this Article to your personal favoritest for easy future access to your favorite Code Examples and Articles. Submit a code example Print this code example.
BACK ADD A COMMENT SEND TO A FRIEND ADD TO MY FAVORITES SUBMIT AN ARTICLE PRINT
Title : Simple Connection to Microsoft SQL Server with PHP
Categories : PHP, MSSql, Databases Picture not available
Meloni Julie
Date : 2000-01-16
Grade : 5 of 5 (graded 1 times)
Viewed : 31393
Search : More Articles by Meloni Julie
Action : Grade This Article
Tools : My Favotite Articles


Submit your own code examples 
 


There are numerous built-in functions for PHP -> Microsoft SQL Server connectivity,
documented in detail in the PHP Manual. However, you only need a few of these
functions in order to make a simple connection and select some data:



mssql_connect - opens a connection to Microsoft SQL Server; requires a server name, username and password.




mssql_db_select - selects a database on the Microsoft SQL Server.



mssql_query - issues the SQL statement.





mssql_fetch_array - puts a SQL statement result row in an array.



mssql_free_result - frees the resources in use by the current connection.



mssql_close - closes the current connection.



Just for argument's sake, let's pretend that Microsoft SQL Server is already installed on your system, and you have a valid username and password for an existing database. Let's also assume that you've created a table on that database, called COFFEE_INVENTORY. The COFFEE_INVENTORY table has three columns: COFFEE_NAME, ROAST_TYPE and QUANTITY.



The rows in the COFFEE_INVENTORY table could be populated with data such as:



French Roast,dark,18



Kenya,medium,6



Ethiopian Harrar,medium,35



Sumatra,dark,8



Columbian,light,12



Now, let's do some PHP. Before you begin, you must know the name of the server on which the database resides, and have a valid username and password for that server. Then, start your PHP code by creating a connection variable:


<?
$connection
= mssql_connect("servername","username","password");


Test that a connection was established and if it wasn't, print an error message and exit the program:


if (!$connection) {
echo "Couldn't make a connection!";
exit;
}


NOTE: If you have problems making a connection, try using an empty string for the password ("") when creating the connection variable.






If you have made it through the connection test, the next steps are to select the database and create the SQL statement. Suppose that the COFFEE_INVENTORY table exists in a Microsoft SQL Server database called "myDB". First, create a database variable, such as:


$db = mssql_select_db("myDB", $connection);


Next, test that the database was selected. If it wasn't, print an error message and exit the program:


if (!$db) {
echo "Couldn't select database!";
exit;
}


Up to this point, you've told PHP to connect to a server and select a database. If you've made it this far, you can issue a SQL statement and hopefully see some results! Using the COFFEE_INVENTORY table, suppose you want to view your inventory, including the name of the coffee and the roast type, with the highest number of bags listed first. Create a variable that holds your SQL statement:


$sql = "SELECT COFFEE_NAME, ROAST_TYPE, QUANTITY
FROM COFFEE_INVENTORY
ORDER BY QUANTITY DESC";



Next, create a variable to hold the result of the query, carried out by the mssql_query function. The mssql_query function takes two arguments: the connection and SQL statement variables you've previously created.


$sql_result = mssql_query($sql,$connection);



To format the results currently held in $sql_result, first separate the results by row, using the mssql_fetch_array function:


while ($row = mssql_fetch_array($sql_result)) {
// more code here
}


The while loop will create an array called $row for each record in the result set. To get the individual elements of the record (COFFEE_NAME, ROAST_TYPE, QUANTITY), create specific variables:


$coffee_name = $row["COFFEE_NAME"];
$roast_type = $row["ROAST_TYPE"];
$quantity = $row["QUANTITY"];


You'll probably want to print the results in a simple HTML table. Step back and place this statement before the while loop begins, to open the table tag and create the row headings:


echo "<TABLE BORDER=1>";
echo "<TR><TH>Coffee Name&lt;/TH><TH>Roast Type&lt;/TH><TH>Quantity&lt;/TH>";


After defining the variables within the while loop, print them in table format:


echo "<TR><TD>$coffee_name&lt;/TD><TD>$roast_type&lt;/TD><TD>$quantity&lt;/TD>&lt;/TR>";



The new while loop now looks like this:


while ($row = mssql_fetch_array($sql_result)) {
$coffee_name = $row["COFFEE_NAME"];
$roast_type = $row["ROAST_TYPE"];
$quantity = $row["QUANTITY"];
echo "<TR><TD>$coffee_name&lt;/TD><TD>$roast_type&lt;/TD><TD>$quantity&lt;/TD>&lt;/TR>";
}



After the while loop, close the HTML table:


echo "&lt;/TABLE>";



Finally, you'll want to free up the resources used to perform the query, and close the database connection. Failing to do so could cause memory leaks and other nasty resource-hogging things to occur.


mssql_free_result($sql_result);
mssql_close($connection);

?>



The full script to perform a simple connection and data selection from Microsoft SQL Server could look something like this:


<?php

// create connection
$connection = mssql_connect("servername","username","password");

// test connection
if (!$connection) {
echo
"Couldn't make a connection!";
exit;
}

// select database
$db = mssql_select_db("myDB", $connection);

// test selection
if (!$db) {
echo
"Couldn't select database!";
exit;
}

// create SQL statement
$sql = "SELECT COFFEE_NAME, ROAST_TYPE, QUANTITY
FROM COFFEE_INVENTORY
ORDER BY QUANTITY DESC"
;

// execute SQL query and get result
$sql_result = mssql_query($sql,$connection);

// start results formatting
echo "<TABLE BORDER=1>";
echo
"<TR><TH>Coffee Name&lt;/TH><TH>Roast Type&lt;/TH><TH>Quantity&lt;/TH>";

// format results by row
while ($row = mssql_fetch_array($sql_result)) {
$coffee_name = $row["COFFEE_NAME"];
$roast_type = $row["ROAST_TYPE"];
$quantity = $row["QUANTITY"];
echo
"<TR><TD>$coffee_name&lt;/TD><TD>$roast_type&lt;/TD><TD>$quantity&lt;/TD>&lt;/TR>";
}

echo
"&lt;/TABLE>";

// free resources and close connection
mssql_free_result($sql_result);
mssql_close($connection);

?>


Please see the PHP Manual for additional Microsoft SQL Server functions, and try using your own tables and SQL statements instead of the examples above.









Simple Connection to Sybase with PHP
Categories : PHP, Sybase, Databases
Creating an IE-Only Database Driven Menu System With PHP, MySQL and DHTML
Categories : PHP, MySQL, Databases, DHTML
Simple Connection to Informix with PHP
Categories : PHP, Informix, Databases
Simple ODBC Connections with PHP
Categories : PHP, ODBC, Databases
PHP, MySQL and Authentication 101
Categories : PHP, Databases, MySQL, Authentication
Building A Persistent Shopping Cart With PHP and MySQL
Categories : PHP, MySQL, Databases, Ecommerce
Case Study: Handling MySQL Growth With a PHP Class
Categories : Databases, MySQL, PHP
Abstracting Oracle Connectivity with PHP and OCI8
Categories : PHP, Databases, OCI8, Oracle
Practical Date and Time examples with PHP and MySQL
Categories : Databases, MySQL, PHP, Date/time
Miles To Go Before I Sleep...
Categories : PHP, Calendar, Databases, MySQL
Alternating row colors with PHP and mySQL
Categories : PHP, Databases, MySQL, HTML and PHP
Multicolumn Output from a Database with PHP
Categories : PHP, Databases, HTML and PHP, MySQL
PHP and PostgreSQL
Categories : PHP, Databases, PostgreSQL
Simple Connection to MySQL with PHP
Categories : PHP, MySQL, Databases
Date Arithmetic With MySQL
Categories : PHP, Databases, MySQL, Date Time
Ilir Pruthi wrote : 311
Are there any resources for MS SQL on the web? I know
that MS SQL *IS NOT* free like MySQL, so I never found
any websites that teach MS SQL syntax, etc.

After all, MS SQL must be different from MySQL, right?

Thanks,
I.