PHP

Sending SQL to MSSQL

mssql_query()

The function mssql_query() sends an SQL statement to the MSSQL/MSDE installation. Again, the parameter order is a bit strange: first the SQL command, then the database handle. However, as you saw in Listing 7.18 in the call to mssql_select_db(), this information can also be omitted then, the last connection established is used automatically.

Another important point is escaping special characters. In MSSQL/MSDE, single quotes must not be escaped using a backslash, but double quotes are the way to go:

INSERT INTO quotes (quote, author, year) VALUES ('Ain''t Misbehavin''', 'Louis Armstrong',
 1929)

To achieve this, addslashes() can be usedhowever, first, it has to be configured to behave so that MSSQL/MSDE-compatible strings are returned:

Sending SQL to MSSQL/MSDE
ini_set('magic_quotes_sybase', 'On');
$author = addslashes($_POST['author']);
<?php
  if ($db = @mssql_connect('localhost', 'user',
    'password')) {
    require_once 'stripFormSlashes.inc.php';
    mssql_select_db('hoshmand', $db);
    ini_set('magic_quotes_sybase', 'On');
    mssql_query(sprintf(
      'INSERT INTO quotes (quote, author, year)
         VALUES (\'%s\', \'%s\', \'%s\')',
      addslashes($_POST['quote']),
      addslashes($_POST['author']),
      intval($_POST['year'])), $db);
    echo 'Quote saved.';
    mssql_close($db);
  } else {
    echo 'Connection failed.';
  }
?>

The listing at the beginning of This sanitizes some form data and writes it to the (by now) well-known sample database.

Retrieving Results of a Query to MSSQL

$result = mssql_query();
mssql_fetch_assoc($result);

Finally, there is, of course, also a way to retrieve all data in the resultset. A while loop comes into play, using one of these functions:

  • mssql_fetch_assoc() returns the current row in the resultset as an associative array

  • mssql_fetch_object() returns the current row in the resultset as an object

  • mssql_fetch_row() returns the current row in the resultset as a numeric array

Retrieving Data from MSSQL/MSDE
<table>
<tr><th>#</th><th>Quote</th><th>Author</th><th>Year<
  /th></tr>
<?php
  if ($db = @mssql_connect('localhost', 'user',
    'password')) {
    mssql_select_db('hoshmand', $db);
    $result = mssql_query('SELECT * FROM quotes',
      $db);
    while ($row = mssql_fetch_assoc($result)) {
      printf(
        '<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></
   tr>',
        htmlspecialchars($row['id']),
        htmlspecialchars($row['quote']),
        htmlspecialchars($row['author']),
        htmlspecialchars($row['year'])
      );
    }
    mssql_close($db);
  } else {
    echo '<tr><td colspan="4">Connection
      failed.</td></tr>';
  }
?>
</table>

by BrainBellupdated
Advertisement: