PHP Query not working when connect to a MSSQL DB

Mainer82 · June 11, 2013, 7:48pm

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;
        }
        ;
        ?>

cpradio · June 12, 2013, 11:30am

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

Mainer82 · June 12, 2013, 12:06pm

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

?>

cpradio · June 12, 2013, 12:19pm

okay, and have you done a if (!$oDbLink) die(“An error occurred establishing the connection.”); check?

Mainer82 · June 12, 2013, 12:52pm

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');
}
?>

cpradio · June 12, 2013, 1:06pm

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

cpradio · June 12, 2013, 1:07pm

A second thought, do you have a call to mssql_select_db following your mssql_connect statement?

Mainer82 · June 12, 2013, 1:17pm

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.

cpradio · June 12, 2013, 1:19pm

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)) {

Mainer82 · June 12, 2013, 1:24pm

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;
        }
        ;
        ?>

cpradio · June 12, 2013, 1:32pm

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…

Mainer82 · June 12, 2013, 1:36pm

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>

cpradio · June 12, 2013, 1:58pm

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.

Mainer82 · June 12, 2013, 2:11pm

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” }

cpradio · June 12, 2013, 2:28pm

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)

Mainer82 · June 12, 2013, 4:01pm

Both suggestions worked, thank you very much for your help.

Topic		Replies	Views
PHP form and MSSQL help PHP	3	1064	October 8, 2014
MSSQL form conversion from MySQL to MSSQL PHP	6	1259	October 8, 2014
MySQL failing in PHP without error PHP	9	1774	January 14, 2016
Dynamically populate drop down list with PHP / MSSQL PHP	3	2980	October 8, 2014
Cannot Select DB using mssql PHP	4	3758	October 13, 2010

PHP Query not working when connect to a MSSQL DB

Related topics