LinuxQuestions.org
Support LQ: Use code LQ3 and save $3 on Domain Registration
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 11-21-2006, 11:02 AM   #1
joelhop
Member
 
Registered: Mar 2004
Location: Pennsylvania::USA
Distribution: Fedora Core 6
Posts: 100

Rep: Reputation: 15
Question PHP and ms-SQL


I've got a question about using PHP with ms-SQL.

I'm trying to setup a basic php script that queries an ms-SQL database and stores the results of that query into an array.

Here is what I have so far:

<?php
$myServer = "localhost";
$myUser = "your_name";
$myPass = "your_password";
$myDB = "Northwind";

//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
or die("Couldn't connect to SQL Server on $myServer");

//select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
or die("Couldn't open database $myDB");

//declare the SQL statement that will query the database
$query = "SELECT ContactName, ContactTitle, Phone ";
$query .= "FROM Customers ";
$query .= "WHERE Country='UK'";

//execute the SQL query and return records
$result = mssql_query($query);

//calculate number of rows
$numRows = mssql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";



Does $result contain the entire result set as an Array? When I try var_dump($result) all that I see is: resource(2) of type (mssql result)

I want this to behave as a regular array, when I do a var_dump() on an array I build it just dumps the results to screen. How can I make a msSQL result set do the same?

I do not wish to use the mssql_fetch_array() function to access the result set each time, I know you can do:

while($row = mssql_fetch_array($result))
{
echo "<li>" . $row["id"] . $row["name"] . $row["year"] . "</li>";
}


Is there another way?

I would like to have the result array stored in a variable which I can pass around between other code objects.
 
Old 11-22-2006, 04:44 AM   #2
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
The result of a query is the resource identifier. So no, it does not contain the array.
With the *_fetch_array, *_fetch_row and *_fetch_object you tell the *sql server to give you one row of the result set.
So, as far as I know, you have to use the above function to iterate through the resulting rows.

If you write a function that runs the query, you can let it return an array of the result.

Something like
Code:
function get_data($query)
{
    $rc=mssql_query($query);
    if(!$rc)
        return false;

    if(!mssql_num_rows($rc))
        return false;

    while($result=mssql_fetch_array($rc))
    {
        $data[$result['id']]=$result;
    }

    return $data;

}
This code returns an array of records that matched the query. You can move data around as you like.
It's universal if all your tables have a column 'id'. My tables usually don't as I get confused when I start joining tables, so I have something like id_table1, id_table2 etc. So I write a function like this for every table.

Please note that I use result in a different way than you do.

PS code not tested, just to give you the basic idea.

Last edited by Wim Sturkenboom; 11-22-2006 at 04:48 AM.
 
Old 11-22-2006, 08:45 AM   #3
joelhop
Member
 
Registered: Mar 2004
Location: Pennsylvania::USA
Distribution: Fedora Core 6
Posts: 100

Original Poster
Rep: Reputation: 15
That is excellent, I can definitely see how you could construct custom arrays in that manner. While I was further researching this issue I discovered the Perl DB library. I installed Perl and included a reference to it in the php.ini file. It then gave me the level of DB abstraction I was looking for. It returns a 2 dimensional array of my query as an object $dbo:

<?php
require_once 'DB.php';



$dsn = array(
'phptype' => "mssql",
'hostspec' => "localhost",
'database' => "JoelDB",
'username' => "anonymous",
'password' => "password"
);

$dbo = DB::connect($dsn);

if (PEAR::isError($dbo))
{
echo "An error occurred while trying to connect to the database server.<br>\n";
echo "Error message: " . $dbo->getMessage() . "<br>\n";
echo "A more detailed error description: " . $dbo->getDebugInfo() . "<br>\n";
}
else
{
var_dump($dbo);
}

?>


This same bit of code with some further modification would allow me to pass in any db name and still output a db object the same way for each, also a central location for specifying db type, such as mssql mysql and so on.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Looking for a PHP and SQL tutorial runnerpaul Programming 3 09-13-2006 05:39 AM
Php, Sql kotoisin Fedora 3 11-30-2004 05:18 PM
PHP SQL PHProjekt crossfire_mania Linux - Software 1 01-28-2004 01:43 PM
php/SQL shaggystyle Linux - General 1 12-23-2003 09:29 AM
PHP & my SQL Z28kid Linux - General 2 04-20-2003 11:01 AM


All times are GMT -5. The time now is 01:26 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration