Views and PHP: Part I

17 08 2010

As a PHP Developer looking for work during the current economic downturn, I can always depend on the interviewer asking if I know about MySQL5’s views. What is interesting about this question is that working with views requires no programming skills. Yet, if the views question is not answered properly, the interviewer may judge the job candidate as an inferior programmer! So, let’s take a look at how to work with PHP and MySQL’s views.

If you’re coming from the front-end the best way to explain a view is to say that it is analogous to dynamic HTML, in particular using JavaScript’s document.write() to generate HTML. It’s that easy — actually easier. All you need to do is issue one CREATE VIEW query that contains a SELECT statement. This will result in a table whose content is just the SELECT statement itself. When you attempt to access the view, the SELECT statement takes effect, generating a virtual table of results. What’s nice about a view, is that you can in turn run queries on it and thereby achieve simplicity and efficiency.

If you want to know more detailed information about views in MySQL, check out dev.mysql.com/tech-resources/articles/mysql-views.pdf

To illustrate how to work with PHP and views, I provide the following schema for my database test4ah:

CREATE TABLE IF NOT EXISTS `invoice` (
`invnum` mediumtext NOT NULL,
`userid` int(11) NOT NULL,
`invdate` date NOT NULL,
`amount` double NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='used for testing';
CREATE TABLE IF NOT EXISTS `user` (
`userid` int(11) NOT NULL AUTO_INCREMENT,
`companyname` varchar(80) NOT NULL,
`state` tinytext NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='used for testing' AUTO_INCREMENT=11 ;

Now, let’s add some data:

INSERT INTO `user` (`userid`, `companyname`, `state`) VALUES
(1, '3M', 'VT'),
(2, 'Toyota', 'CA'),
(3, 'MapleSyrupINC', 'VT'),
(4, 'MapleHomesCorp', 'VT'),
(5, 'MyMortgageInc', 'FL'),
(6, 'Fidelity', 'FL'),
(7, 'Invalid', 'ZZ');

and the data for the other table is:
INSERT INTO `invoice` (`invnum`, `userid`, `invdate`, `amount`) VALUES
('123A', 1, '2002-12-14', 460.33),
('123ABC', 1, '2007-02-28', 100.33),
('123ABCD', 2, '2007-01-29', 300.53),
('1234G', 2, '2006-01-12', 177.53),
('X123A', 3, '2002-03-30', 560.33),
('Y123ABC', 3, '2001-02-28', 100.33),
('XX123ABCD', 4, '2002-12-12', 200.53),
('YY1234G', 4, '2002-12-15', 177.53),
('aabbcc123', 5, '2010-08-27', 225),
('bbccdd123', 5, '2010-08-28', 255),
('zzaabb123', 6, '2010-08-26', 550),
('abcdef123', 6, '2010-08-02', 697.13),
('aabbccddee332211', 6, '2010-08-09', 392.25),
('zzzzzzzzzzzz', 7, '0000-00-00', 0);

You can manually create a view, after you select a database (you must select a database), by issuing an SQL CREATE VIEW statement in phpMyAdmin or at the MySQL command-line prompt. Alternatively, you may wish to use phpMyAdmin’s CREATE VIEW link, appearing at the bottom of page, after you’ve already clicked the Browse tab to view a table’s records. Since the nested SELECT statement is integral to creating a view, first make sure your SELECT statement is working correctly and then proceed with creating the view. You can even use PHP to create a view which you’ll see in the final section in Part III.

Note: Parts I and II, are both posts which provide background information for the actual PHP view-based code listings in Part III. So you could skip ahead but I suggest that you at least glance at Part II.

This work is licensed under a Creative Commons License
.

Advertisements

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: