Some Problem With The Book (First 4 Chapter)

nammae · December 12, 2012, 2:18pm

Hello everyone,

I just got a copy of Web Application Development with Yii and PHP 2nd edition.

I was so excited to try a new PHP framework.

I just read first 4 chapters of this book and here are some problem I met.

The problem with the compatibility between Yii 1.1.12 and PHPUnit 3.7.10 (missing function ‘phpunit_autoload’)

Okie I got a solution in githup.

I know it is not author’s fault but I think it’s still worth mentioning in the book.

In page 53, the code for DbTest.php contains a line:




this->assertTrue(true);

But after that, the author wrote "Change the assertEquals(true) statement …".

So what exactly is the statement?

Okie, it’s just a typo.

In the chapter 4, project CRUD here is the code for creating table tbl_project




$this->createTable('tbl_project', array(

			'id' => 'pk',

			'name' => 'string NOT NULL',

			'description' => 'text NOT NULL',

			'create_time' => 'datetime DEFAULT NULL',

			'create_user_id' => 'int(11) DEFAULT NULL',

			'update_time' => 'datetime DEFAULT NULL',

			'update_user_id' => 'int(11) DEFAULT NULL',

		), 'ENGINE=InnoDB');

Everything went smoothly until I tried to create a new project like the author suggested.

I entered only the name and description of new project(coz they are required) and left other fields blank.

Then I got a problem.




CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '' for column 'create_time' at row 1. The SQL statement executed was: INSERT INTO `tbl_project` (`name`, `description`, `create_time`, `create_user_id`, `update_time`, `update_user_id`) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5)

I think the problem is that the datetime should not be declared as DEFAULT NULL.

Maybe it should be "0000-00-00 00:00:00" or something like that.

Hope someone can help me.

Thanks in advance.

Nammae

jefftulsa · December 15, 2012, 4:37pm

Thanks for sharing your experience so far. I hope you are finding the book beneficial.

#1) Thanks for bringing the versions compatibility to attention. The installation of various versions of this third-party software (i.e. PHPUnit) is left to the reader as it is outside of the scope of this book.

#2) Thank you for identifying this small typo. I have added this to the errata page

#3) What version of MySQL are you using? The following sql statement behaves just fine (based on the table structure outlined in the book) tested on MySQL 5.5.28 and 5.1.63:

[sql]INSERT INTO tbl_project (name, description, create_time, create_user_id, update_time, update_user_id) VALUES (‘test’, ‘testing’, ‘’, null, ‘’, null);[/sql]

Does this statement throw an error on your system?

timkan2012 · December 20, 2012, 9:21am

Hello All, I have the similar issue and can’t create tbl_project.MySQL version 5.5.28. Pls help and appreciated highly.

test code is:


dami@ubuntu:~$ cd /var/www/trackstar/protected

dami@ubuntu:/var/www/trackstar/protected$ ./yiic migrate 


Yii Migration Tool v1.0 (based on Yii v1.1.13-dev)


Total 1 new migration to be applied:

    m121220_015328_create_project_table


Apply the above migration? (yes|no) [no]:yes

*** applying m121220_015328_create_project_table

    > create table tbl_project ...exception 'CDbException' with message 'CDbCommand failed to execute the SQL statement: CDbCommand failed to prepare the SQL statement: SQLSTATE[HY000]: General error: 1 near "ENGINE": syntax error. The SQL statement executed was: CREATE TABLE 'tbl_project' (

	"id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,

	"name" varchar(255) NOT NULL,

	"description" text NOT NULL,

	"create_time" datetime DEFAULT NULL,

	"create_user_id" int(11) DEFAULT NULL,

	"update_time" datetime DEFAULT NULL,

	"update_user_id" int(11) DEFAULT NULL

) ENGINE=InnoDB' in /var/www/htdocs/yii/framework/db/CDbCommand.php:357

Stack trace:

#0 /var/www/htdocs/yii/framework/db/CDbCommand.php(1321): CDbCommand->execute()

#1 /var/www/htdocs/yii/framework/db/CDbMigration.php(228): CDbCommand->createTable('tbl_project', Array, 'ENGINE=InnoDB')

#2 /var/www/trackstar/protected/migrations/m121220_015328_create_project_table.php(15): CDbMigration->createTable('tbl_project', Array, 'ENGINE=InnoDB')

#3 /var/www/htdocs/yii/framework/cli/commands/MigrateCommand.php(385): m121220_015328_create_project_table->up()

#4 /var/www/htdocs/yii/framework/cli/commands/MigrateCommand.php(109): MigrateCommand->migrateUp('m121220_015328_...')

#5 [internal function]: MigrateCommand->actionUp(Array)

#6 /var/www/htdocs/yii/framework/console/CConsoleCommand.php(172): ReflectionMethod->invokeArgs(Object(MigrateCommand), Array)

#7 /var/www/htdocs/yii/framework/console/CConsoleCommandRunner.php(67): CConsoleCommand->run(Array)

#8 /var/www/htdocs/yii/framework/console/CConsoleApplication.php(91): CConsoleCommandRunner->run(Array)

#9 /var/www/htdocs/yii/framework/base/CApplication.php(169): CConsoleApplication->processRequest()

#10 /var/www/htdocs/yii/framework/yiic.php(33): CApplication->run()

#11 /var/www/trackstar/protected/yiic.php(7): require_once('/var/www/htdocs...')

#12 /var/www/trackstar/protected/yiic(4): require_once('/var/www/tracks...')

#13 {main}dami@ubuntu:/var/www/trackstar/protected$ ^C

dami@ubuntu:/var/www/trackstar/protected$

timkan2012 · December 20, 2012, 10:58am

Ok,I find solution from other discussion.

change config/console.php code as following and yiic migrate will run normally.


	'components'=>array(

		/*'db'=>array(

			'connectionString' => 'sqlite:'.dirname(__FILE__).'/../data/testdrive.db',

		),

		// uncomment the following to use a MySQL database

		*/

		'db'=>array(

			'connectionString' => 'mysql:host=localhost;dbname=trackstar',

			'emulatePrepare' => true,

			'username' => 'yourname',

			'password' => 'yourpassword',

			'charset' => 'utf8',

		),

and chapter5 migrations/m121220_105024_create_issue_user_and_assignment_tables update as following.


<?php


class m121220_105024_create_issue_user_and_assignment_tables extends CDbMigration

{

	/*

	public function up()

	{

	}


	public function down()

	{

		echo "m121220_105024_create_issue_user_and_assignment_tables does not support migration down.\n";

		return false;

	}

	*/




        // Use safeUp/safeDown to do migration with transaction


        public function safeUp()


        {


        //create the issue table


        $this->createTable('tbl_issue', array(


        'id' => 'pk',


        'name' => 'string NOT NULL',


        'description' => 'text',


        'project_id' => 'int(11) DEFAULT NULL',


        'type_id' => 'int(11) DEFAULT NULL',


        'status_id' => 'int(11) DEFAULT NULL',


        'owner_id' => 'int(11) DEFAULT NULL',


        'requester_id' => 'int(11) DEFAULT NULL',


        'create_time' => 'datetime DEFAULT NULL',


        'create_user_id' => 'int(11) DEFAULT NULL',


        'update_time' => 'datetime DEFAULT NULL',


        'update_user_id' => 'int(11) DEFAULT NULL',


        ), 'ENGINE=InnoDB');


        //create the user table


        $this->createTable('tbl_user', array(


        'id' => 'pk',


        'username' => 'string NOT NULL',


        'email' => 'string NOT NULL',


        'password' => 'string NOT NULL',


        'last_login_time' => 'datetime DEFAULT NULL',


        'create_time' => 'datetime DEFAULT NULL',


        'create_user_id' => 'int(11) DEFAULT NULL',


        'update_time' => 'datetime DEFAULT NULL',


        'update_user_id' => 'int(11) DEFAULT NULL',


        ), 'ENGINE=InnoDB');


        //create the assignment table that allows for many-to-many relationship between projects and users


        $this->createTable('tbl_project_user_assignment', array(


        'project_id' => 'int(11) DEFAULT NULL',


        'user_id' => 'int(11) DEFAULT NULL',


        'PRIMARY KEY (`project_id`,`user_id`)',


        ), 'ENGINE=InnoDB');


        //foreign key relationships


        //the tbl_issue.project_id is a reference to tbl_project.id


        $this->addForeignKey("fk_issue_project", "tbl_issue", "project_id", "tbl_project", "id", "CASCADE", "RESTRICT");


        //the tbl_issue.owner_id is a reference to tbl_user.id


        $this->addForeignKey("fk_issue_owner", "tbl_issue", "owner_id", "tbl_user", "id", "CASCADE", "RESTRICT");


        //the tbl_issue.requester_id is a reference to tbl_user.id


        $this->addForeignKey("fk_issue_requester", "tbl_issue", "requester_id", "tbl_user", "id", "CASCADE", "RESTRICT");


        //the tbl_project_user_assignment.project_id is a reference to tbl_project.id


        $this->addForeignKey("fk_project_user", "tbl_project_user_assignment", "project_id", "tbl_project", "id", "CASCADE", "RESTRICT");


        //the tbl_project_user_assignment.user_id is a reference to tbl_user.id


        $this->addForeignKey("fk_user_project", "tbl_project_user_assignment", "user_id", "tbl_user", "id", "CASCADE", "RESTRICT");


        }





        public function safeDown()


        {


        $this->truncateTable('tbl_project_user_assignment');


        $this->truncateTable('tbl_issue');


        $this->truncateTable('tbl_user');


        $this->dropTable('tbl_project_user_assignment');


        $this->dropTable('tbl_issue');


        $this->dropTable('tbl_user');


        }


}

lektrikpuke · December 17, 2013, 11:15pm

Yes, INSERT INTO tbl_project (name, description, create_time, create_user_id, update_time, update_user_id) VALUES (‘test’, ‘testing’, ‘’, null, ‘’, null);

/* 2013-12-17 17:09:47 [Localhost] / / SQL Error (1292): Incorrect datetime value: ‘’ for column ‘create_time’ at row 1 */

And it should throw an error, ‘’ is not the same thing as NULL.

lektrikpuke · December 22, 2013, 2:56pm

This fixes the problem:

find array(‘create_time, update_time’, ‘safe’), in project model and replace with

array(‘create_time, update_time’, ‘default’, ‘setOnEmpty’ => true, ‘value’ => null),