Hey everyone!
After some futzing about, and using techniques you guys all helped out
with, I now have a nice, portable, fully-commented-for-future-maintenance
script for doing *all* of my SQL queries to our MSSQL server, and
outputting a nice, easy-to-read table. I only have to change one comment at
the beginning, then the variables at the top of the script. Quite a bit
easier to maintain that my old script (which most of you saw some of the
other day). The script has been Sanitized, Homogenized, as well as
Pasteurized and Anonymized For Your Protection (feel free to break the
paper seal...) as well as mine. :)))
Now my next trick is to have the same query output to a
downloaded-to-the-client-PC CSV file, but I just need to read more on
that.. I have the CSV output, just have to tuck it into a plain/text CSV
stream.. it will be fun to figure out!!! :)))) As I said to one of my
coworkers last night when I got this done, "There is now much joy in
Lumetaville..." :) hehe
In gratitude to the community who helped me build it, here's my script,
which was originally snagged from a php-coding website that I visited
somewhere in my travels, and unfortunately forgot which one. The script
kept the "SelectQuery" function name and parameters from the original
script. Since then, it has been mangled and coded and recoded and such by
myself, with the help of the people here and at my office.
The script now:
1) Automagically figures out how many fields are in the query,
2) Feeds the header information from those fields into the table,
3) Checks to see if any data cells are blank, and if so, throws in
a ,
4) Alternates the colors by company name.
5) Produces "pretty" HTML source.
The whole thing is orders of magnitude more efficient than the original
script was, and orders of magnitude easier for me to use to create new
pages with!
In addition, if you have certain variables set, it will:
1) Only print each company name once,
2) Format any "Price" fields as being in dollars as well as
right-aligned,
3) Gives you a link to a similarly-named page that will have a
CSV-formatted file (this is coming soon.. not done yet! hehe) downloaded to
your PC with the data from the table.
I hope people find this useful. It's a good way of being able to query a DB
and just dump the data into a table. I've learned a lot in this exercise,
not the least of which was the fact that associative arrays are handled
differently than regular arrays (and here I thought the mssql_fetch_assoc
just didn't work LOL...), as well as when it's "more right" to use if
clauses instead of ternary operators, for the sake of ease of reading. Feel
free to use, abuse, gut, trash it, whatever you'd like :)
Again, thanks, everyone :)
Sincerely,
Glenn
(note... breaking the php tag so it'll show... instead of executing (I hope))
< ? php
/*
* This script performs a query on the MS SQL Server, against a database.
*
* This will show us <put what this script is showing us here>!
*
* Version 1.12 5/11/02
* ges with the help of ryan/hburch/bunches of people on php-general!
*
* Here we set up variables that change for the page....
*
* Query Parms:
* $fields - comma separated list of field names or "*".
* $tables - comma separated list of table names.
* $where - SQL Where clause (e.g. "id=2").
* $groupBy - SQL Group clause (e.g. "name").
* $orderBy - SQL Order clause (e.g. "name").
* $show_debug - If true then print SQL query.
* $filter_company - If true, then only shows the first
instance of a company's name
* $filter_dollars - If true, then formats any "price" colums
to be $'s and such.
*
* Page Parms:
* $title - What the name of this page is. Echoed in <title>
and <h3> below.
* $subtitle - Further information regarding the page. Echoed
in <h3> below.
* $csv_page - If true, prints the line that offers the user
a CSV version of the page.
* $page_name - Set to the filename of this page, minus the .php
* Returns:
* 2d array of rows and columns on success.
* Error String on failure.
*/
$fields = "Enter the fields to query" ;
$tables = "From which tables" ;
$where = "conditions on match" ;
$groupBy = "grouping by" ;
$orderBy = "sorting by" ;
$show_debug = False; # True or False
$filter_company = False; # True or False
$filter_dollars = False; # True or False
/*
* Page Parms
*/
$title = "Title of page here";
$subtitle ="Subtitle of page here";
$csv_page = True; # True or False
$page_name = "pre-extension portion of the name of the php file here"; #
example "Testing" instead of "Testing.php"
/*
* Here we set up the page...
*/
print
"<HTML>\n\n".
"<HEAD>\n\n".
"<TITLE>$title</TITLE>\n\n".
"</HEAD>\n\n".
"<BODY>\n".
"<H3>$title</H3>\n\n";
if ($subtitle !== "") print ("<H4>$subtitle</h4>\n\n");
print
"<HR>\n\n";
/*
* Now we set up the "last updated" part of the page...
* This is to give users a warm feeling about when the query on their
page was run.
* As well as showing them the link back to the main list of queries,
and whether
* or not there's a CSV version of this file available.
*/
print
"Last Updated: ".
(date ("l ")).
"the ".
(date ("dS of F Y h:i:s A")).
"<P><P>\n\n";
if ($csv_page !== False) print ("For the CSV fomatted copy
of this table, click <A HREF=\"$page_name-csv.php\">here</A><BR><BR>\n\n");
print
"<A HREF = \"http://our-internal-webserver-name/SQL/\";>".
"Click here for the Main Menu</A><P>\n\n";
/*
* Now let's define some variables in regards to the SQL server...
*/
define (DB_HOST,"hostname of the MSSQL Database");
define (DB_NAME,"name of the MSSQL database we're connecting to");
define (DB_USER,"MSSQL Username");
define (DB_PASS,"MSSQL User's Password");
/*
* Ok--now we define the SelectQuery function, to be called later!
* We pass to it the variables defined earlier in this script
*/
Function SelectQuery($tables="", $fields="", $where="", $groupBy="",
$orderBy="", $show_debug=False, $filter_company=false, $filter_dollars=false)
{
$header_color="#eeddff"; # A pleasant light purple
$row1_color="#ffffff"; # Standard white
$row2_color="#ffffc0"; # Light mustard yellow
/*
* First we connect to the MS SQL server!
*/
$db = mssql_connect(DB_HOST, DB_USER, DB_PASS) or DIE("DATABASE
FAILED TO RESPOND.");
/*
* Now we declare $values to be an array to capture the data!
*/
$values = array();
/*
* Now let's build the query! We use the ternary operation ?: here!
(Thanks Ryan!!!)
*/
$query = "select $fields from $tables ";
$query .= empty($where) ? '' : " where $where ";
$query .= empty($groupBy) ? '': " group by $groupBy ";
$query .= empty($orderBy) ? '': " order by $orderBy ";
/*
* Ok--now if we have $show_debug defined as True above, we echo the
Query we just built!
*/
if ($show_debug !== False) echo("query=$query<br>\n");
/*
* Now we execute the query as defined above
*/
$stmt = mssql_query ($query, $db) or DIE("Table unavailable");
/*
* Now we create a table for this data to be read into...
*
* First we read how many fields there are in the table itself...
*/
$head = mssql_num_fields ($stmt);
/*
* Now we create a TH (TableHeader) entry for every field in the table
*
* Note: PHP starts at 0, MSSQL starts at 1, so we need to make sure
that $i never gets to be greater than
* or equal to $head
*/
print ("<TABLE BORDER=1>\n\t<TR BGCOLOR=$header_color>\n");
for ($i = 0; $i < $head ; $i++)
{
$field = mssql_field_name ($stmt);
$fieldname[$i] = $field;
if ($filter_dollars == True)
{
if ($field == "Price") $alignment='RIGHT';
}
else $alignment='LEFT';
print "\t\t<TH ALIGN=$alignment>$field</TH>\n";
}
print ("\t</TR>\n");
/*
* Time to set some variables to be called later. $sPrevCompany is
set to NULL to ensure we aren't using it yet.
* $sColor is set to be 'FF' so that the original Table Row
background color will end up being Yellow, not white.
*
*/
$sPrevCompany = '';
$sColor = '#ffffc0';
/*
* Ok, now let's fetch the SQL data from the server in an Associative
Array
*/
while ($line = mssql_fetch_assoc($stmt))
{
/*
* Now we set $sCompanyName to be the current Company name from the Query
*/
$sCompanyName = $line["Company"];
/*
* If our previous company name is not the same as this one, change
the color and print
* the company name.
*/
if ($sPrevCompany !== $sCompanyName)
{
if ($sColor == $row1_color) $sColor =
$row2_color;
else $sColor = $row1_color;
$sPrevCompany = $sCompanyName;
} else {
/*
* If we have set $filter_company = True, above, then here we will
tell the script not to print it again
*/
if ($filter_company == True) $line["Company"] =
' ';
}
/*
* If we have set $filter_dollars = True, above, then here we check
for "Price" and format it, and the Table Data, accordingly.
*/
if ($filter_dollars == True)
$line["Price"] = "$" .
number_format($line["Price"],2,'.',',');
/*
* Now let's make it so if any of the cells are blank, we put in a
' '
* Otherwise, it just prints the data present in the cell.
*/
print "\t<TR BGCOLOR=$sColor>\n";
for ($i = 0; $i < $head; $i++) {
$value = $line[$fieldname[$i]];
if (strcmp($value,NULL) ==
0) $value = " ";
if
($filter_dollars ==True)
{
if
(strcmp($fieldname[$i],"Price") == 0) $alignment = 'RIGHT';
}
else $alignment = 'LEFT';
print "\t\t<TD
ALIGN=$alignment>$value</TD>\n";
}
print "\t</TR>\n";
}
print ("</TABLE>\n");
/*
* Ok, we're done here, let's close the connections
*/
@mssql_free_result ($stmt);
@mssql_close($db);
/*
* And let's pass the array back to the main program!
*/
return $values;
}
/*
* Now let's call the function, passing all the variables needed...
*/
$ges =
SelectQuery($tables,$fields,$where,$groupBy,$orderBy,$show_debug,$filter_company,$filter_dollars);
/*
* And that's it! Finito! Let's close up shop....
*/
print ("<HR>\n</BODY>\n\n</HTML>\n")
? >
---
The original portions of this message are the copyright of the author
(c)1998-2002 Glenn E. Sieb. ICQ UIN: 300395 IRC Nick: Rainbear
"All acts of Love and Pleasure are Her rituals"-Charge of the Goddess
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
