Database Programming for a LAMP Stack
Deliverables (See class' page for due dates):
- Deliverable #1: Demo at the command line in lab at least three useful 'novel queries'
from the SeSPoP database
to demo SQL joins, aggregate functions, like operator, handling null data.
Prepare for quiz questions like:
- Subtotals by Region and Volunteer, or by either, in order of Amount, descending
- Report each region's donations with subtotals for each volunteer
- Calculate each volunteer's commission for the period, at 30% of the Amount of the Donation
- Names and addresses of Members getting T-shirts
- Names and addresses of Entities with any of the rare negative blood types within the nearest 100 zip codes.
- Top 10 Donations showing names of member, volunteer, and region.
- Stats by Volunteer or Region, or by Region and Volunteer: total, count, average, max, min.
- Which Regions, or Volunteers, have no donations?
- Top 10 3-Digit Zips by Donation Amount showing Region Name and City
- Others may be proposed, get on it please...
- Deliverable #2: Referring to the
$_GET samples, make three
queries that are not already on one of the reports at
and get them prominently linked on your home page and styled like your site.
They must demo table joins & summary functions like sum involving group by, handling of null data,
coalesce, case, and rollup. Reports should be appropriately tabulated with tables or divs.
Everything must validate and be mobile-friendly.
Deliverable #3: Using the
$_POST samples or other favorite reference, make three new queries, not in the examples, where the User of the web page
gets to select from a list of properties, and gets back an
appropriately tabulated report, styled like your site.
A PHP script on the server edits again, gives errors or prompts 'is it OK?', and echoes completed form if it is...
Log into mysql _after_ you've logged into info250.us and have a bash prompt. For these exercises
the command to get to the SeSPoP database with MySQL is:
mysql -usespop -psespop SeSPoP
The sespop user id gets 'select only' privileges on the SeSPoP database, can also be used in the PHP scripts.
The next few classes are an intro to Database Programming for the Web starting with the M-MySQL/MariaDB in LAMP and
one of the options for P-PHP.
- (1/4) Use w3schools.com,
Learn SQL as
your intro text for database and MySQL and visit mysql.com/marida for a detailed
reference. Pass the SQL Quiz for 5 points.
- (1/6) Be aware of MariaDB.org, especially the Learn link.
The docs are almost the same as those at MySQL.com as at 2016, but won't be for long.
MariaDB Documentation is
in their Knowledge Base along with other stuff about the environment.
- Intro to DBMS
shows a Database ERD-Entity Relationship Diagram for the three related tables for SeSPoP.
Another table, Entities, is not related to these.
There are a few other topics that will be quizzed, too, generations of programming languages, &c.
The topic 'Professional Considerations' raises awareness of
the danger inherent in this activity where any lapse of secruity or waste of resources can break a company.
and wasted resources and other links cover topics. Please read all the links at SeSPoP,
get your questions answered, and be ready to answer questions from them on the exam...
- Brief History of MySQL & MariaDB: For decades, MySQL, and now MariaDB, has provided a very quick DBMS for
free or $175 for a commercial license.
It's been quick because as it's usually deployed is doesn't provide some 'enterprise features'
that an 'ANSI Standard' database does. And, it is not 'highly scalable' to support an enterprise.
Mostly deployed in a 32-bit server environment,
MySQL has been a good choice for for the relatively small relational database a workstation/server class machine
less than 4GigaBytes total, and not mission-critical, since too many transactions too fast
can break it.
MySQL's fast and robust enough for lots of applications and delivers quick results for a WordPress site or
other database-oriented application. You can't beat the price.
Shortly before Sun Microsystems was acquired by Oracle, Sun had acquired MySQL and promised
to make it an enterprise-class DBMS. When Oracle acquired Sun they also acquired MySQL and
also promise to make it better. Not fond of Oracle, MySQL's developers 'forked' the open source
DBMS to MariaDB.org and are keen on seeing their open source dbms exploit everything a 64-bit CPU can do.
The MySQL.com developers, who don't believe Oracle is the devil, are also enthusiastically hacking at
their fork of the code.
Proprietary ANSI SQL DBMS like Oracle or IBM's DB/2, or non-relational databases like IBM's IMS and HP's
CODASYL DBMS have been proven to be almost infinitely scalable, as close to 100% reliable as possible,
and able to handle a transaction load that would crush something like MySQL.
Proprietary DBMS are not free, are sold with a CAL-Concurrent Access Limit and can cost millions if configured for a large enterprise or organization.
They can also be very reasonably priced for small organizations with a limited CAL.
This allows the VARs who sell most DBMS to sell at a very competitive rate for startups in a 'vertical market' and provide reliability and scalability of a mid-range or mainframe environment if/when the startup experiences exponential growth.
- (1/6) Use the EBUS202/SeSPoP database to make novel queries that demo joining tables inner and outer,
handling null data, and providing totals & subtotals. Sample SQL statements can
be found in the PHP scripts at /home/SeSPoP/Web that support the links at info250.us/SeSPoP.
Here are more complex examples that demo CASE, COALESCE, ROLLUP and OUTER JOIN.
- Here's an outline of the demo done in class, not quite useless:
DBMS at SeSPoP
- Learn about MySQL Data Types.
MariaDB Documentation also covers
Server-side Scripting with PHP
Use w3schools.com, Server
PHP as your introductory text to Web Programming with PHP. (Prior work with PHP was for CLI --
Please do not run CLI scripts from the web!)
Visit us2.php.net often for detailed stuff about PHP.
Here is a link to Discussion of the Sample Scripts. Use this for explanation of the
PHP language features.
Use the docs at us2.php.net, fastest US mirror, for more details about each PHP language element.
Development Environment for Web Work
Noobs, use vi and the command line for these projects, become comfortable navigating and editing as you
get your code.
Advanced students may pick another editor:
It _must_ do syntax highlighting and it _must not_ be an 'HTML Editor' like DreamWeaver
or Seamonkey. vi works fine if you like it, does syntax highlighting and is quickest if you have good bandwidth.
It might be time to learn emacs, which is the ultimate geekish server-side editor and is on most *ix servers,
will really impress a technical interviewer in the future.
Windoze users might like NotePad++ or a higher functioning editor like Atom, Crimson, or Sublime where you edit on the desktop
and use FileZilla to transfer files to the server via sftp (NotePad++ will automate this step if you
edit its settings). Mac users should check out CodeWrangler, which handles the
publishing of files to the server automagically via sftp and also has vertical marks that help align structures in html or scripts.
It's imperative to keep an eye on the error_log to make sure your html
and/or scripts are not producing warnings or errors! Keep a terminal window
open that's 'tailing the log' with:
tail -f /var/log/httpd/error_log | grep yourid
Keep this peeking out from under your browser so you can see it jump when you get an error!
Here is a screenshot showing such an arrangement of putty sessions and browser:
Database programming for the web is an inherently complex task involving multiple skills,
data structures, and programming languages. Careful attention
to error messages from the server and browser is required. The database needs to be
quickly available to test SQL statements used in the scripts, to see the effect
SQL in the scripts has on the database, and to delete trashed records or make changes to the schema.
The more a programmer can _see_ of these rather abstract components as they work, the easier the
One way to keep the components visible is to use have two or three putty or terminal sessions running
keep FireFox or Chrome's developer tools -> error console available.
- One putty window is used for the scripting, another for the database, and another
to get error messages from the server running the php scripts.
- If these are overlapped so a corner and edge of each is always visible it's very quick to move from one to another by
clicking with a mouse -- even quicker using Alt-Tab or Windoze-Tab. This reduces time & effort of clicking
minimize/restore, opening/closing files in the editor, and other superstitious and wasteful efforts that
further complicate what is already a complex picture.
- Server-side errors from PHP or HTML show up by 'tailing the log' and filtering it with grep to show only your errors.
Replace 'yourlogin' with your login id:
tail -f /var/log/httpd/error_log | grep yourlogin -- close this using Ctrl-C
Keep this window visible whenever running a server-side script so you can see it 'jump'
if/when the script generates errors.
- Browser-side errors in
Clear the Error Console before each debugging run, refresh your