When I went over my example of using the WITH clause to solve how to use a series of literal values in data sets, some students got it right away and some didn’t. The original post showed how to solve a problem where one value in the data set is returned in the SELECT-list and two values are used as the minimum and maximum values with a BETWEEN operator. It used three approaches with literal values:

• A list of Python dictionaries that require you to filter the return set from the database through a range loop and if statement that mimics a SQL BETWEEN operator.
• A WITH clause that accepts the literals as bind variables to filter the query results inside the query.
• A table design that holds the literals values that an analyst might use for reporting.

It was the last example that required elaboration. I explained you might build a web form that uses a table, and the table could allow a …

CSV or SQL? SQL or CSV? How about together? Without a doubt, CSV’s are one of the most common and familiar data interchange formats. Importing and exporting CSV data into or out of an SQL database is a staple process in most every data workflow. I’ve written numerous blog posts on both importing and exporting CSV data in an SQL environment. In this post, I am including all of these specific posts (as of the time of writing) in one easy-to-read and centralized location…

Image by xresch from  …

As an example for my class on the usefulness of Common Table Expressions (CTEs), I created three examples with Python. They extend an exercise in Chapter 9 on subqueries from Learning SQL by Alan Beaulieu. All of the examples work with the sakila sample database.

These bullets describe the examples:

1. Uses local variables and a range for loop and if statement that uses the variables to evaluate and add an element to the derived table (or query result set) from MySQL.
2. Uses a CTE with substitution variables from the Python program, which eliminates the need to evaluate and add an element to the query result set because the query does that.
3. Uses a table to hold the variables necessary to evaluate and add the element to the query result set.

This is the first Python program:

# Import the library.
import sys
import …

I use CodeIgniter 4 a great deal for learning, personal projects, and application development. In this post, I cover 2 different methods you can use and retrieve the MySQL Last Insert ID value after executing an INSERT statement in CodeIgniter. Continue reading for more information…

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

Model Setup

Starting out, I use the CLI Generator feature and create a Model by executing this code in the terminal in the project root:

php spark make:model user --suffix

I have removed much of the …

If you need a structure only .sql file of a MySQL tables’ Data Definition Language (DDL), phpMyAdmin makes this super-easy with just a few clicks. Continue reading and learn how…

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

Why use a DDL file?

Why would you need a structure-only .sql DDL file anyway?

Maybe you want to build out a tables’ structure on another server to bring in different data or something comparable, and want it void of any existing data.

Rather than creating the …

When the time comes that you no longer have a need for specific data, use DELETE to remove it completely from your table. CodeIgniter 4 Models have a delete() method that is super-easy to use. Continue reading for more information…

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

CodeIgniter 4 CRUD Series with MySQL

I have written a now complete 4-part series over on my Medium account about CodeIgniter 4 Model CRUD operations. Feel free and visit any of the previous posts in the series to get up to speed:

• …

An interesting outcome of teaching SQL is discovering what skills new users require. One that I continuously rediscover is how to build a test case for various elements of SQL. This is a small article on querying with filters in the WHERE clause.

There are several of the exercises in Alan Beaulieu’s Learning SQL, 3rd Edition that would benefit from example setup. For example, Chapter 4 provides a snapshot of the payment table but doesn’t provide any instructions.

You can create an exercise_4_2 table with the following SQL statement if you plan to change the data:

CREATE TABLE exercise_4_2 AS
SELECT payment_id
,      customer_id
,      amount
,      payment_date
FROM   payment
WHERE  payment_id BETWEEN 101 AND 120;

Alternatively, you can create an exercise_4_2 view with the following SQL statement if you plan to only query the data:

CREATE VIEW exercise_4_2 AS
SELECT payment_id
, …

In some applications, data may never change. Yet, in others, data changes numerous times in its lifecycle. In SQL the UPDATE command changes existing rows of data. CodeIgniter 4 Models have 2 methods available for update operations: update() and save(). Continue reading and learn more about update()…

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

CodeIgniter 4 CRUD Series with MySQL

This post is a re-share of an article I originally published on my Medium account and is part 3 in the CodeIgniter 4 CRUD with MySQL series. Be sure and …

Storing data is but one part of many in application development. Once data is stored, interested parties will likely want to see it. This is the Read aspect of CRUD – reading (or viewing) the data. Continue reading and see examples using CodeIgniter 4 built-in Model methods…

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

In part 1 of this series, CodeIgniter 4 CRUD with MySQL: Create, I used the Model insert() method to store new rows of data. Now that the data is present in the table, we want to see that data.

There are 2 built-in Model …

I’m studying and beginning to use CodeIgniter 4 in ‘real-world‘ projects and want to share what I learn, as I pick up on concepts of the framework. I’m posting a series of CRUD-related posts over on Medium and resharing them here for any readers who are interested…

Are you a Medium member? If so, receive an email notification each time I publish a blog post if you prefer the Medium platform. Not a member? No worries! Use my sign-up link (I will get a commission at no extra cost to you) and join. I really enjoy reading all the great content there and I know you will too!!!

…