MySQLQuery Dataset to Create Dynamic Smarty Menus (AppObsess)

May 2nd, 2009 |

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. ‘<li><a href="’.$query->Fields[‘menu_url’].‘">’.$query->Fields[‘menu_name’].‘</a></li>’;
$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.

Download AppObsess-dev-002.zip here!

Get Shareaholic

Posted in Delphi for PHP

Michael:

May 2, 2009 at 10:22 am

As some of you already know, I am currently in Chengdu, China. I’m using my notebook and a headset microphone to create these videos. I’m currently working on commercial development for a company and I have a few small projects of my own that I continue to move forward. These things prevent me from adding content on a regular basis.

I’ll continue to add content to this blog and continue to expand AppObsess and share information freely. I don’t plan to make this a paid membership site, although I am currently experimenting and expanding my knowledge with aMember software tied with WordPress.

I might not be adding content on a steady basis, but I have no intention to abandon this website. I’m just a little busy with things, but once I get back to the USA I should have more time to play and help this site grow.

Thanks,

Michael

MySQLQuery Dataset to Create Dynamic Smarty Menus (AppObsess)

3 Responses to “MySQLQuery Dataset to Create Dynamic Smarty Menus (AppObsess)”

Leave a Reply