Archive for May, 2009

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

Example: Insert statements

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

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.

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.

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.

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.

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:

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.

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.

Download AppObsess-dev-002.zip here!