webJestic.NET

Database Designs: MySQL NULL Fields

Michael — Wed, 10 Jun 2009 13:28:55 +0000

Does your table designs support NULL values?

There is a debate about creating tables which permit NULL values. I personally make every effort to make every field in a table NOT NULL. The argument for this: I want to be able to depend on my SQL statements. In this post I will illustrate how an insignificant field that allows NULL values can create an issue. This is a simple illustration. More complex queries can break down as easily where NULL values are allowed.

It’s in my opinion, that NULL fields should be a topic for discussion, when you begin talking about your designs and the integrity behind those designs.

Example: NULL fields are allowed.

CREATE TABLE SECURITY_USERS (
SECURITY_USER_ID Integer NOT NULL,
USER_NAME Varchar(30) NOT NULL,
FIRST_NAME Varchar(20),
LAST_NAME Varchar(20),
MIDDLE_INITIAL Varchar(1),
CONSTRAINT PK_SECURITY_USERS PRIMARY KEY (SECURITY_USER_ID),
CONSTRAINT UNQ_SECURITY_USERS_USER_NAME UNIQUE (USER_NAME)
) TYPE=InnoDB DEFAULT CHARSET=utf8;

There are several things wrong with this table, but for the moment I would like to simply use it as an example of a table that supports NULL values. There are plenty of other fields in this table, but I’ve removed them for the sake of this post. They all permit NULL values.

In my opinion, the problem with this style design is that “I can’t always depend on my queries to produce good results“. Most of the time the system has entered in FIRST_NAME and LAST_NAME into the table and sometimes but not always, they entered a MIDDLE_INITIAL.

So, if we have two users in the system and the data looks like this:

Example: Sample data.

/*
SECURITY_USER_ID | USER_NAME | LAST_NAME | FIRST_NAME | MIDDLE_INITIAL
1 admin Master Web (NULL)
2 jdoe Doe John B
*/

…and one of them has a middle initial, while the other does not. Then this creates the potential for unpredictable results in advanced queries.

Example: SQL Statement.

SELECT concat(LAST_NAME, ‘, ‘, FIRST_NAME, ‘ ‘, MIDDLE_INITIAL) AS FULL_NAME
FROM security_users WHERE security_user_id = 1 OR security_user_id = 2

All I want to do is concatenate the users name in SQL, so it displays in the format of my choice without my having to do it in code. Sometimes, it’s nice to bring back data in the format you want. Besides, if I execute this query in more than one place in my application I don’t want to have to continue formatting it in code. I just like to have the query do it.

The problem is, when NULL fields are allowed, you cannot depend on good results. Especially if I built my query and was testing it with people who had middle names. The first time the query came across someone who had a NULL middle name in the application, the application would receive unexpected results.

Example: Query Results

/*
FULL_NAME
(NULL)
Doe, John B
*/

Despite the fact there is data in FIRST_NAME (= Web) and LAST_NAME (= Master), the returned result is still NULL. The MySQL concat() function failed to return the proper results, because MIDDLE_INITIAL contains a NULL value.

Using simple straight forward SQL statements failed, due to a NULL field in MIDDLE_INITIAL. This is a visual example; something we can see in a result window. However, if I had a more complicated query and was using a NULL field in a JOIN, then the problem isn’t as interesting. The problem can even become frustrating.

Example: NOT NULL table redesign (following the data structure)

CREATE TABLE security_users (
id bigint(20) NOT NULL AUTO_INCREMENT,
user_name varchar(30) NOT NULL,
user_first_name varchar(20) NOT NULL,
user_last_name varchar(20) NOT NULL,
user_middle_initial varchar(1) NOT NULL DEFAULT ”,
UNIQUE KEY user_name (user_name),
PRIMARY KEY (id)
) TYPE=InnoDB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1000;

In this table, MIDDLE_INITIAL has a value, even if the value is empty. An empty value is not the same as NULL and the results would return as I might expect them. The query in this post would execute properly and return the right results.

Working with MySQL Auto Incrementing Keys

Michael — Sun, 10 May 2009 08:31:56 +0000

If you’ve been working with Firebird, Oracle, or any number of other databases, the first thing you’ll want to understand is how to work with MySQL auto_increment keys. Anytime you work with keys that are auto incremented by the database engine, you will need a way to retrieve the key values for your insert statements. Relational database are built on joins and understanding how to assign your foreign keys is essential.

Using MySQL to manage your foreign key inserts you can use the LAST_INSERT_ID() function. This allows you to directly insert the value for your foreign keys without having to execute a query or some other mechanism. The LAST_INSERT_ID() MySQL function returns the value of the PRIMARY KEY previously inserted.

This works perfectly for simple designs where only a single foreign key exists and you want to link to the master table.

Example: Tables used

CREATE TABLE web_template (
id bigint(20) NOT NULL AUTO_INCREMENT,
template_name varchar(30) NOT NULL,
template_location varchar(100) NOT NULL,
UNIQUE KEY template_name (template_name),
PRIMARY KEY (id)
) TYPE=InnoDB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1000;
CREATE TABLE web_config (
id bigint(20) NOT NULL AUTO_INCREMENT,
config_template_id bigint(20) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (config_template_id) REFERENCES web_template(id) ON DELETE CASCADE
) TYPE=InnoDB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1000;

Example: Insert statements

INSERT INTO web_template (template_name, template_location) VALUES (‘Default Template’, ‘/template/Default/’);
INSERT INTO web_config (config_template_id) VALUES (LAST_INSERT_ID());

In PHP we have the mysql_insert_id() function, which allows us to retrieve the last key inserted. This function works pretty good, but it must be executed before you commit your transaction, otherwise you might get unexpected results. Additionally, it’s ideal to execute the statement directly after you submit your insert statement.

Using this function gives us more flexibility in key management in our applications, especially when working with tables that have multiple foreign keys.

Example: PHP statements

mysql_query("insert into web_template (template_name, template_location) values (’Default Template’, ‘/template/Default/’");
$web_template_id = mysql_insert_id();
mysql_query("insert into web_config (config_template_id) values (".$web_template_id.")");
?>

Note: In a “data aware controls” environment, it’s not always easy to manage your auto incrementing keys (a lot of this is due to design, not just environment). In most cases where I have the decision making power I try to avoid data aware control development (especially on the web, now that Delphi for PHP is available). Read only grids are an exception, but most of the time you can design your applications to avoid “live data aware” entry/edit forms. It may seem like more work, but I believe in the end it’s cleaner and easier to debug.

How to Convert a Fireworks Image Design to HTML

Michael — Fri, 08 May 2009 12:54:40 +0000

(Please open the article to see the flash file or player.)

In the last video in this mini site creation series I showed How to Design a Mini Site in Fireworks. In this video I show how to slice the image and export the image into HTML and then modify it into a very basic mini site.

There is nothing advanced in this tutorial, it’s just a very basic slice and export process with very minimal editing. This is to assist someone who has very little knowledge and experience with HTML. Basic understanding of using word processors and editing documents is all that’s required.

Please visit W3Schools to learn basic HTML programming.

I’ve created the two menu pages seen in the design image and created a link to the exported HTML mini site. You can view it here. I haven’t added anything to it that isn’t in the video. It is exactly how it was when I designed it in the video.

How to Design a Mini Site in Fireworks

Michael — Fri, 08 May 2009 09:50:48 +0000

(Please open the article to see the flash file or player.)

In the last video series, I went through the process of How to Create eCovers (eBooks) and in this video I show how to design the mini site that compliments that eCover.

Mini sites are a pretty easy way to quickly create a website that is e-commerce ready. You can find tons of them on the internet in almost every genre imaginable. More commonly, the mini site serves the purpose of selling informational products, such as “How-to Make Money Online“. A somewhat horrible industry, due to the fact there is a lot stupid information available. I think most of that information is recycled garbage, modified by someone who promises riches, but has no idea what they are doing.

Nevertheless, the mini site is a powerful web site formula and not to be overlooked by someone with real talent and real knowledge. Understanding the formula and how to design and create the mini site is a valuable asset, as it can be used in many ways by both the entrepreneur and the freelancer.

The image below is the end result of this design video.

How to Create eCovers (FW+PS)(ebooks)

Michael — Wed, 06 May 2009 18:03:47 +0000

(Please open the article to see the flash file or player.)

I use Adobe products for many things. I’m not a graphic artist, so I’ve never been able to master or even make good sense with Photoshop. However, Adobe Fireworks is easy to learn and a very powerful tool for me as a web developer.

In this video I use Adobe Photoshop and Adobe Fireworks to create an ebook cover.

To create modern day Web 2.0 style ebook covers, most everybody uses Action Scripts of some sort. In this video I am using Cover Action Pro scripts (a commercial product).

This video is about 25 minutes long and shows every step I took to create the eZine eCover in this post. My style might seem goofy to some Photoshop experts, but I’m a Fireworks user and all scripts that I am aware of at the moment are for Photoshop. So, I design the eCover in Fireworks and run the Photoshop scripts on the design.

MySQLQuery Dataset to Create Dynamic Smarty Menus (AppObsess)

Michael — Sat, 02 May 2009 15:09:31 +0000

(Please open the article to see the flash file or player.)

In this video tutorial I use a MySQL connection to dynamically create a MySQLQuery object, extract menu data from the database, and then create data-driven menu items for a Smarty Template.

This is a continuation from the Connecting to MySQL in Delphi for PHP (AppObsess) post.

The basic concepts of this video continue to show how to work with the Smarty Templates and how to spin through a MySQLQuery dataset using Delphi for PHP.

If you are familiar with Delphi, then you probably already know how to use the First, Next, Last, and EOF functions of a TDataSet object to spin through the data and collect information using the FieldByName or FindField functions.

The same principle applies in Delphi for PHP and all the basic navigation methods are available. The only difference in Delphi for PHP is that the fields are in an array property called Fields and each field retrieved in the query is accessible via the MySQLQuery->Fields['FieldName'] array elements.

I’ve created a unit called template_functions.php and added some functions to help build some of the common elements of a template. The core functionality of creating the query and spinning through the results looks like this:

// Dynamically create the main menu
function add_MainMenu($sender, $params, $MainData) {
$query = new MySQLQuery($MainData);
$query->Database = $MainData->MySQLDB;
$query->SQL = ’select * from menu where menu_type_id = (select id from menu_type where menu_type_name = "MainMenu")’;
$query->Prepare();
$query->open();
if ($query->RecordCount > 0 ) {
$query->first();
while (! $query->EOF) {
$result = $result. ‘
$query->Fields[‘menu_url’].‘">’.$query->Fields[‘menu_name’].‘

$query->next();

}

return $result;

}

I’ve also added two new tables to the database. As time goes by I’ll be adding ALTER statements and adapting the database structure as I continue playing with this project. I might not always add the SQL in the posting, but it will always be included in the downloadable project .ZIP file.

CREATE TABLE menu_type (
id bigint(20) NOT NULL AUTO_INCREMENT,
menu_type_name varchar(60) NOT NULL,
menu_type_desc varchar(200) NOT NULL,
UNIQUE KEY menu_type_name (menu_type_name),
PRIMARY KEY (id, menu_type_name)
) TYPE=InnoDB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1000;
CREATE TABLE menu (
id bigint(20) NOT NULL AUTO_INCREMENT,
menu_type_id bigint(20) NOT NULL,
menu_name varchar(60) NOT NULL,
menu_url text NOT NULL,
UNIQUE KEY menu_name (menu_name, menu_type_id),
PRIMARY KEY (id, menu_name),
FOREIGN KEY (menu_type_id) REFERENCES menu_type(id) ON DELETE CASCADE
) TYPE=InnoDB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1000;

If you would like to download this application in the state it is for this video, you can download it using the link below. The .SQL file, the template, and the Delphi for PHP project code is all included.

Free Membership Required: Please Login or Register to read the rest of this content.

Syntax Highlighted Source Code in WordPress

Michael — Sun, 22 Mar 2009 19:08:04 +0000

Adding syntax highlighted source code in WordPress doesn’t come without a plugin. WordPress by nature strips out any potentially dangerous code in the posts and pages, protecting you from yourself. There are two very good plugins to accomplish this. 1) SyntaxHighlighter2 and 2) Dean’s Code Highlighter. Both are good.

I prefer the first, but there are some problems with it when using some themes in WordPress and the current theme I have installed is one of them. To post source code with this plugin in WordPress, you place your code between the [sourcecode language="php"] [/sourcecode] tags.

The 2nd almost always works and I’m using it in this theme now. Using Dean’s Code Highlighter I can place my source code between the

tags in my WordPress HTML editor and preserve code formatting and have intelligent syntax highlighting, based on GeSHi.

“Delphi” Sample:

for (x := 0 to 100) do begin
end;

“PHP” Sample:

for ($x= 0; $x= 100; $x++) {
}

“JavaScript” Sample:

for(i = 0; i < 5; i++){
}

“CSS” Sample:

body {
background-color: Black;
}

“XML” Sample:

version="1.0" encoding="UTF-8"?>
xmlns="http://www.google.com/schemas/sitemap/0.84">
>
>http://webjestic.net/>
>weekly>
>0.5>
>
>

“XHTML” Sample:

For a complete listing of all the supported languages, visit GeSHi.

Connecting to MySQL in Delphi for PHP (AppObsess)

Michael — Sun, 22 Mar 2009 07:14:45 +0000

(Please open the article to see the flash file or player.)

In this video tutorial I begin building an ongoing project called AppObsess, which I will use as a generic and very simple application engine using the Smarty Template engine and the MySQL database. I begin this video series having applied the techniques from the other video’s: Creating Editable Forms for Smarty Templates and Creating Dynamic Smarty Variables in Delphi for PHP.

Connecting to a MySQL database in Delphi for PHP is an extremely simple task. In this video I create a data module and a config file containing the connection strings, include the files and execute the program. That’s the entire video in a nutshell. Most of the video is spent getting up to speed on the state of the application.

The core code is written in a file called maindb.php (the data module) and it looks like this:

$MainData->MySQLDB->Host = HOSTNAME;
$MainData->MySQLDB->DatabaseName = DATABASE;
$MainData->MySQLDB->UserName = USERNAME;
$MainData->MySQLDB->UserPassword = PASSWORD;
$MainData->MySQLDB->Open();
if (! $MainData->MySQLDB->Connected) {
echo ("Unable to connect to " . DATABASE . "\r\n");
exit ("maindb.php MainData->MySQLDB critical error");
}

As always, you should have much better error control handling then I added for this simple tutorial. My connection testing was just to make sure something stupid didn’t happen while I was creating this video.

The config.inc file is very simple as well and only contains the connection strings for MySQL. It looks like this:

DEFINE(‘HOSTNAME’, ‘localhost’);
DEFINE(‘DATABASE’, ‘obsess’);
DEFINE(‘USERNAME’, ‘obsess’);
DEFINE(‘PASSWORD’, ‘demo’);

The starting template I am using for this AppObsess web framework in Delphi for PHP is the Blue Obsess template available from my XHTML/CSS Templates page.

The starting database SQL I have for this application is:

DROP DATABASE IF EXISTS obsess;
CREATE DATABASE IF NOT EXISTS obsess;
USE obsess;
CREATE TABLE page (
id bigint(20) NOT NULL AUTO_INCREMENT,
page_name varchar(200) NOT NULL,
page_body text NOT NULL,
UNIQUE KEY page_name (page_name),
PRIMARY KEY (id, page_name)
) TYPE=InnoDB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1000;

Free Membership Required: Please Login or Register to read the rest of this content.

Creating Editable Forms for Smarty Templates in Delphi for PHP

Michael — Thu, 19 Mar 2009 14:10:30 +0000

(Please open the article to see the flash file or player.)

Once you begin working with the Smarty Template engine in Delphi for PHP and once you learn how to include and update smarty variable tags, you’ll want to start including work areas for your data forms. With a lack of VCL documentation, it’s not obvious how to get your controls in the smarty template and get them positioned correctly.

Additionally, for several days I was stumped as to why I couldn’t get my Button code to execute when I used a smarty template. This video shows how to accomplish all these tasks. This video extends the lesson in Creating Dynamic Smarty Variables in Delphi for PHP and in Hello World (Delphi for PHP).

The main thing to remember in all of these lessons is that in obtaining the desired effect, we are basically configuring the proper Object Inspector Properties or extending the code within the Object Inspector Events for the proper objects.

Creating Dynamic Smarty Variables in Delphi for PHP

Michael — Wed, 18 Mar 2009 18:23:26 +0000

(Please open the article to see the flash file or player.)

One of the first things you’ll notice when you begin working with Smarty Templates in the Delphi for PHP environment is that it’s not obvious how you can include custom or dynamic smarty variable tags into your template from Delphi for PHP.

This video was created to demonstrate exactly how to get dynamic smarty variables into your smarty templates and display the correct variable information. The main thing to know and understand is, that the VCL in Delphi for PHP has a certain time it executes the Smarty Engine events.

To access the correct time and place to assign your variables is to use the Forms->OnTemplate event and access the smarty object through the provided $params. In this video I’ve created 3 dynamic smarty tag variables. $MyTitle, $MyDescription, and $MyKewords. I assign the values of these variables in Delphi for PHP and placed them in the HTML smarty template.

function Unit1Template($sender, $params)
{
$template = $params[‘template’];
$template->_smarty->assign(‘MyTitle’, ‘My Custom Page Title’);
$template->_smarty->assign(‘MyDescription’, ‘My Custom description…’);
$template->_smarty->assign(‘MyKeywords’, ‘keyword 1, Smarty Keyword’);
}

If you’ve found this video posting useful, you might also be interested in the video posting that extends this lesson: Creating Editable Forms for Smarty Templates in Delphi for PHP.