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>
