SELECT Statement Subquery Example (MySQL/SQL Server)

How to use a subquery in a SELECT statement (SQL Server/MySQL)

Often I need to produce a query over a parent table and get a total of values from a child table. This can be accomplished with a single query that utilizes a subquery within the SELECT statement. In the following example, we’re selecting parent values from a table named tbl_loans and utilizing a subquery to select the total of the amount paid toward principal from a child table named tbl_loanpayments. For each row in the table tbl_loans, there could be multiple child rows in the table tbl_loanpayments. We want the amount paid to the principal for all loan payment rows that have an amount paid not equal zero.

The following example will work in SQL Server or MySQL.
Continue reading “SELECT Statement Subquery Example (MySQL/SQL Server)”

October 30, 2017December 8, 2017

JQuery Copy to Clipboard Example

How to copy span, div, or other HTML element contents to clipboard using JQuery

In this blog, I provide examples of code I have written or used over the years. With each post, I provide the ability for you the user to copy the code contents to the clipboard so you can paste it into your page. This handy copy to clipboard logic enables you to copy the contents of the code block without having to select it first. Just click the copy hyperlink and the script does the rest.

I particularly like the example below because I can use it in my blog post without having to embed script in the post itself. I simply load a script function in the head tag of my pages and provide a structured link in the post body that identifies the span (or pre tag in my blog) that I want to copy. The script selects the range of data in the span, highlights it, copies it to the clipboard, then alerts the user the copy has completed.

The script monitors for a click event on all elements on the page with a name=”copy_pre”. All of our copy links are named “copy_pre”. Each link has a unique ID that matches the ID of the span we want to copy.

The copy script:
Continue reading “JQuery Copy to Clipboard Example”

October 30, 2017May 3, 2022

JavaScript Simple Tabbed Content Example

How to use JavaScript to display tabbed content

There have been many cases where I’ve needed to display various child data pertaining to a parent record. Rather than clutter the page with all the information at once, I like to present the data in tabbed content.

With tabbed content, you can group and display relevant content together with a tab. This makes for better organization of data on the page and potentially keeps the user from having to page down to find the content they are looking for.
Continue reading “JavaScript Simple Tabbed Content Example”

October 30, 2017May 3, 2022

PHP Display Google Pie Chart Example

How to display a Google Pie Chart with PHP

Pie charts are a great way to display data in an appealing manner. Google charts are easy to implement and offer the user a colorful, interactive experience. The user can hover over sections of the chart to reveal specifics. The following example shows how to render a very simple Google donut style pie chart with two simple values. Our chart will show loan balance amount and loan paid amount effectively displaying the progress made against the loan.

Google offers a variety of charts each with the ability to customize the colors, values, sizes, etc. Reference Google Charts for more information.
Continue reading “PHP Display Google Pie Chart Example”

October 27, 2017November 7, 2017

JQuery Override Datepicker to Return Today’s Date Example

How to override the JQuery Datepicker to return today’s date

I support an ASP.net application that has multiple pages with fields that prompt for a date. All of our date input fields use the JQuery Datepicker calendar pop-up control to allow the user to select a date from the calendar.

The datepicker pop-up control can be defined to show several options (reference: JQuery Datepicker for details). One of those options is a button for today. When the today button is clicked the calendar control re-positions to the month containing today’s date. Many users wanted the today button to select and return today’s date to the date input field. This can be easily accomplished with a simple override.

Paste the following script in each page that loads the JQuery datepicker control, or better yet load it globally.
Continue reading “JQuery Override Datepicker to Return Today’s Date Example”

October 27, 2017October 31, 2017

SQL Subtotal With Detail Example (SQL Server/MySQL)

How to subtotal a result while retaining detail (SQL Server/MySQL)

I recently needed to produce a query with detail and have it subtotal by employee, by date. Subtotals can be difficult if you have data that varies within the subtotalled group.

The work around is to union queries. One query with the detail and another query with the subtotal by the control break you need. We then use a case condition in the order by clause to cause our subtotal to appear where we want it in the result.

The following example will work in SQL Server or MySQL.
Continue reading “SQL Subtotal With Detail Example (SQL Server/MySQL)”

October 20, 2017November 15, 2017

VB.Net Multiple Key Dictionary Example

How to define a Visual Basic dictionary with multiple keys

By definition, a Visual Basic dictionary represents a collection of keys and values. With a little coding, we can define our dictionary to use a multiple value key. A Visual Basic dictionary with multiple keys works great for organizing data where multiple keys are needed. Adding to, updating and accessing the dictionary is very simple and fast. Visual Basic dictionaries provide multiple Methods – See Dictionary(Of TKey, TValue) Class for more information.

The following is a pared down Visual Basic (.Net) program example of a multi-key dictionary.

I used this multiple key dictionary on a financial report I worked on. The report needed to collect a decimal value that varied based on a specific set of unique keys. Tracking values with multiple keys is not uncommon and there are several approaches you can take to programmatically track values with multiple keys. I found the use of a Visual Basic dictionary to be perfect for the task. In our example we’re going to cover constructing the dictionary, constructing the key set, comparing the key set for a match, adding to the dictionary, updating the dictionary, removing an item from the dictionary and lastly looping through the dictionary.
Continue reading “VB.Net Multiple Key Dictionary Example”

October 19, 2017May 3, 2022

JQuery Display Multiple Dialogs Example

How to display multiple JQuery dialogs on the same page

I recently started adding pop up help messages to my forms as online help text. These are handy for explaining to the user why the input is required, what is expected in the field and how the input should be formatted. But there are plenty of other areas where a dialog pop up could be useful. I use JQuery dialog because it is super simple and allows for an easy way to hide help text until it is needed.

Here is an simple example page with 3 simple paragraphs each with a separate help dialog popup. By giving each dialog a unique ID we can include as many dialog on the page as we need. In our example each dialog has the same style but you could have individual styles for each dialog.
Continue reading “JQuery Display Multiple Dialogs Example”

October 19, 2017November 15, 2017

JavaScript Validate Date Example

How to use JavaScript to validate dates

So often I have a form on the page that includes a date field and I want to validate that date without a round trip to the server. Here is a simple JavaScript function that will do just that.

The function accepts the date value and a string indicating the format expected (i.e. ‘MDY’). Simply call this function from the form either onChange of the field or on submit of the form. I prefer the on submit approach and check all the fields at once. I find it less annoying that bugging the user on every field change.
Continue reading “JavaScript Validate Date Example”

October 18, 2017October 27, 2017

PHP Simple Word Document Example

How to create a simple Microsoft Word document with PHP only

I’ve had the need to create several word documents over the years for various clients and found the simplest way to do it without having to install specific libraries or third party software is to just create the document with plain PHP.

The following script will create a word document named document_name.doc. When you browse to the page with this script the page will automatically prompt you to open or save the file.
Continue reading “PHP Simple Word Document Example”