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


(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...

                 if ($subtitle !== "") print ("<H4>$subtitle</h4>\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.
         "Last Updated: ".
         (date ("l ")).
         "the ".
         (date ("dS of F Y h:i:s A")).

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

         "<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 
  *      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 
         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 = 
                                 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"] = "$" . 
  *      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 = "&nbsp;";
($filter_dollars ==True)
(strcmp($fieldname[$i],"Price") == 0) $alignment = 'RIGHT';
                                                 else $alignment = 'LEFT';
                                                 print "\t\t<TD 
                                         print "\t</TR>\n";
                         print ("</TABLE>\n");

  *      Ok, we're done here, let's close the connections
         @mssql_free_result ($stmt);

  *      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 = 
  *      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

Reply via email to