I am porting over a database from MySQL to MSSQL, the server is running IIS and PHP and that is up and running fine however, I’m converting the PHP queries to work with MSSQL. When I use the code below, nothing returns as a result… what am I doing wrong? The query itself is fine, as it works when I execute it via SQL on the server.
<?php
// Request the text of all the info and sort it *****ASC*****
$order = isset($_GET['order']) ? mssql_escape_string($_GET['order']) : 'vd.Vendor, nm.Version +1 DESC'; // Request the text of all the info
$result = @mssql_query("SELECT
vd.Vendor,
nm.Name,
nm.Version,
dt.Serial,
dt.Comments,
dt.Acquired,
ow.Owner,
dp.Department,
cp.Computer
FROM
dbo.KeyList_Vendor vd
LEFT JOIN dbo.KeyList_Name nm ON vd.Vendor_ID = nm.Vendor_ID
LEFT JOIN dbo.KeyList_Detail dt ON nm.Name_ID = dt.Name_ID
LEFT JOIN dbo.KeyList_Owner ow ON dt.Owner_ID = ow.Owner_ID
LEFT JOIN dbo.KeyList_Department dp ON dt.Department_ID = dp.Department_ID
LEFT JOIN dbo.KeyList_Computer cp ON dt.Computer_ID = cp.Computer_ID
WHERE
vd.Vendor_ID = '4'
ORDER BY $order");
if (!$result) {
exit('<p>Error performing query: ' . mssql_error() . '</p>');
}
//creating the table w/ headers
echo '
<table id="tblhover" cellpadding="3px">
<tr>
<td style="width:200px;font-weight:bold"><p><a href="?order=nm.Name" rel="nofollow">Item</a></p></td>
<td style="width:65px;font-weight:bold"><p><a href="?order=nm.Version" rel="nofollow">Version</a></p></td>
<td style="width:250px;font-weight:bold"><p><a href="?order=dt.serial" rel="nofollow">Serial</a></p></td>
<td style="width:125px;font-weight:bold"><p><a href="?order=dp.Department" rel="nofollow">Department</a></p></td>
<td style="width:125px;font-weight:bold"><p><a href="?order=ow.Owner" rel="nofollow">Owner</a></p></td>
<td style="width:25px;font-weight:bold"><p><a href="?order=cp.Computer" rel="nofollow">Computer(s)</a></p></td>
</tr>';
// Display each item
while ($row = mssql_free_result($result)) {
echo '
<tr>
<td style="width:200px;">' . $row['nm.Name'] . '</td>
<td style="width:65px;">' . $row['nm.Version'] . '</td>
<td style="width:250px;">' . $row['dt.Serial'] . '</td>
<td style="width:65px;" title="Purchased: ' . $row['dt.Acquired'] . '">' . $row['dp.Department'] . '</td>
<td style="width:65px;">' . $row['ow.Owner'] . '</td>
<td style="width:25px;" title="Comments: ' . $row['dt.Comments'] . '">' . $row['cp.Computer'] . '</td>
</tr>
';
}
echo '</table>';
$order = '';
switch($_GET['order'])
{
case 'order':
$order = 'nm.Name';
break;
case 'nm.Name':
$order = 'item';
break;
case 'nm.version':
$order = 'version';
break;
case 'dt.Serial':
$order = 'serial';
break;
case 'ow.owner':
$order = 'ownername';
break;
case 'dp.Department':
$order = 'department';
break;
default:
$order = 'order';
break;
}
;
?>
Are you sure it successfully connected to the server?
I don’t see your connect statement, can you remove your username/password and post that code too? It should mimic something like the following example:
http://www.php.net/manual/en/function.mssql-connect.php
There’s a call at the top of the page that I didn’t include that calls this config file:
<?php
// Connect to the database server
$oDbLink = mssql_connect('server', 'name','pass'); // connect
?>
okay, and have you done a if (!$oDbLink) die(“An error occurred establishing the connection.”); check?
I just updated the code to this which was on the example page, and my queries still come back blank.
<?php
$server = 'server';
// Connect to MSSQL
$link = mssql_connect($server, 'un', 'pass');
if (!$link) {
die('Something went wrong while connecting to MSSQL');
}
?>
After your query, put var_dump(mssql_get_last_message()); and see if that tells you anything
http://www.php.net/manual/en/function.mssql-get-last-message.php
A second thought, do you have a call to mssql_select_db following your mssql_connect statement?
I didn’t, I do now and it renders the HTML for the table header but not data from the query. I did get an error when I added the bad query.
That’s because you are calling mssql_free_result instead of mssql_fetch_row
Change
while ($row = mssql_free_result($result)) {
To
while ($row = mssql_fetch_row($result)) {
I’m getting the same results, here’s the updated code:
<?php
// Request the text of all the info and sort it *****ASC*****
$order = isset($_GET['order']) ? mssql_escape_string($_GET['order']) : 'nm.Name DESC'; // Request the text of all the info
$result = @mssql_query("SELECT vd.Vendor,nm.Name,nm.Version,dt.Serial,dt.Comments,dt.Acquired,ow.Owner,dp.Department,cp.Computer FROM dbo.KeyList_Vendor vd
LEFT JOIN dbo.KeyList_Name nm ON (vd.Vendor_ID = nm.Vendor_ID)
LEFT JOIN dbo.KeyList_Detail dt ON (nm.Name_ID = dt.Name_ID)
LEFT JOIN dbo.KeyList_Owner ow ON (dt.Owner_ID = ow.Owner_ID)
LEFT JOIN dbo.KeyList_Department dp ON (dt.Department_ID = dp.Department_ID)
LEFT JOIN dbo.KeyList_Computer cp ON (dt.Computer_ID = cp.Computer_ID)
WHERE vd.Vendor_ID = '4' ORDER BY $order");
if (!$result) {
exit('<p>Error performing query: ' . mssql_error() . '</p>');
}
//creating the table w/ headers
echo '
<table id="tblhover" cellpadding="3px">
<tr>
<td style="width:200px;font-weight:bold"><p><a href="?order=nm.Name" rel="nofollow">Item</a></p></td>
<td style="width:65px;font-weight:bold"><p><a href="?order=nm.Version" rel="nofollow">Version</a></p></td>
<td style="width:250px;font-weight:bold"><p><a href="?order=dt.serial" rel="nofollow">Serial</a></p></td>
<td style="width:125px;font-weight:bold"><p><a href="?order=dp.Department" rel="nofollow">Department</a></p></td>
<td style="width:125px;font-weight:bold"><p><a href="?order=ow.Owner" rel="nofollow">Owner</a></p></td>
<td style="width:25px;font-weight:bold"><p><a href="?order=cp.Computer" rel="nofollow">Computer(s)</a></p></td>
</tr>';
// Display each item
while ($row = mssql_fetch_row($result)) {
echo '
<tr>
<td style="width:200px;">' . $row['nm.Name'] . '</td>
<td style="width:65px;">' . $row['nm.Version'] . '</td>
<td style="width:250px;">' . $row['dt.Serial'] . '</td>
<td style="width:65px;" title="Purchased: ' . $row['dt.Acquired'] . '">' . $row['dp.Department'] . '</td>
<td style="width:65px;">' . $row['ow.Owner'] . '</td>
<td style="width:25px;" title="Comments: ' . $row['dt.Comments'] . '">' . $row['cp.Computer'] . '</td>
</tr>
';
}
echo '</table>';
$order = '';
switch($_GET['order'])
{
case 'order':
$order = 'nm.Name';
break;
case 'nm.Name':
$order = 'item';
break;
case 'nm.version':
$order = 'version';
break;
case 'dt.Serial':
$order = 'serial';
break;
case 'ow.owner':
$order = 'ownername';
break;
case 'dp.Department':
$order = 'department';
break;
default:
$order = 'order';
break;
}
;
?>
And you don’t get any additional output if you put var_dump(mssql_get_last_message()); above
var_dump(mssql_get_last_message());
while ($row = mssql_fetch_row($result)) {
May need to do a view source to see it…
If I add that, I get this above the start of the table header:
string(47) “Changed database context to ‘Software_Support’.”
HTML Source for the table:
<table id="tblhover" cellpadding="3px">
<tr>
<td style="width:200px;font-weight:bold"><p><a href="?order=nm.Name" rel="nofollow">Item</a></p></td>
<td style="width:65px;font-weight:bold"><p><a href="?order=nm.Version" rel="nofollow">Version</a></p></td>
<td style="width:250px;font-weight:bold"><p><a href="?order=dt.serial" rel="nofollow">Serial</a></p></td>
<td style="width:125px;font-weight:bold"><p><a href="?order=dp.Department" rel="nofollow">Department</a></p></td>
<td style="width:125px;font-weight:bold"><p><a href="?order=ow.Owner" rel="nofollow">Owner</a></p></td>
<td style="width:25px;font-weight:bold"><p><a href="?order=cp.Computer" rel="nofollow">Computer(s)</a></p></td>
</tr>string(47) "Changed database context to 'Software_Support'."
<tr>
<td style="width:200px;"></td>
<td style="width:65px;"></td>
<td style="width:250px;"></td>
<td style="width:65px;" title="Purchased: "></td>
<td style="width:65px;"></td>
<td style="width:25px;" title="Comments: "></td>
</tr>
</table>
Ah, I see. You are getting a result, the data is just empty (or using the wrong key).
Put this inside your while loop
var_dump($row);
Example:
while ($row = mssql_fetch_row($result)) {
var_dump($row);
That will show you the keys to use to reference your data.
I got this but I’m struggling how what I’m doing wrong as this works great with MySQL.
array(9) { [0]=> string(5) “Adobe” [1]=> string(22) “Design and Web Premium” [2]=> string(3) “CS6” [3]=> string(13) “158651-654654” [4]=> string(4) “Test” [5]=> string(10) “06/11/2013” [6]=> string(11) “James” [7]=> string(16) “Software Support” [8]=> string(10) “computerID” }
Okay, it has been a while since I’ve worked with MSSQL in PHP, so I missed the fact that you really want to use mssql_fetch_array($result, MSSQL_ASSOC) instead of mssql_fetch_row($result)
You may still have to change $row[‘nm.Name’] to $row[‘Name’], but I won’t know that until you change mssql_fetch_row to mssql_fetch_array($result, MSSQL_ASSOC)
Both suggestions worked, thank you very much for your help.