Connect to MSSQL (Microsoft) database - an example

spambox · **Joined:** Mon May 15, 2006 2:52 pm **Posts:** 27

Hi,

the past time a read various topics, people asking for howto query a mssql database. Assuming you have installed php5-sybase on your webserver, hereby a short example to show how I did. If someone else knows a better / cleaner / nicer etc. solution, shout!

Code:

                
function dothetrick() {
                $db=mssql_connect("IP ADDRESS","USERNAME","PASSWORD");
                $d=mssql_select_db("DATABASE NAME", $db);
                $sql = "SELECT name,balance FROM tablename";
                $res = mssql_query($sql);
                $i = 0;
                // Convert the results to an object, so you can access your results
                // the same way like you would have queried a mysql from within Joomla
                while ($record = mssql_fetch_array($res)) {
                                $tmp = & new stdClass;
                                foreach ($record as $key => $value) {
                                $tmp->$key = & $record[$key];
                }
                                $array[$i] = $tmp;
                                $i++;
                }
                mssql_close();
                return $array;
}

If someone else has a better solution, just let me know!

paquito684 · **Joined:** Wed Jan 24, 2007 4:15 pm **Posts:** 6

I'm with php and joomla for 6 days, and i need connect Joomla with a MS sql server 7.0

Do you think i can use your code?

Please, i dont know what have i do with the code, help me.

I must to do a file.php or add the code to any file.php?

Thanx

lagibosen · **Joined:** Sat Jun 28, 2008 11:43 pm **Posts:** 3

thanks for sharing your script

spambox · **Joined:** Mon May 15, 2006 2:52 pm **Posts:** 27

Over a year ago, someone else did put some effort in writing a Microsoft SQL (MSSQL) layer for Joomla. Unfortunately, I don't remember his name anymore, but credits go to this fellow!

In the attached mssql.php I rewrote some parts of this work, so it will work with Joomla 1.5.3. Please feel free to use, or even better, to write your own enhancements! Extract & place this file in /libraries/joomla/database/database/ as mssql.php.

How to use? Very simple, just like your normal Joomla queries!
Please note: mssql support for php has to be installed on your server!

Code:

$option['driver'] = 'mssql';
$option['host'] = 'hostname';
$option['user'] = 'username';
$option['password'] = 'password';
$option['database'] = 'mssqldatabasename';
$option['prefix'] = '';

$db = &JDatabase::getInstance($option);

$db->setQuery($query);

$results = $db->loadObjectList();

Even 'LIMIT 10,20' is supported, the query will be rewritten accordingly.

jackbauerr · **Joined:** Thu Jul 03, 2008 8:47 am **Posts:** 1

errrrrr... Hi !
I am currently on a Joomla! project which should use a MSSQL Server.

I extracted your mssql.php into the correct path but where should I write the code you wrote in your post?

Thanks for your help!

jdschuitemaker · **Joined:** Tue Jul 15, 2008 2:53 pm **Posts:** 2

I would be curious to know where to put the code too...

spambox · **Joined:** Mon May 15, 2006 2:52 pm **Posts:** 27

The file I attached has not been made to let Joomla on itself run on a MSSQL backend. The file can be used if you develop your own component, plugin or what so ever wich needs to retrieve information from MSSQL.

So, if you develop your own component and you run a query on a MySQL database, you would normally do something like:
$db = & JFactory::getDBO();
$db->setQuery($query);
$results = $db->loadObjectList();

But if you want to query a MSSQL database, you can use the code as mentioned a few topics above. For more information on how to query a database in general, please see the Hello World 2 example on http://dev.joomla.org/component/option, ... omponents/

Forum rules: Please click here to view the forum rules

Connect to MSSQL (Microsoft) database - an example

Who is online