1. PHP MSSQL - How To Create an Identity Column?
Many tables require an ID column to assign a unique ID number for each row in the table. For example, if you have a table to hold forum member profiles, you need an ID number to identify each member. To allow SQL Server to automatically assign a new ID number for each new record, you can define the ID column with IDENTITY attribute as shown in the following sample script:
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
$sql = "CREATE TABLE ggl_users ("
. " id INTEGER IDENTITY NOT NULL"
. ", name VARCHAR(80) NOT NULL"
. ", email VARCHAR(80) NULL"
. ", time DATETIME NULL"
. ")";
$res = mssql_query($sql, $con);
if (!$res) {
print("Table creation failed with error:
");
print(" ".mssql_get_last_message()."
");
} else {
print("Table ggl_users created.
");
}
mssql_close($con);
?>
2. PHP MSSQL - How To Query Multiple Tables Jointly?
If you want to query information stored in multiple tables, you can use the SELECT statement with a WHERE condition to make an inner join. Assuming that you have 3 tables in a forum system: "users" for user profile, "forums" for forums information, and "posts" for postings, you can query all postings from a single user with a script as shown below:
<?php
$con = mssql_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');
$userID = 101;
$sql = "SELECT posts.subject, posts.time, users.name,"
. " forums.title"
. " FROM posts, users, forums"
. " WHERE posts.userID = ".$userID
. " AND posts.userID = users.id"
. " AND posts.forumID = forums.id";
$res = mssql_query($sql, $con);
while ($row = mssql_fetch_array($res)) {
print($row['subject'].", ".$row['time'].", "
.$row['name'].", ".$row['title']."
");
}
mssql_free_result($res);
mssql_close($con);
?>
3. PHP MSSQL - How To Perform Key Word Search in Tables?
The simplest way to perform key word search is to use the SELECT statement with a LIKE operator in the WHERE clause. The LIKE operator allows you to match a text field with a keyword pattern specified as '%keyword%', where (%) represents any number of any characters. Any single quote (') in the keyword needs to be protected by replacing them with two single quotes (''). The tutorial exercise below shows you how to search for records whose "notes" contains "e":
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
$key = "e";
$key = str_replace("'", "''", $key);
$sql = "SELECT id, url, notes FROM ggl_links"
. " WHERE notes LIKE '%".$key."%'";
$res = mssql_query($sql, $con);
while ($row = mssql_fetch_array($res)) {
print($row['id'].", ".$row['url'].", "
. $row['notes']."
");
}
mssql_free_result($res);
mssql_close($con);
?>
If you run this script, you will get something like this:
102, www.GlobalGuideLine.com, Nice site.
202, www.yahoo.com, It's another search engine!
301, netsc
4. PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes?
You have seen a lots of Websites are displaying past times in days, hours and minutes. If you want to do this yourself, you can use the DATEDIFF() SQL function The following tutorial exercise shows you how to use DATEDIFF() to present a past time in days, hours, and minutes:
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
$submit_time = "2007-05-29 04:09:49";
$sql = "SELECT 'Posted '"
. " + CONVERT(VARCHAR(40),"
. " DATEDIFF(minute, '$submit_time',"
. " GETDATE())/(24*60))"
. " + ' days, '"
. " + CONVERT(VARCHAR(40),"
. " DATEDIFF(minute, '$submit_time',"
. " GETDATE())%(24*60)/60)"
. " + ' hours, and '"
. " + CONVERT(VARCHAR(40),"
. " DATEDIFF(minute, '$submit_time',"
. " GETDATE())%60)"
. " + ' minutes ago.'";
print("
$sql
");
$res = mssql_query($sql, $con);
if (!$res) {
print("SQL statement failed with error:
");
print(" ".mssql_get_last_message()."
");
} else
5. PHP MSSQL - How To Include Date and Time Values in SQL Statements?
If you want to provide date and time values in a SQL statement, you should write them in the format of "yyyy-mm-dd hh:mm:ss", and quoted with single quotes ('). The tutorial exercise below shows you two INSERT statements. The first one uses a hard-code date value. The second one uses the date() function to return a date value representing current date and time.
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
$notes = "Added long time ago!";
$time = "1999-01-01 01:02:03";
$sql = "INSERT INTO ggl_links (id, url, notes, time)"
. " VALUES ("
. " 301, 'netscape.com', '".$notes."', '".$time."')";
if (!mssql_query($sql, $con)) {
print("SQL statement failed with error:
");
print(" ".mssql_get_last_message()."
");
} else {
print("1 rows inserted.
");
}
$notes = "Added today!";
$time = date("Y-m-d H:i:s");
$sql = "INSERT INTO ggl_links (id, url, notes, time)"
. " VALUES ("
. " 302, 'myspace.com', '".$notes."', '".$time."')";
if (!mssql_query
6. PHP MSSQL - How To Include Text Values in SQL Statements?
Text values in SQL statements should be quoted with single quotes ('). If the text value contains a single quote ('), it should be protected by replacing it with two single quotes (''). In SQL language syntax, two single quotes represents one single quote in string literals.
The tutorial exercise below shows you two INSERT statements. The first one will fail, because it has an un-protected single quote. The second one will be ok, because a str_replace() is used to replace (') with (''):
7. PHP MSSQL - How To Delete Existing Rows in a Table?
If you want to remove a row from a table, you can use the DELETE statement with a WHERE clause to identify the row. The following sample script deletes one row:
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
$sql = "DELETE FROM ggl_links WHERE id = 1102";
$res = mssql_query($sql,$con);
if (!$res) {
print("SQL statement failed with error:
");
print(" ".mssql_get_last_message()."
");
} else {
$number_of_rows = mssql_rows_affected($con);
print("$number_of_rows rows deleted.
");
}
mssql_close($con);
?>
If you run this script, you will get something like this:
1 rows deleted.
If you run it again, no rows will be deleted. And you will get something like this:
0 rows deleted.
8. PHP MSSQL - How To Update Existing Rows in a Table?
Updating existing rows in a table requires to run the UPDATE statement with a WHERE clause to identify the row. The following sample script updates one row with two new values:
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
$sql = "UPDATE ggl_links SET notes='Nice site.', counts=8"
. " WHERE id = 102";
$res = mssql_query($sql,$con);
if (!$res) {
print("SQL statement failed with error:
");
print(" ".mssql_get_last_message()."
");
} else {
$number_of_rows = mssql_rows_affected($con);
print("$number_of_rows rows updated.
");
}
mssql_close($con);
?>
If you run this script, you will get something like this:
1 rows updated.
9. PHP MSSQL - How To Loop through Returning Rows?
The best way to query tables and loop through returning rows is to run a SELECT statement with the mssql_query() function, catch the returning object as a result set, and loop through the result with mssql_fetch_array() function in a while loop as shown in the following sample PHP script:
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
$sql = "SELECT id, url, time FROM ggl_links";
$res = mssql_query($sql,$con);
while ($row = mssql_fetch_array($res)) {
print($row['id'].",".$row['url'].",".$row['time']."
");
}
mssql_free_result($res);
mssql_close($con);
?>
10. PHP MSSQL - What Is a Result Set Object Returned by mssql_query()?
A result set object is a logical representation of data rows returned by mssql_query() function on SELECT statements. Every result set object has an internal pointer used to identify the current row in the result set. Once you get a result set object, you can use the following functions to retrieve detail information:
* mssql_free_result($res) - Closes this result set object.
* mssql_num_rows($res) - Returns the number rows in the result set.
* mssql_num_fields($res) - Returns the number fields in the result set.
* mssql_fetch_row($res) - Returns an array contains the next row indexed by field positions. The internal pointer is moved to the next row too.
11. PHP MSSQL - How To Get the Number of Affected Rows?
If you insert multiple rows with a single INSERT statement, you can use the mssql_rows_affected() function to find out how many rows were inserted. mssql_rows_affected($connection) returns the number of affected rows of the last INSET, UPDATE or DELETE statement.
The following tutorial script shows you report back the number of rows inserted properly:
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
$sql = "INSERT INTO ggl_links"
. " SELECT id+1000, REVERSE(url), notes, counts, time"
. " FROM ggl_links WHERE id > 1000";
$res = mssql_query($sql,$con);
if (!$res) {
print("SQL statement failed with error:
");
print(" ".mssql_get_last_message()."
");
} else {
$number_of_rows = mssql_rows_affected($con);
print("$number_of_rows rows inserted.
");
}
mssql_close($con);
If you run this script, you should get:
2 rows inserted
12. PHP MSSQL - How To Insert Multiple Rows with a subquery?
If want to insert rows into a table based on data rows from other tables, you can use a subquery inside the INSERT statement as shown in the following script example:
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
$sql = "INSERT INTO ggl_links"
. " SELECT id+1000, REVERSE(url), notes, counts, time"
. " FROM ggl_links";
$res = mssql_query($sql,$con);
if (!$res) {
print("SQL statement failed with error:
");
print(" ".mssql_get_last_message()."
");
} else {
print("Multiple rows inserted.
");
}
mssql_close($con);
If you run this script, the table should have 4 rows now. And you will get:
Multiple rows inserted
13. PHP MSSQL - How To Insert Data with NULL Values?
There are two ways to provide NULL value to a column in an INSERT statement:
* Include the column in the statement, but specify keyword NULL as the value.
* Exclude the column from the statement.
The following tutorial exercise inserts two rows. Both of them have NULL values. But they were inserted differently:
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
$sql = "INSERT INTO ggl_links"
. " (id, url, notes, counts, time)"
. " VALUES (101, 'www.GlobalGuideLine.com',"
. " NULL, NULL, NULL)";
$res = mssql_query($sql,$con);
if (!$res) {
print("SQL statement failed with error:
");
print(" ".mssql_get_last_message()."
");
} else {
print("One data row inserted.
");
}
$sql = "INSERT INTO ggl_links (id, url) VALUES ("
. " 102, 'www.GlobalGuideLine.com')";
$res = mssql_query($sql,$con);
print("Another data row inserted.
");
mssql_close($con);
?>
If you run this script, you will get:
One data row inserted.
Another data row inserted.
14. PHP MSSQL - How To Make a Column Nullable?
Based on the testing result from the previous tutorial you can find out that there is a big difference in the column definition when running CREATE TABLE statement with mssql_query():
* If CREATE TABLE is executed through mssql_query() and "NULL/NOT NULL" keyword is omitted in column definition, mssql_query() will assume NOT NULL.
* If CREATE TABLE is executed directly on SQL Server and "NULL/NOT NULL" keyword is omitted in column definition, SQL Server will use NULL as the default.
Now you have to modify the CREATE TABLE statement to create "ggl_links" again by adding NULL to columns: notes, counts, and time:
15. PHP MSSQL - How To Insert Data into an Existing Table?
If you want to insert a row of data into an existing table, you can use the INSERT INTO statement as shown in the following sample script:
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
$sql = "INSERT INTO ggl_links (id, url) VALUES ("
. " 101, 'www.GlobalGuideLine.com')";
$res = mssql_query($sql,$con);
if (!$res) {
print("SQL statement failed with error:
");
print(" ".mssql_get_last_message()."
");
} else {
print("One data row inserted.
");
}
mssql_close($con);
?>
If you run this script, unfortunately, you will get an error:
SQL statement failed with error:
The statement has been terminated.
So what is wrong with the statement? The error message does not give any details. You need to take this statement to SQL Server Management Studio to try it:
USE GlobalGuideLineDatabase
GO
INSERT INTO ggl_links (id, url) VALUES (
101, 'www.GlobalGuideLine.com')
GO
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'notes',
table 'GlobalGuideLineDatabase.dbo.ggl_links
16. PHP MSSQL - How To Drop an Existing Table?
If you need to delete a table created before, you can run the DROP TABLE SQL statement using the mssql_query() function, as shown in the following sample script:
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
# dropping an existing table
$sql = "DROP TABLE ggl_links";
$res = mssql_query($sql,$con);
print("Table ggl_links dropped.
");
# creating a new table
$sql = "CREATE TABLE ggl_links ("
. " id INT NOT NULL"
. ", url VARCHAR(80) NOT NULL"
. ", notes VARCHAR(1024)"
. ", counts INT"
. ", time DATETIME"
. ")";
$res = mssql_query($sql,$con);
print("Table ggl_links created.
");
mssql_close($con);
?>
If you run this script, "ggl_links" will be dropped and created again:
Table ggl_links dropped.
Table ggl_links created.
17. PHP MSSQL - How To Create a New Table?
If you want to create a table in the SQL Server database, you can run the CREATE TABLE SQL statement using the mssql_query() function, as shown in the following sample script:
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
# creating a new table
$sql = "CREATE TABLE ggl_links ("
. " id INT NOT NULL"
. ", url VARCHAR(80) NOT NULL"
. ", notes VARCHAR(1024)"
. ", counts INT"
. ", time DATETIME"
. ")";
$res = mssql_query($sql,$con);
if (!$res) {
print("Table creation failed with error:
");
print(" ".mssql_get_last_message()."
");
} else {
print("Table ggl_links created.
");
}
mssql_close($con);
?>
If you run this script for the first time and there is no existing table called ggl_links in the database, you will get:
Table ggl_links created.
If you run it again, you will get:
Table creation failed with error:
There is already an object named 'ggl_links' in the
database.
18. How To List All Field Names in the Result Set using mssql_field_name()?
The result set object returned by a SELECT statement also contains column (field) names, lengths and types. You can use mssql_field_name(), mssql_field_length() and mssql_field_type() to get those information. The tutorial exercise below shows a good example:
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
$sql = "SELECT * FROM sys.objects"
. " WHERE type_desc='USER_TABLE'";
$res = mssql_query($sql, $con);
print("Result set columns:
");
for ($i=0; $i
Run this PHP script, you will see a list columns (fields):
Result set columns:
name, char, 255
object_id, int, 4
principal_id, int, 4
schema_id, int, 4
parent_object_id, int, 4
type, char, 2
type_desc, char, 120
create_date, datetime, 8
modify_date, datetime, 8
is_ms_shipped, bit, 1
is_published, bit, 1
is_schema_published, bit, 1
19. How To Retrieve Field Values using mssql_result()?
Once the result set is captured in an object, you can think of it as a "table" with rows and columns (fields). You can use mssql_result() to retrieve the value of any given row and column (field) with this formats:
$value = mssql_result($res, $row, $column);
#- $row is the row number, starting with 0
#- $column is the column number, starting with 0
The tutorial PHP script below shows you how to list tables in the database with multiple field values:
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
$sql = "SELECT * FROM sys.objects"
. " WHERE type_desc='USER_TABLE'";
$res = mssql_query($sql, $con);
print("User Tables:
");
for ($i=0; $i
If you run this script, you will get something like:
User Tables:
ggl_rates, 85575343, USER_TABLE
ggl_team, 165575628, USER_TABLE
ggl_random, 821577965, USER_TABLE
ggl_links_indexed, 1061578820, USER_TABLE
ggl_links, 1093578934, USER_TABLE
ggl_links_copy, 1253579504, USER_TABLE
tipBackup2, 2121058592, USER_TABLE
20. How To Loop through Result Set Objects using mssql_fetch_array()?
If the returning output of a query statement is captured in a result set object, you can use mssql_fetch_array() to loop through each row in the output.
The tutorial PHP script below shows you how to list tables in the database:
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
$sql = "SELECT * FROM sys.objects"
. " WHERE type_desc='USER_TABLE'";
$res = mssql_query($sql, $con);
print("User Tables:
");
while ($row = mssql_fetch_array($res)) {
print(" ".$row{'name'}."
");
}
mssql_free_result($res);
mssql_close($con);
?>
If you run this script, you will get something like:
User Tables:
ggl_rates
ggl_team
ggl_random
ggl_links_indexed
ggl_links
ggl_links_copy
tipBackup2
21. How To Retrieve Error Messages using mssql_get_last_message()?
When you call mssql_query() to execute a SQL statement, and the execution failed on the SQL Server, you can use mssql_get_last_message() function to retrieve the error messages.
The tutorial script below shows you a good example:
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
# dropping a table
$sql = 'DROP TABLE fyi.center';
$res = mssql_query($sql, $con);
if (!$res) {
print("Execution failed:
");
print(" Error: ".mssql_get_last_message()."
");
} else {
print("Execution was successful.
");
}
mssql_close($con);
?>
22. How To Execute a SQL Statement using mssql_query()?
Once you have created a connection object, you can use the mssql_query() function to send a SQL statement to the SQL Server linked to the connection object for execution.
Here is a simple PHP script that creates a new schema and a new table:
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
# creating a new schema
$sql = 'CREATE SCHEMA ggl';
mssql_query($sql, $con);
# creating a new table
$sql = 'CREATE TABLE globalguideline (name VARCHAR(80))';
mssql_query($sql, $con);
mssql_close($con);
?>
If you run this script for the first time, it will execute those two statements correctly for you. But if you run it again, you will some warning messages:
Warning: mssql_query(): message: There is already an object
named 'ggl' in the database. (severity 16)
in C: estglobalguideline.php on line 7
Warning: mssql_query(): message: CREATE SCHEMA failed
due to previous errors. (severity 16)
in C: estglobalguideline.php on line 7
The messages are very clear and easy to understand.
23. How To Select an Exiting Database using mssql_select_db()?
The first thing after you have created a connection object to the SQL Server is to select the database where your tables are located, by using the mssql_select_db() function. If your MSSQL server is offered by your Web hosting company, they will assign an empty database to you and provide you the database name. You should use this name to select this empty database as your current database. The following script shows you how to select a database called "GlobalGuideLineDatabase".
To test the mssql_select_db() function, try the following script:
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
$sql = 'SELECT * FROM sys.tables';
if ($res = mssql_query($sql, $con)) {
print(mssql_num_rows($res) . " tables in database.
");
} else {
print("SQL failed.
");
}
mssql_close($con);
?>
You will get something like this:
10 tables in database.
24. How To Disconnect from a SQL Server using mssql_close()?
When you call mssql_connect(), it will return an object representing the connection to the SQL Server. This connection object will be used by subsequent MSSQL function calles. When you are done with this connection, you should close it to free up resources by calling the mssql_close() function.
The tutorial script below shows you how to call mssql_connect() and mssql_close() to connect and disconnect to the SQL Server:
<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
if (!$con) {
print("Connection failed with error.
");
} else {
print("The SQL Server connection object is ready.
");
mssql_close($con);
}
?>
If you run this script and get this output: "The SQL Server connection object is ready", your connection to the SQL Server are working.
Note that 'sa' and 'GlobalGuideLine' used in this script are system administrator login name and password. You may use any other login name and password defined on the SQL Server.
25. What Are Commonly Used MSSQL Functions in PHP?
If you look at the PHP 5 manual, you will see a group of functions listed under the Microsoft SQL Server Functions section. The commonly used functions are:
mssql_connect ? Open MS SQL server connection mssql_close ? Close MS SQL Server connection mssql_select_db ? Select MS SQL database mssql_query ? Send MS SQL query mssql_num_rows ? Gets the number of rows in result mssql_fetch_row ? Get row as enumerated array mssql_fetch_assoc ? Returns an associative array of the current row in the result mssql_fetch_array ? Fetch a result row as an associative array, a numeric array, or both mssql_fetch_object ? Fetch row as object mssql_free_result ? Free result memory mssql_num_fields ? Gets the number of fields in result mssql_field_name ? Get the name of a field mssql_field_type ? Gets the type of a field mssql_field_length ? Get the length of a field mssql_fetch_field ? Get field information mssql_get_last_message ? Returns the last message from the server mssql_rows_affected ? Returns the number of records affected by the query
Some of the functions will be discussed in this tutorial collection.
26. How To Connect PHP with Different Port Numbers?
You know that SQL Server could be configured to accept connections with different TCP/IP port numbers. See other tutorial collections on how to view and configure SQL Server TCP/IP protocol.
If you installed SQL Server 2005 Express Edition with default settings, it should be running with instance name of "SQLEXPRESS" and port number of "1269" You can use the mssql_connect() function to connect to the server in 3 ways:
$con = mssql_connect('LOCALHOST','login','pass');
$con = mssql_connect('LOCALHOSTSQLEXPRESS','login','pass');
$con = mssql_connect('LOCALHOST,1269','login','pass');
Other ways of entering the server name and port number will not work. The PHP manual has this statement: "servername - The MS SQL server. It can also include a port number. e.g. hostname:port." The example is really for non-Windows systems. Try the following testing PHP script to find out:
27. Where to Find ntwdblib.dll Version 2000.80.194.0?
You know that the copy of ntwdblib.dll version 2000.2.8.0 included in Windows binary version of PHP 5.2.3 does not work with MS SQL Server 2005. You need to get a new version of ntwdblib.dll.
One way to get a free copy of ntwdblib.dll is to download MS SQL Server 2000 SP4 from Microsoft Website. It contains a copy of ntwdblib.dll version 2000.80.194.0 in the system directory.
Copy ntwdblib.dll from SQL Server 2000 SP4 package to C:
twdblib.dll-new. Check the properties of this file, you will see the version is 2000.80.194.0. Enter this single-statement script, fyi-center.php, for testing:
<?php
mssql_connect('LOCALHOST','sa','GlobalGuideLine');
?>
28. What Happens If ntwdblib.dll Is Missing on Your Machine?
In order to install the proper version of ntwdblib.dll on your machine, you need to find out how many copies of ntwdblib.dll do you have on your machine and what are the version numbers on those copies.
1. Go to Start > Search > All files and folders. Enter ntwdblib.dll in the "All or part of the file name:" field and click Search. You should see only one copy:
ntwdblib.dll C:php
Check the properties of this copy, you will see its version is 2000.2.8.0.
2. Rename C:php
twdblib.dll to C:php
twdblib.dll-old. Run this script:
<?php mssql_connect('LOCALHOST','sa','GlobalGuideLine');
?>
You see this error message dialog box and an error in the command window:
Missing ntwdblib
PHP Warning: PHP Startup: Unable to load dynamic library
'./extphp_mssql.dll' - The specified module could not be
found.
in Unknown on line 0
Fatal error: Call to undefined function mssql_connect()
in C: estglobalguideline.php on line 2
This proves that there is only one copy of ntwdblib.dll on your machine. Read the next tutorial to see how to download the correct version of ntwdblib.dll for SQL Server 2005.
29. What Is Wrong with SQL Server Client Libarary DLL, ntwdblib.dll?
The second requirement to access SQL Server with PHP scripts is the SQL Server Client Libarary DLL, ntwdblib.dll. The good news is that the Windows binary version of PHP 5.2.3 has ntwdblib.dll included in the PHP home directory.
But the bad news is that the included copy of ntwdblib.dll is not compatible with SQL Server 2005. What makes it worse is that no specific error messages is returned from the PHP engine, php_mssql.dll, ntwdblib.dll, or the SQL Server. All you get is "Unable to connect to server: LOCALHOST", and you know that SQL Server is running properly on LOCALHOST.
If you read the PHP manual, it does mention that "copying ntwdblib.dll from winntsystem32 on the server (SQL Server machine) to winntsystem32 on the PHP box". This instruction is too short and causes the following confusions:
* Windows binary version of PHP 5.2.3 already has a copy of ntwdblib.dll included. But that copy is not good for SQL Server 2005.
* There is no ntwdblib.dll on the SQL Server 2005 Express Edition machine.
* You don't need to place ntwdblib.dll in winntsystem32. Placing it in PHP home directory is fine.
30. How to Turn on the MSSQL API Module in PHP?
If you want to access SQL Server database with PHP scripts, the first thing you need to do is to turn on the MSSQL API Module as shown in this tutorial:
1. Prepare a single line script to test the MSSQL API Module:
<?php
mssql_connect('LOCALHOST','sa','GlobalGuideLine');
?>
Run the script, you should get:
Fatal error: Call to undefined function mssql_connect()
in C: estglobalguideline.php on line 2
2. If you C:phpphp.ini does not exist, copy C:phpphp.ini-dist to C:phpphp.ini. Open C:phpphp.ini and remove (;) on the extension=php_mssql.dll setting:
;extension=php_mssql.dll
extension=php_mssql.dll
Run the script again, you may get:
PHP Warning: PHP Startup: Unable to load dynamic library
'C:php5php_mssql.dll' - The specified module could not
be found.
in Unknown on line 0
Fatal error: Call to undefined function mssql_connect()
in C: estglobalguideline.php on line 2
3. Open C:>phpphp.ini and change the extension_dir setting. The default setting is "./", but all extension module DLL files are in ./ext.
31. What Do You Need to Connect PHP to SQL Server?
If you want to access MS SQL Server in your PHP script, you need to make sure that:
1. MSSQL API module (extension) is installed and turned on in your PHP engine. If you installed the Windows binary version of PHP 5.2.3, MSSQL API module is included but not turned yet. See the next tutorial on turning on MSSQL API module.
If you are getting errors like: "Fatal error: Call to undefined function mssql_connect()", you know that MSSQL API module is not turned on correctly. See the next tutorial on turning on the MSSQL API module.
2. MS SQL Client Tools to be installed on the system where PHP is installed. The PHP manual also suggested an alternative, which is to get a copy of the SQL Server Client Libarary DLL, ntwdblib.dll. Again Windows binary version of PHP 5.2.3 contains a copy of ntwdblib.dll.
If you are getting errors like: "Unable to load dynamic library './extphp_mssql.dll' - The specified module could not be found", you know that the DLL, ntwdblib.dll, is not installed correctly. See the next tutorial on installing ntwdblib.dll.
32. PHP ODBC - How To Create an Identity Column?
Many tables require an ID column to assign a unique ID number for each row in the table. For example, if you have a table to hold forum member profiles, you need an ID number to identify each member. To allow SQL Server to automatically assign a new ID number for each new record, you can define the ID column with IDENTITY attribute as shown in the following sample script:
<?php
$con = odbc_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');
$sql = "CREATE TABLE ggl_users ("
. " id INTEGER IDENTITY NOT NULL"
. ", name VARCHAR(80) NOT NULL"
. ", email VARCHAR(80)"
. ", time DATETIME"
. ")";
$res = odbc_exec($con, $sql);
if (!$res) {
print("Table creation failed with error:
");
print(odbc_error($con).": ".odbc_errormsg($con)."
");
} else {
print("Table ggl_users created.
");
}
odbc_close($con);
?>
If you run this script, a new table will be created with ID column defined as an identity column. It value will be auto-assigned each time a new record is inserted with an auto-incremented number. The sample script below inserts two records with ID values assigned by SQL Server:
If you run this script, you will get something
33. PHP ODBC - How To Query Multiple Tables Jointly?
If you want to query information stored in multiple tables, you can use the SELECT statement with a WHERE condition to make an inner join. Assuming that you have 3 tables in a forum system: "users" for user profile, "forums" for forums information, and "posts" for postings, you can query all postings from a single user with a script as shown below:
<?php
$con = odbc_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');
$userID = 101;
$sql = "SELECT posts.subject, posts.time, users.name,"
. " forums.title"
. " FROM posts, users, forums"
. " WHERE posts.userID = ".$userID
. " AND posts.userID = users.id"
. " AND posts.forumID = forums.id";
$res = odbc_exec($con, $sql);
while ($row = odbc_fetch_array($res)) {
print($row['subject'].", ".$row['time'].", "
.$row['name'].", ".$row['title']."
");
}
odbc_free_result($res);
odbc_close($con);
?>
34. PHP ODBC - How To Perform Key Word Search in Tables?
The simplest way to perform key word search is to use the SELECT statement with a LIKE operator in the WHERE clause. The LIKE operator allows you to match a text field with a keyword pattern specified as '%keyword%', where (%) represents any number of any characters. Any single quote (') in the keyword needs to be protected by replacing them with two single quotes (''). The tutorial exercise below shows you how to search for records whose "notes" contains "e":
<?php
$con = odbc_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');
$key = "e";
$key = str_replace("'", "''", $key);
$sql = "SELECT id, url, notes FROM ggl_links"
. " WHERE notes LIKE '%".$key."%'";
$res = odbc_exec($con, $sql);
while ($row = odbc_fetch_array($res)) {
print($row['id'].", ".$row['url'].", "
. $row['notes']."
");
}
odbc_free_result($res);
odbc_close($con);
?>
If you run this script, you will get something like this:
102, www.GlobalGuideLine.com, Nice site.
202, www.google.com, It's another search engine!
301, yahoo.com, Added long time ago!
302, myspace.com, Added today!
35. PHP ODBC - How To Display a Past Time in Days, Hours and Minutes?
You have seen a lots of Websites are displaying past times in days, hours and minutes. If you want to do this yourself, you can use the DATEDIFF() SQL function The following tutorial exercise shows you how to use DATEDIFF() to present a past time in days, hours, and minutes:
<?php
$con = odbc_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');
$submit_time = "2007-05-29 04:09:49";
$sql = "SELECT 'Posted '"
. " + CONVERT(VARCHAR(40),"
. " DATEDIFF(minute, '$submit_time',"
. " GETDATE())/(24*60))"
. " + ' days, '"
. " + CONVERT(VARCHAR(40),"
. " DATEDIFF(minute, '$submit_time',"
. " GETDATE())%(24*60)/60)"
. " + ' hours, and '"
. " + CONVERT(VARCHAR(40),"
. " DATEDIFF(minute, '$submit_time',"
. " GETDATE())%60)"
. " + ' minutes ago.'";
print("
$sql
");
$res = odbc_exec($con, $sql);
if (!$res) {
print("SQL statement failed with error:
");
print(odbc_error($con).": ".odbc_errormsg($con)."
");
} else {
odbc_fetch_row($res);
$message