#native_company# #native_desc#
#native_cta#

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

<?php
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!");
	mysql_select_db($db);

# 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";
	mysql_close($cn);
}

if (isSet($svr)) {
	doit();
} 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
}
?>
<html>
<head>
<style type="text/css">
th, td, body {
	font-family: verdana;
	font-size: 0.65em;
	padding-left: 10px;
}
</style>
<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;
}
</script>
</head>
<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?>">
  <br>
<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">
  <br>
<input type="submit">
</form>
<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>
</body>
</html>