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:
$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.
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.
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.
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:
$dsn = array(
'phptype' => "mssql",
'hostspec' => "localhost",
'database' => "JoelDB",
'username' => "anonymous",
'password' => "password"
$dbo = DB::connect($dsn);
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";
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 08:19 AM.|