Statistics

A useful book for getting more advanced functionality from Excel is Microsoft Office Excel Step-by-Step ; another is F1 Get the Most out of Excel, e.g., Tip 177: "Summing Values from Cells in Different Sheets" (both via Books24x7). See also catstats page.

MySQL & PHP (NELINET)

NELINET course: Introduction to MySQL , taught by Ed Sperr

Most steps and examples on handout. Commands should work in all versions of SQL, including MS-SQL (i.e., in Access).

Starting with MySQL 5.0 command line client, enter password, then:

show databases; [all commands end with semi-colons]

Then, start Apache 2 (here, activate dormant icon on system tray.)

Then, browser address bar: localhost/pma to get php myadmin, allows us (like command line) to talk with backend database. This serves as GUI interface, almost OPAC-like, i.e. "using Web browser to make request to apache server".

USE databases; (or Use wikidb;, etc.)

[here: plus sign denotes mysql command prompt]

UP ARROW show earlier commands
+show databases;
+drop ....; [actually deletes the thing from harddrive]

Next step: create our own db:
+create database mylibrary;
+use mylibrary;
+create table characters (name varchar(30),occupation varchar(20));

+show tables;
+describe characters;

Or use phpmyadmin, refresh, select mothergoos database on left column. Data dictionary shows schema of table.

So far table is defined, but still empty.

+INSERT INTO table_name (col_name,...) VALUES (value,...)

+INSERT INTO characters (name, occupation) VALUES ("Mary", "shepherdess");

Computer response: Query OK, one row affected.

To retrieve data from table ("if there's one thing you should take away from this class ...") :

+SELECT select_expr FROM table_name

New example
+create database mylibrary;
+use mylibrary;
We're going to store:
Title (as "title")'
Publication Information (as "pubinfo")
Author's last name (as "lname")
first name (as "fname")
Authors Dates (as "adates")
Publication Date (as "pdate")

Hint: CREATE TABLE table_name(field_name data_type(length),...)

+CREATE TABLE books (title VARCHAR(255), pubinfo VARCHAR(255), lname VARCHAR(255), fname VARCHAR(255), adates CHAR(9), pdate CHAR(4));

If mysql prompt disappears, try several carriage returns, but may need to close and reopen command line client.

+insert into books (title, pubinfo, lname, fname, adates, pdate) values ("Moby Dick, or, the Whale", "New York; London: Penguin, 2003.", "Melville", "Herman", "1819-1891", "2003");

Remember that double quotes escapes all the characters within, including single quote. Backslash is another escape character

Inserting records from comma and tab delimited text files.-->
+load data infile 'T:/classfiles/mysql/books.txt' into table books;

Then
+select * from books; [to retrieve all data]

MySQL 5.0 manual

Make sure fields are put in the right order, so: "select title [etc.] from books;" ("'Select' is bread and butter command") Much better display in phpadmin, selecting db, then sql tab, then "sel * from books" [this produces cool OPAC-like display]

New topic: "Aggregate functions", math-oriented transactions,

e.g., SELECT select_expr, agg_func(expr) FROM table_name GROUP BY field

GROUP BY means: grouping like things together, again easier to see in phpmyadmin. What if we wanted to count number of books by each author. E.g., everything written by S. Clemens ...

SELECT fname, lname, COUNT (title) FROM books GROUP BY lname;

Put in alphabetical order?

• ORDER by expr


• SELECT fname, lname, COUNT (title) FROM books GROUP BY lname ORDER by lname;

WHERE Clauses

• SELECT select_expr


• FROM table


• ex.: SELECT lname, fname, title FROM books WHERE lname = "Twain";


• What copies/versions of Scarlet Letter to we have?.: SELECT * FROM books WHERE title LIKE "%Scarlet Letter%"; will pull up all titles with the phrase "Scarlet Letter" in title field. % = wildcard. Backslash before would make the % literally read as a percentage sign.


• Which nathaniel Hawthorne books were published after 1960? SELECT title FROM books ...

Relational Database

• Common problem with single table db, is waste of space, redundancy of data values, increase risk of data entry error. E.g., address book, where almost everyone has "Main Library" and "567 Campus Rd.". So table one would have "Last name, First name, and Extension and Table Two would have Library and Library address. Need to add keys to relate them together, e.g., sequentially numbered rows.

How would you set somthing like this up if you're a collection deve librarian keeping track of vendor gifts and contacts?

• CREATE TABLE vendors (vendor VARCHAR (100), ADDRESS varchar(255), phone CHAR(14));


• And another table for reps: CREATE TABLE reps (lname VARCHAR(50), fname VARCHAR(50), phone ChAR(14), gift VARCHAR(255));


• Then, to save time: LOAD DATA infile 'T:/classfiles/mysql/vendors.txt' INTO TABLE vendors; LOAD DATA infile 'T:/classfiles/mysql/reps.txt' INTO TABLE reps;


• To see all gifts brought by reps: Select * from reps;


• To see all addresses of vendors: Select * from vendors;
• To add field to tables: ALTER TABLE table_name [ADD/CHANGE/DROP] column_name column_definition;


• to add numeric keys: e.g. ALTER TABLE vendors ADD id int; [int=integer, no need to specify field length]


• To make it auto-increment, first remove previous: ALTER TABLE vendors DROP id; then ALTER TABLE vendors ADD id int not null auto_increment primary key FIRST;
• SELECT * [everything] from vendors;


• Another table: ALTER TABLE reps ADD vendor_id int;


• DESCRIBE reps;


• So Tom Stevens works for Ingram. Need to link tables. Ingram is record 2. So put 2 in vendor id slot for Stevens. Need to update table manually.: UPDATE table = name SET column_name-new_value_or_expression WHERE where_condition;
• SO: UPDATE reps SET vendor_id = 2 WHERE lname = 'Stevens';


• Then: SELECT * from reps; (now Rep Stevens has vendor ID of 2).


• Add these: Jane Smith works for Baker and Taylor (1) Joan Drake works for YBBP (4); Vera Miles works for Rittenhouse (3)


• Digression: If you need to change data type use ALTER TABLE: e.g., pubdate should have been data type 'year': ALTER TABLE books CHANGE pubdate pupdate year;


• SELECT * FROM reps;


• We now have relationship between two tables.


• Which rep brought the pens? SELECT fname, lname FROM reps WHERE gift LIKE "%pens%";

Joins

SELECT select_expr FROM table 1 JOIN table2 ON (table1_field-table2_field) WHERE where_condition

or

SELECT vendor FROM vendors JOIN reps ON (id = vendor_id) WHERE gift LIKE "%pens%";

or

SELECT vendor FROM vendors, peps WHERE (id - vendor_id And gift LIKE '%pens%');

Add more data ...

INSERT INTO reps (lname, fname, phone, gift) VALUES ("Delor", "Jacques", "(617)525-8412", "Big wheel of stinky brie"); INSERT INTO reps (lname, fname, phone, gift, vendor_id) VALUES ("Sneed", "Shanna", "(508)522-1567", "Old ALA \"Read\" Posters", 3);

If I want to see all information about people who work at Rittenhouse: SELECT * FROM reps JOIN vendors ON (id = vendor_id) WHERE vendor = "Rittenhouse";

To see everybody: SELECT * FROM reps JOIN vendors ON (id = vendor_id);

JOIN = INNER JOIN

But what about Jacques (who's currently unemployed)? SELECT * FROM reps LEFT JOIN vendors ON (id = vendor_id);

What happens with SELECT * FROM reps RIGHT JOIN vendors ON (id = vendor_id); Jacques doesn't show up because he doesn't appear in second table.

With authors spelled differently, another table for authority control.

To do this, get rid of Books table: DROP TABLE table_name;

New one:

CREATE TABLE books(id int primary key, title VARCHAR(255), pubinfo VARCHAR(100), author_id int);

CREATE TABLE authors (id int primary key, lname VARCHAR(255), fname VARCHAR(100), author_id int);

LOAD DATA INFILE 'T:/classfiles/mysql/authors.txt' INTO TABLE authors;

Then,

select * from authors, books where authors.id = author_id;

MySQL more secure than MS Access? Tools exist to convert one from the other. Another option: export as tab-delimited text file. Keep in mind that Access designed to be personal, part of MS Office, whereas MySQL built from bottom up for Web environment.

Grab apache, php, etc., files from XAMPP, not for production, but good for sandbox playing around. MySQL is backend silo where data live. Scripting langauges allow us to communicate with them e.g., PHP, PERL, Python, ColdFusion to auomate tcomman language,

PHP 11/15/06

"Hypertext pre-processor"

Helper applications PHP MyAdmin and MySQL Command line client. These are ways of communicating with black-box MySQL running in background. Today, more sophisticated way to do the same thing. Scripting language PHP instead of command line and MyAdmin. Why? Relatively simple. OSS. Best way to learn: look at code other people post on message boards. Designed from ground up specifically for building Web applications. Interpretated language, i.e., does not need to be compiled (e.g., C++), so changes show up immediately, instant gratification. Also: Iterative.

Static Web page = simple file request. PHP (installed, along with Apache, on Web server) mediates the request. php.net documentation "is fabulous".

"Follow the bouncing ball" metaphor: "Hello World" [?php echo 'Hello World!; ?]

C:-->Program Files-->Apache Group-->Apache Group-->htdocs, where Apache installs itself.

Basic php command is "Echo". Similar to "Print" command. Followed by argument: 'Hello World' . Result is no longer static text, but rather output of a compmand. "You have just gone through the rabbit hole (or looking glass)."

Variables (Or, PHP Madlibs). "Jack and [] went up the [] to fetch a [] of water ..." Could use this technique to collect input in Web form, then sent it to SQL as structured query, and return results as HTML output. Single quotes are literal, double quotes or no quotes are variable. Period stitches things together.

FYI: Scintilla seems like good text editor.

Loop command: Keep doing operation until given condition has been met. For loops establish set of starting conditions, est., what is to change, est. assessment for when conditions are met; this concept common to all programming languages. E.g.: ($i = 0; $i while ... uses same concept but structured differently, with php commands in separated code-blocks, says "Do this WHILE this is the case".

If statement: If variable favorite color is blue do what's in brackets, otherwise "else", i.e., do other bracketed echo (very similar to PERL command). Quotation marks around html attributes require backslash escape characters.

Date function: asks the system "What's the time?" There are lots of built-in PHP gfunctions; and one can create or call other functions.

multiplier.php file shows mathematical functions, and multiplier2.php shows for loop through 13. "Leveraging power of 'for-loop' to save ourselves time." See form: "multiform.html", interesting part is "form action=" in this case "from action 'multiback.php' method= 'get' "

Arrays

Similar to variables, but convenient structure for storing multiple related values, require keys and values, where keys can be integers ("0", "1", "2", etc.) or names (e.g., "Title", "Author") ... Result of simple array looks like table with key in column one and value in column 2.

Easiest way to creat new database is to drop new data folder into MySQLData folder.

In order to use PHP to communicate with SQL db, need to do the following:
(1) connect to DB,
a. use built-in function called mysql connect: myql_connect(host, user name, password
b. mysql_select_db

(2) run query (pass sql query to a function),
a. mysql_query(sqlquery)

(3) retrieve the results,
a. mysql_fetch_assoc(resource_result) [fetches set of arrays reprenting rows of a database)]


(4) then format (parse) them for display (at which PHP really excels).

EXAMPLE:

search_step1.php ...

beware sql injection attacks. Final example iteration shows safety feature.

MS Access (Level I)

Learning Center Course on Access 2003 Level I , taught by Sam Eskridge (help desk 5-3200).

First create folder in mydocuments, then copy 7 folders from Z:\Access\Access 2003\Level 1 to that folder. Open concepts.mdb from unit 1 folder in Access. [Problem with sound bleeding through from Pathways next door. ] Four objects to be covered in Level I: Tables, Queries, Forms, Reports. Pages and Macros come up in Levels II and III.

Modules are VB script, beyond scope of Access courses at Learning Center.

We're going to work with "Outlander Spices" retail database model. Primary key of table is product ID. These numbers are not recycled.

Database Window shows all objects in DB.

Menu-->View-->Task pane. New feature in Access 2003. Conext sensitive. Includes list of recently opened files.

Much of the first hour is exceedingly basic. More specific to Windows Office than to Access. How to use Menus, Office Assistant, etc.

Unit 2: Consider drafting process map or flow-chart before building database.

"Project" allows Access to be used as front end to MySQL or other backend databases. Try "From existing file" to modify pre-existing DB. Try "Templates" on "My computer" and Databases tab. --> "Order Entry". Rename "Order Entry1.mdb" to "Outlander_spices", save in Unit 02 folder by clicking "Create". Follow steps in "Database Wizard." Select ... Templates are fully developed databases, so may get you 80% of the way to where you want to be. Good option to consider.

My Company information form pops up during installation, see p. 2-6 in booklet.

Forms Switchboard ...

p. 2-7: Creating DB from scratch ... "CreateDatabase.mdb" . Access2000 file format allows the many non-upgraded users to work with dtabase. This is default setting.

Shift+Enter = Save, without moving forward, i.e., to avoid creating new record.

Design view (versus datasheet view):
includes columns on Data type (e.g., text, currency), field name, description, with data properties (including captions) displayed at bottom.

[10-minute break]

Create new DB

Use wizard--> click New in Design view. --> select "Table Wizard" to create populated DB. Business--> Sample Tables--> "Employees and Tasks". Single arrow pulls selection into "My new table". Double arrow pulls all fields over. Note importance of unique field names (system appends numeral 1 to end of duplicated field name).

Rename table: "tblEmployeeTasks". [This sets us down wrong path] "No I'll set primary key"--> EmployeeID-->Enter data directly into table. [By making Employee ID primary key, only able to assign one task to any given employee. Better: "Employee Task ID" representing the combination, allows one-to-one, one-to-many,many-to-one, etc. options. Switch to design view.

MS can generate autonumbers up to 1.4 billion.

Keep in mind that telephone and SS numbers, etc., are given datatype "text" not "number" since these are non-calculable numbers. Don't want dashes to convert to mathematical notation.

Field naming conventions indicate, but don't determine datatype. Not necessary to use, but can be helpful. Use captions in field properties in order to diplay more user-friendly names.

Memo-notes can store 30 pages of size-12 text.

Note smart tags, context-sensitive drop-down menus. Good for making global changes.

Second session: Nov. 9, 2006

Picking up at session 3, pg. 7, opening database page, tbEmployee table, looking at Dept column (field) with Dept. code (which will link to a separate Dept. table). Change AT code to AC. Several techniques to do this globally ... Click find button (binoculars icon) to open "Find and Replace" dialog box. "Look in field" will automatically fill-in where-ever insertion point was resting.--> Find AT--> Replace with AC--> Replace all. [Could be handy in case we change instances of department name in db-driven Web site]

Open tblDept, in this case, where codes and department names are arranged side-by-side, so they only have to be recorded once in db. For spell-checker, select entire field (becomes highlighted), and click button. But remember: "Dew knot trussed yore document to spell checque".

Shift + Enter. THen close table.

Open tblEmployee (includes names, HR#, earnings, etc.. ) "Horizontal Inegrity" principle means that re-sorting one field causes resorting of every field. This is unlike Microsoft Excel (so says Sam), which sometimes sorts single field only. But sorting thorugh database menu will impose horizontal integrity.

What happens when you select two columns and then sort? Both fields get sorted, with secondary one to right "mutual sort". If fields not adjacent, then possible to do same thing by constructing a query.

Filter indicated by funnel button. Select cell, then "filter by selection", to get extract from table.

Fildter by form. Selct any record ... Table replaced by search form ... note drop down arrow to get combo box, with list of all unique values in this field. Select one of these, then silver funnel button.

May also write criterion in form oneself, e.g., "Earnings" field: ">50000".

Deleting record button

Unit 4: Data Entry Rules

Note: "Record Navigator" is tool bar at bottom.
Setting field properties, changing these, e.g., "Required" will cause Access to test integrity of data in each record. But what happens whn property is required, but agent simply doesn't have data to put here (e.g., no fax number)? Then , "Allow zero length" property set to "yes." this is equivalent to N/A, just hit space bar.

"Field size property" and "Bounce tone".

4-10: Input-mask characters. e.g., enter 2034325660: Displays as (203) 432-5660. Punctuation is called "literal characters" and digits are called "values". "Input-mask characters" are given in table on this page. Set input mask for phone field: "(999) 000-0000;0;#". Input-mask field also provides "builder" button, which in this case activates "Input-mask wizard". Then tab, then "try it".

"Default values" property, e.g., "Portland" for "city" saves keystrokes for frequently used terms.

"Validation Rule" property: enter statement in SQL: "Like "* ox" Or Like "* lb" (for bulk chives record). Then add "Validation Text": Unit values must be oz or lb.

Unit 5: Create Queries

Query produces "Record set". Selcet "Make-Table Query" as query type in order to create entirely new (self-standing) table.

[BREAK]

Query -- double click fields from table, click red exclamation point to "run" . Extremely simple. SQL running in background of all db's, but Access can write it for us.

Unit 6: Using Forms

Section headers. Add form footers. Pull up or down like curtain. Click in Header, select Aa in toolbar. Place cross-haris where desired, and left mouse button drag to create desired area for label.

Easiest way to create a form: Tables page, select table, select New Object button arrow to right, click down arrow, and select "Auto form". Access will pull all fields from table and put into form.

Unit 7: Reports

Written off of tables or queries. Reports are updated dynamically when tables are changed. "Reports designed once can write themselves."

Computing (MySQL, etc.)

[2005-06-12]
Reminder: Dynamic Drive site has script for opening links in new window.

Categories: New_Horizons, PHP, MySQL, PERL, Databases

bin.yale.edu
"provides users with access to a variety of scripting and programming tools [e.g., MySQL]. The goal is to allow users to experiment with development tools that are not available on other institutional servers." But there's a caveat: "can be overkill for small data sets that do not change frequently. If you are unfamilair with SQL, but are comfortable with a scripting language such as PERL, it may be easier to store your data in a tab-delimited file" (from http://bin.yale.edu/mysqlover.html).

Categories: MySQL


There's a list of PERL Modules installed on bin.yale.edu (with several broken links). There's a PERL FAQ page, and a URL for downloading latest release. How to access MySQLthrough BIN account. Note MySQL Reference Manual for version 3.23.41 available on Yale site.

Server specifications
bin.yale.edu is a SPARCstation 20, running SunOS 5.8, with Apache 1.3.14
httpd server installed. Documentation on the Apache server is at
http://httpd.apache.org/

How to access bin.yale.edu:
Connect to the server bin.yale.edu using your NetID and password. You can
use any of a number of methods to log in and to transfer files. All of
these methods protect your password.

Logging In
You can log in to bin.yale.edu using either Kerberized telnet or Secure
Shell (SSH). Clients for Windows, Macintosh, and Linux are available at
http://www.yale.edu/software/network/secure/

Transferring files:
You can transfer files either using SAMBA or Secure Shell File Transfer
(SFTP) under Windows, and Secure Copy (scp) or Secure Shell File Transfer
(SFTP) under Unix, and Kerberized FTP (KFTP) for the Macintosh. More
information on secure file transfer can be found at
http://www.yale.edu/webmaster/secure-ftp.html

Once you have connected:
Upon connecting you'll find yourself in your home directory:/export/home/NetID

Within this home directory is a subdirectory,
public_html, which is the directory from which web pages, including web
pages generated by scripts, will be served. Within the public_html
subdirectory is in turn a directory called cgi-bin, into which scripts
should be placed.

The corresponding URLs:
/export/home/NetID/public_html = http://bin.yale.edu/~NetID/
/export/home/NetID/public_html/cgi-bin = http://bin.yale.edu/~NetID/cgi-bin/

Because we've configured the Apache server to run with suEXEC enabled,
you can protect your cgi-bin directory so that only you can read and
write to it.

Also, there are no shared group affiliations. Your NetID is also your
group ID. Your scripts belong to you individually, not your www.yale.edu
group. If you stop having access to bin.yale.edu, be sure to hand over
your files to someone else who can maintain them.

Note: Change of server software from Netscape to Apache:

Any Netscape-server specific behavior in scripts or pages you have ported
from www.yale.edu must be modified to work under the Apache server.
The most obvious change is in the syntax for .htaccess files.
If you currently have .htaccess files on elsinore, which uses a
Netscape server, you'll need to modify the syntax of those files.

http://apache-server.com/tutorials/ATusing-htaccess.html contains a tutorial on using .htaccess files under Apache.

http://httpd.apache.org/docs/mod/directives.html contains a list of directives that can go into .htaccess files.

Perl has moved: Perl on elsinore was in a non-standard location. On bin.yale.edu it's been installed in

/usr/local/bin/perl

which is a more normal place for it to be. If you're porting scripts from www.yale.edu, you'll need to change the header in your scripts to reflect this change.

MySQL
To access MySQL, type the following at the command line:

/usr/local/mysql/bin/mysql -p

You will then be prompted for your MySQL password. When you have entered your MySQL password, a prompt will appear:

mysql>

At the prompt, type use netid (substitute your netid) to access your database.

PHP
According to BIN Tools: "You will have to put #!/usr/local/bin/php at the top of your php pages contrary to the 'common' way of doing php where it is parsed directly by the server."

suEXEC environment:
All your scripts will execute as you, not the web server. You no longer
need to have your files world-readable to have the web server look at
them, nor will you need to have them world-writable to have the web
server edit them. However, you do need to be sure that you (the owner)
have execute permission for the script, or it will not run.

If you have any questions about the new server, please reply to us at
webmast@pantheon.yale.edu, or call us at (203)-432-6598.


[2004-11-25]
Start with DreamWeaver Exchange tutorials.Webmonkey also has tutorial on PHP with MySQL. Another is available at freewebmasterhelp.com, where one is encouraged first to read specific tutorial on PHP. But dev.mysql.com might be the best.

[2004-11-21]

Blogger article "How to create expandable post summaries" including critical piece:

[span class="fullpost"] [/span]

where the expanded post text is insterted inside the span tags.


[2004-11-24]

Set up mailing list for SAC-FAST, but still waiting for confirmation from fellow subcommittee members that the invitations were distributed. The Administrative page allows modification of settings for members or list. Users are directed to the information page. These Web pages are customizable using HTML.


[2004 10 16]
HTML Cheat Sheet

Julie Linden, discussing good web design, recommends two web sites: Electronic Library Initiatives and Web, Workstation and Digital Consulting Services

For blog technical assistance, htmldog.com might be worth a look. Includes handy tags reference sheet.

July 8, 2004
The website SiteExperts.com seems like it might be useful. Also take a look at dive-into-xml.html and CSS Online which is actually a part of SiteExperts.com, I believe. Also check out SourceForge.net: Software Map for additional java script snippets. Here's a bit of java script that forces selected link to open in new window, which I've used on both Blogger and my static html homepage.
[2005-03-09]
Yale-hosted Tiki Wiki , a PHP/MySQL -backed opensource Wiki solution used by Yale ITS Technology & Planning to drive departmental webpage, superceded by uPortal, based at Princeton.

Consider Dream Weaver 2 for Learning Plan.

[2005-04-14]
MediaTech Solutions on Whalley Avenue offer classes in XML, DreamWeaver, ColdFusion, MySQL, and other web and database tools of interest to the Library. Less convenient, but better known, is New Horizons in Trumbull, which has lots of stuff on database-driven website development, but almost all of it presupposes a Windows OS environment. Here's their Course Catalog. It does have a DreamWeaver level 2 class, which might be worth taking. Would need to read course outline and make sure it's new territory. Also consider taking Programming with XML in the Microsoft .NET Framework, though it appears I don't have proper prerequisites. HOTT offers specific class on php programming, but rather expensive ($1895), and takes 4 days.

PHP + MySQL in slashdot

Michael J. Ross June 30, 2005 Slashdot book review of Vikram Vaswani's How to Do Everything with PHP and MySQL. Doesn't assume prior knowledge of database programming, but does presuppose familiarity with HTML. Chapter one is freely available on publisher's Web site.

Categories: PHP, MySQL, Databases, Vikram_Vaswani, Michael_Ross