MS SQL Server

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.

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

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.

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);

?>

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.

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

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.

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

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

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

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);

?>

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);

?>

If you want to change the owner of a schema, you can use the "ALTER AUTHORIZATION" statement using the following syntax:

ALTER AUTHORIZATION ON SCHEMA::schema_name TO user_name

The following tutorial example shows you how to change ownership of schema "ggl" to "ggl_user":

-- Login with "sa"

USE GlobalGuideLineDatabase;

GO

ALTER AUTHORIZATION ON SCHEMA::ggl TO ggl_user

GO

SELECT s.name, u.name AS owner

FROM sys.schemas s, sys.database_principals u

WHERE s.principal_id = u.principal_id;

GO

name   owner

------------------- --------------------

dbo    dbo

ggl    ggl_User

guest   guest

...

If you want to delete a schema, you need to move all objects out of that schema, then use the "DROP SCHEMA" statement to delete the schema. The tutorial exercise below shows you how to drop schema "ggl":

-- Login with "sa"

USE GlobalGuideLineDatabase;

GO

-- Drop failed because schema is not empty

DROP SCHEMA ggl;

GO

Msg 3729, Level 16, State 1, Line 1

Cannot drop schema 'ggl' because it is being referenced

by object 'DF__ggl_links__creat__4316F928'.

-- Move one table out

ALTER SCHEMA dbo TRANSFER ggl.ggl_links;

GO

-- Delete one table

DROP TABLE ggl.test;

GO

-- Dropped ok

DROP SCHEMA ggl;

GO

Command(s) completed successfully.

A result set object is a logical representation of data rows returned by odbc_exec() 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:

* odbc_free_result($res) - Closes this result set object.

* odbc_num_rows($res) - Returns the number rows in the result set.

* odbc_num_fields($res) - Returns the number fields in the result set.

* odbc_fetch_row($res) - Moving the internal pointer to the next row.

* odbc_fetch_array($res) - Returns an array contains the next row indexed by filed names. The internal pointer is moved to the next row too.

* odbc_fetch_object($res) - Returns an object representing the next row. The internal pointer is moved to the next row too.

* odbc_field_len($res, $1) - Returns an array contains lengths of all fields in the last row returned.

* odbc_field_name($res, $i) - Returns the name of the field of the specified index.

* odbc_result($i) - Returns the value of the field specified by its position from the current row.

* odbc_result($name) - Returns the value of the field specified by its name from the current row.

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.

<?php

$con = odbc_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');

$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 (!odbc_exec($con, $sql)) {

print("SQL statement failed with error: ");

print(odbc_error($con).": ".odbc_errormsg($con)." ");

} 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."')";

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!

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);

?>