Jes.sTelford

Zend Framework Quickstart with MySQL

January 08, 2012

code coding mysql php the web zend framework

The Zend Framework Quickstart guide is a great place to start with the Framework, however it bases the example program on SQLite. As MySQL is the choice of the rest of the world (and even Zend’s own Stack installation), here is the Zend Framework Quickstart done with MySQL. This information is based on the Zend Framework Quickstart “Create a Model and Database Table” step, and is designed to simply replace the SQLite information with the relevant MySQL version.

Initializing the Db Resource

When initializing the Db Resource, we’ll set it up to use MySQL connection information.

$ zf configure db-adapter "adapter=PDO\_MYSQL&dbname=\[PROD\_DBNAME\]&host=localhost&username=\[PROD\_USER\]&password=\[PROD\_PASS\]" production
$ zf configure db-adapter "dbname=\[TEST\_DBNAME\]&username=\[TEST\_USER\]&password=\[TEST_PASS\]" testing
$ zf configure db-adapter "dbname=\[DEV\_DBNAME\]&username=\[DEV\_USER\]&password=\[DEV_PASS\]" development

Alternatively, to use the same MySQL user for each database:

$ zf configure db-adapter "adapter=PDO\_MYSQL&dbname=\[PROD\_DBNAME\]&host=localhost&username=\[PROD\_USER\]&password=\[PROD\_PASS\]" production
$ zf configure db-adapter "dbname=\[TEST_DBNAME\]" testing
$ zf configure db-adapter "dbname=\[DEV_DBNAME\]" development

Replacing the [*_DBNAME], [*_USER], [*PASS] where appropriate (these should correspond to the following MySQL commands). This will update your \application/configs/application.ini_ for you.

Create the schema & data

Skip making the data/db/ directory, it isn’t necessary for MySQL. Instead, create the following files: scripts/schema.mysql.sql

DROP TABLE IF EXISTS guestbook;

CREATE TABLE guestbook (
    id int NOT NULL AUTO_INCREMENT,
    email varchar(32) NOT NULL DEFAULT 'noemail@example.com',
    comment TEXT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE INDEX id ON guestbook(id);

and scripts/data.mysql.sql

INSERT INTO guestbook (email, comment) VALUES
('ralph.schindler@zend.com', 'Hello! Enjoy this sample zf application!', '2012-01-01 00:00:00'),
('foo@bar.com', 'Baz baz baz, baz baz Baz baz baz - baz baz baz.', '2012-01-01 00:00:00');

Initialize the databases

For each Database, execute the following commands (type ‘exit’ once done):

$ mysql -u root -p
mysql> DROP DATABASE IF EXISTS \[DBNAME\];
mysql> CREATE DATABASE \[DBNAME\];
mysql> USE \[DBNAME\];
mysql> SOURCE scripts/schema.mysql.sql;
mysql> SOURCE scripts/data.mysql.sql;

Then, create the users and give them permission:

$ mysql -u root -p
mysql> CREATE USER '\[USER\]'@'localhost' IDENTIFIED BY '\[PASS\]';
mysql> GRANT ALL ON \[DBNAME\].* TO '\[USER\]'@'localhost';

Note that if you are using the same user for each database, then only execute the CREATE USER command once, and the GRANT USER command for each [*_DBNAME].

Continue the Quickstart tutorial

Keep going with the Quickstart from the creation of the Zend_Db_Table.

Jess Telford
🛠 @ceteio: Tools for organizing local structured meetups
🎤 Host @CodeHeartDesign & @ReactSydney
🗓 Ex: FE Arch Domain/Groupon/Yahoo7