#native_company# #native_desc#

MS SQL & php4 : a complete example

By Thomas Trainer
on September 10, 2001

Version: 1.1

Type: Full Script

Category: Databases

License: GNU General Public License

Description: Tested in W98/WinNT/WinNTserver! Shows a complete forms-to-php (self posting) MSSQL query. Notice the stored procedure ALSO works, but for some reason won’t show the COMPUTE avg(price) figures in reptq1 (which I’ve internally replaced with ‘xxx’)

function doit() {
# set variables global so this function can see them
	global $svr, $uid, $pwd, $db, $tbl, $SQL;
	$cn = mysql_connect($svr, $uid, $pwd) or die("$svr won't talk to me!");

# perform the query and then get # of rows and columns returned.
	$rs = mysql_query($SQL);
	$nRows = mysql_num_rows($rs);
	$nCols = mysql_num_fields($rs);
	if($nRows == 0) die ("<br>No data found!<br>n");
	echo "<b>Total of ".$nRows." rows and ".$nCols." columns returned.</b>n";

#	create a simple HTML table with headers and data.
	echo "<table cellpadding='1' cellspacing='0' border='0'>n<tr><th>No</th>";
	for ($i = 0; $i <= $nCols - 1; $i++) {
		$fName = mysql_field_name($rs, $i);
		echo "<th>", $fName, "</th>";
	echo "</tr>n";

	for ($i = 0; $i <= $nRows - 1; $i++) {
#	Do a cool little altering background color.
		$rc = (($i/2 - intval($i/2)) > .1) ? "e0e0e0" : "f0f0f0";
		echo "<tr bgcolor='", $rc, "'><td><b>", ($i + 1), "</b></td>";
		$k = mysql_fetch_row($rs);
		for ($j = 0; $j <= $nCols - 1; $j++) {
			$k9 = $k[$j];
			if (!$k9) $k9 = "xxx";
			echo "<td>", $k9, "</td>";
		echo "</tr>n";
	echo "</table>n";

if (isSet($svr)) {
} else {
#	We're posting to self, so if first run, set the variables.
	$svr = "server";     # name of your MY SQL server
	$uid = "userid";      # user id and password
	$pwd = "";
	$db  = "dbname";    # database name
	$tbl = "tablename"; # name of your table
	$SQL = "select * from ".$tbl; # the SQL select statement
<style type="text/css">
th, td, body {
	font-family: verdana;
	font-size: 0.65em;
	padding-left: 10px;
<script language="javascript">
function sw(a,b,c,d,e,f) {
	document.f.svr.value = a;
	document.f.uid.value = b;
	document.f.pwd.value = c;
	document.f.db.value  = d;
	document.f.tbl.value = e;
	document.f.SQL.value = f;
<body bgcolor="#f0f0f0">
<form name="f" method="post">
<input type="text" name="svr" value="<?=$svr?>"><br>
<input type="text" name="uid" value="<?=$uid?>"><br>
  <input type="password" name="pwd" value="<?=$pwd?>">
<input type="text" name="db"  value="<?=$db?>"><br>
<input type="text" name="tbl" value="<?=$tbl?>"><br>
  <input type="text" name="SQL" value="<?=$SQL?>" size="70">
<input type="submit">
<a href="#" onClick="sw('bob','sa','','a1','rtPrices','select * from rtPrices'); return false;">bob/rtPrices</a><br>
<a href="#" onClick="sw('bob','sa','','pubs','authors','select * from authors'); return false;">bob/authors</a><br>
<a href="#" onClick="sw('bob','sa','','pubs','titles','exec reptq1'); return false;">exec reptq1</a>