Running SQL Query in PHP File

Question

I have established a connection with my SQL Database through Windows Authentication in my php file. However I'm not sure how the syntax will look if I wanted to display a simple SQL query such as (Select * from Media) on the php page that shows an entire table. I tried a few methods but it displayed a fatal error.

Here is my code for the php:

     <?php

     $serverName = "172.20.90.170,5050"; //serverName\instanceName

     // Since UID and PWD are not specified in the $connectionInfo array,
     // The connection will be attempted using Windows Authentication.
     $connectionInfo = array( "Database"=>"TestDB");
     $conn = sqlsrv_connect( $serverName, $connectionInfo);

     if( $conn ) {
     echo "Connection established.<br />";
     }else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
     }
     $version = mssql_query('SELECT * FROM MEDIA');
     $row = mssql_fetch_array($version);
     echo $row[0];

     ?>

Fatal Error:

  Fatal error: Call to undefined function mssql_query()

This establishes a succesful connection but what would I need to change in my code for this query to run error free and display the required output?

You're using sqlsrv_connect, why then do you try to use mssql_query? Don't you want to use sqlsrv_query? — gen_Eric
– gen_Eric, Commented Jul 24, 2013 at 14:34
did you try stackoverflow.com/questions/17103751/… or stackoverflow.com/questions/7402713/… — Arpit Gupta
– Arpit Gupta, Commented Jul 24, 2013 at 14:42

Kermit · Accepted Answer · 2013-07-24 14:35:41Z

3

It appears you are mixing two different API's (sqlsrv_ and mssql_).

If you're using sqlsrv_, then a simple statement could look like:

$connectionInfo = array( "Database" => "database", "UID" => "username", "PWD" => "password" );
$conn = sqlsrv_connect( "Server", $connectionInfo );

$stmt = "SELECT [column] FROM [table] WHERE [id] = ?";
$params = array( $id );

$query = sqlsrv_query( $conn, $stmt, $params );
if( $query === false ) {
    print( print_r( sqlsrv_errors() ) );
}

while( $row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC )) {
    echo $row['column'];
}

sqlsrv_close( $conn );

Resources

MSDN Documentation
PHP Documentation (contains both Windows & SQL Server authentication examples)

answered Jul 24, 2013 at 14:35

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

omar K Over a year ago

this is for the SQL Server Authentication. How would you place the select statement in my question with the Windows Authentication method?

Kermit Over a year ago

@omarK The PHP docs show how you would connect using Windows Auth.

omar K Over a year ago

I'm not sure but now it's just displaying an empty white page when I tried to run it with your code.

Kermit Over a year ago

@omarK Do you have error_reporting enables and display_errors on?

ital0 · Accepted Answer · 2013-07-24 14:41:27Z

2

From what I understand, you're new to the PHP world, BUT, I advise you to use the PDO class. You will have more facility to do what you want.

Here's a sample using your data. If you study, you can understand.

try
{
    $connection = new PDO("sqlsrv:Server=172.20.90.170;Database=TestDB", "YOUR_USERNAME", "YOUR_PASSWORD");
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $connection->setAttribute(PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ENCODING_SYSTEM);
}catch (Exception $e)
{
    echo $e->getMessage();
    die('Connection could not be established.<br />');
}

try
{
    $sql = 'SELECT * FROM MEDIA';
    $query = $connection->prepare($sql);
    $query->execute();

    $result = $query->fetchAll(PDO::FETCH_ASSOC);
}catch (Exception $e)
{
    die('Cant fetch rows.');
}

foreach ($result as $r)
{
    print_r($r); // do what you want here
}

answered Jul 24, 2013 at 14:41

ital0

3442 silver badges8 bronze badges

8 Comments

omar K Over a year ago

but doesnt this use the sql server authentication method since it's asking for the username and password?

omar K Over a year ago

okay so this is displaying the data but not in a table format its just line after line, do you know how I could display it as a table like in SQL?

omar K Over a year ago

is it possible for you to edit your code to display it in a table format? I just want to test it.

ital0 Over a year ago

Sure. In the foreach loop, you can do this. echo '<table>'; foreach ($result as $r) { echo '<tr>'; echo '<td>' . $r['column_name1'] . '</td>'; echo '<td>' . $r['column_name2'] . '</td>'; echo '<tr>'; } echo '</table>';

omar K Over a year ago

it's still showing them as rows of lines after lines but just a large space between each row now. Still not in a table format.

|

m4t1t0 · Accepted Answer · 2013-07-24 14:38:22Z

0

As others says in the comments, you have two options:

1. Use sqlsrv_query
2. Install MSSQL support to PHP.

Sincerely, I prefer use mssql_* stack of functions. If you use ubuntu, you can install MSSQL support simply:

aptitude install php5-sybase

answered Jul 24, 2013 at 14:38

m4t1t0

5,7313 gold badges25 silver badges30 bronze badges

Collectives™ on Stack Overflow

Running SQL Query in PHP File

3 Answers 3

4 Comments

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related