CODEWITHSUNDEEP
phpmysqliis
Martin Jašek
Martin Jašek

Reputation: 77

IIS, MS SQL and PHP - SQL select in PHP not working

I have a local MS SQL Database, and a web PHP application on IIS on my server.

On IIS I have successfully connected PHP and my MS SQL database (added connection strings and i see my tables)

But, when I use any SQL select in the PHP web application, it does not work. No data is displayed, or any erros, for example :

/* Connect to a MySQL database using driver invocation */
$dsn = 'mysql:dbname=dbname;host=localhost';
$user = 'dbuser';
$password = 'dbpass';    
    try {
        $dbh = new PDO($dsn, $user, $password);
    } catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();
    }        
    $results = mysql_query("SELECT id FROM users");
    while($row = mysql_fetch_array($results)) {
    $name = $row['id']
    ?>
        <tr>
            <td><?php echo '$name'?></td>
        </tr>
    <?php
    }
    ?>
</tbody>
</table>

Upvotes: 2

Views: 909

Answers (5)

Martin Jašek
Martin Jašek

Reputation: 77

Now working great also with select, with this code :

<?php
$serverName = "AC-CLOUD"; //serverName\instanceName
$connectionInfo = array( "Database"=>"Data", "UID"=>"sa", "PWD"=>"Masterkey2010");
$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));
}

$sql = "SELECT Code, Name FROM StoreCards";
$stmt = sqlsrv_query( $conn, $sql );
if( $stmt === false) {
    die( print_r( sqlsrv_errors(), true) );
}

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
      echo $row['Code'].", ".$row['Name']."<br />";
}

sqlsrv_free_stmt( $stmt);


  ?>

How now i set this two values into table? this is last point. thank you for your patience and time.

Upvotes: 0

Professor Abronsius
Professor Abronsius

Reputation: 33813

Mixing the apis would not work - use only PDO methods like this perhaps

/* Connect to a MySQL database using driver invocation */
try {
    /* mysql server */
    /* $dsn = 'mysql:dbname=dbname;host=localhost'; */

    /* MS SQL Server */
    $dsn = 'sqlsrv:Database=dbname;Server=localhost';

    $user = 'dbuser';
    $password = 'dbpass';

    $dbh = new PDO($dsn, $user, $password);
    $sql='select * from users';
    $results=$dbh->query( $sql );

    if( $results ){
        while( $rs=$results->fetch( PDO::FETCH_OBJ ) ){
            echo "<tr><td>{$rs->name}</td></tr>";
        }
    }


} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

Upvotes: 0

devman
devman

Reputation: 31

MS SQL (or SqlSrv) and MySql are not working on the sames drivers. You have to know which one you are using and the find PHP functions ables to deal with it.

Note: PHP Extension for using driver must be installed on your server and activated on php.ini file

For MySql do not use mysql_xxx() deprecated functions, prefer mysqli_xxx() to them.

You can find here docs and samples code for both mysql & mssql php functions :

MySql :

http://php.net/mysqli_connect

php.net/mysqli_fetch_array

SqlSrv :

http://php.net/sqlsrv_connect

php.net/sqlsrv_fetch_array

So what is your database engine ?

Hope that'll helps you, cheers

Upvotes: 1

Shivrag
Shivrag

Reputation: 11

Please follow that code:

$host = '127.0.0.1';

$db   = 'test';

$user = 'root';

$pass = '';

$charset = 'utf8';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";


$opt = 
[

PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,

    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,

    PDO::ATTR_EMULATE_PREPARES   => false,

];

$pdo = new PDO($dsn, $user, $pass, $opt);

$stmt = $pdo->query('SELECT name FROM users');

while ($row = $stmt->fetch())
{

    echo $row['name'] . "\n";
}

Upvotes: 1

user5876173
user5876173

Reputation:

follow like this for pdo connection

$sql = $dbh->prepare("SELECT id FROM users");
$sql->execute();
while($result = $sql->fetch(PDO::FETCH_ASSOC)){
  ?>
 <tr>
 <td><?php echo $result['name'];?></td>
 </tr>
 <?php } ?>

Upvotes: 2

Related Questions