Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - odbc_prepare() - Creating Prepared Statements

By: FYIcenter.com

(Continued from previous topic...)

How To Create Prepared Statements using odbc_prepare()?

If you have a SQL statement that need to executed repeatedly many times with small changes, you can create a prepared statement object with parameters so it can be executed more efficiently.

There are two functions you need to use prepare and execute a prepared statement object:

$statement_object = odbc_prepare($connection,
   $statement_string);
#- The $statement_string may have parameters represented
#- by "?".

$result_set = odbc_execute($statement_object $array);
#- The $array is used to supply values to parameters 
#- defined in the statement object.

The tutorial PHP script below shows you how to insert 3 rows into a table with a prepared statement object with 2 parameters:

<?php
  $con = odbc_connect('FYI_SQL_SERVER','sa','FYIcenter');

  $sql = "INSERT INTO fyi_rates (id, comment) VALUES (?,?)";
  $statement = odbc_prepare($con, $sql);

  $res = odbc_execute($statement, array(301, "Good"));
  $res = odbc_execute($statement, array(302, "Average"));
  $res = odbc_execute($statement, array(303, "Bad"));

  $sql = "SELECT * FROM fyi_rates WHERE id>300";
  $res = odbc_exec($con, $sql);
  while (odbc_fetch_row($res)) {
     print("   ".odbc_result($res,1));
     print(", ".odbc_result($res,2)."\n");
  }
  odbc_free_result($res);

  odbc_close($con); 
?>

If you this sample script, you will get:

   301, Good
   302, Average
   303, Bad

1. What Are the Requirements to Use ODBC Connections in PHP Scripts?
2. What Are Commonly Used ODBC Functions in PHP?
3. How To Test ODBC DSN Connection Settings?
4. How To Connect to a SQL Server using odbc_connect()?
5. How To List All DSN Entries on Your Local Machine using odbc_data_source()?
6. How To Execute a SQL Statement using odbc_exec()?
7. How To Retrieve Error Messages using odbc_errormsg()?
8. How To Turn Off Warning Messages during PHP Execution?
9. How To Receive Returning Result from a Query?
10. How To Loop through Result Set Objects using odbc_fetch_row()?
11. How To Retrieve Field Values using odbc_result()?
12. How To List All Tables in the Database using odbc_tables()?
13. How To List All Columns in a Table using odbc_columns()?
14. How To Create Prepared Statements using odbc_prepare()?