LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (http://www.linuxquestions.org/questions/programming-9/)
-   -   PHP and ms-SQL (http://www.linuxquestions.org/questions/programming-9/php-and-ms-sql-503606/)

joelhop 11-21-2006 11:02 AM

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.

Wim Sturkenboom 11-22-2006 04:44 AM

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.

joelhop 11-22-2006 08:45 AM

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.


All times are GMT -5. The time now is 10:17 PM.