fleaphp Development Guide-6. Accessing the database

Visit | Development Guide | data | database

In this chapter we will look at the database access features of fleaphp. Experience fleaphp's outstanding automated CRUD capabilities.

Connecting to a database

Create the subdirectory TestDB in the Htdocs directory and create the file test1.php file in the subdirectory, as follows:

<?phprequire('../FLEA/FLEA.php');__FLEA_PREPARE();// 准备数据库连接信息$dsn = array(    'driver'        => 'mysql',    'host'          => 'localhost',    'login'         => 'root',    'password'      => '',    'database'      => 'test',);// 获取数据库访问对象$dbo =& get_dbo($dsn);// 连接到数据库if ($dbo->connect()) {    echo 'Connect to database successed.';}?>

Please note that we do not invoke the run () function here. So we need to invoke the __flea_prepare () function to initialize the fleaphp run environment. and the __flea_prepare () function should be invoked after the application settings are modified with Register_app_inf () or Set_app_inf ().

Now start APM Express, execute http://localhost/testDB/test1.php through the browser, and if everything works, you should see Connect to database successed information.

If the following error message appears, the root user password for the MySQL database is incorrect. Please modify the password information in the above code.

Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'root'@'localhost'

Create a record

Next, we use phpMyAdmin to execute the following SQL statement in the test database. This creates a table named posts in the test database.

CREATE TABLE `posts` (  `post_id` int(11) NOT NULL auto_increment,  `title` varchar(255) NOT NULL,  `body` text NOT NULL,  `created` int(11) default NULL,  `updated` int(11) default NULL,  PRIMARY KEY  (`post_id`)) DEFAULT CHARSET=gb2312;

Now we modify the contents of test1.php as follows:

<?phprequire('../FLEA/FLEA.php');// 准备数据库连接信息$dsn = array(    'driver'        => 'mysql',    'host'          => 'localhost',    'login'         => 'root',    'password'      => '',    'database'      => 'test',);// 指定数据库连接设置，TableDataGateway 会自动取出 dbDSN 设置来连接数据库set_app_inf('dbDSN', $dsn);// 初始化 FleaPHP 运行环境__FLEA_PREPARE();// 由于 FLEA_Db_TableDataGateway 并不是自动载入的，因此需要明确载入load_class('FLEA_Db_TableDataGateway');// 从 FLEA_Db_TableDataGateway 派生 Posts 类class Posts extends FLEA_Db_TableDataGateway{    // 指定数据表名称    var $tableName = 'posts';    // 指定主键字段名    var $primaryKey = 'post_id';}// 构造 Posts 实例$modelPosts =& new Posts();// 创建一条新记录，并返回新记录的主键值$newPostId = $modelPosts->create(array(    'title' => 'First post',    'body'  => 'First post body',));echo $newPostId;?>

When you rerun test1.php in the browser, you will see output 1. If you refresh a few more times, you can see that the number is growing. Now go to phpMyAdmin and browse the posts table and you'll find that the table has been inserted into the data.

Note the created field of the red circle in the image above. Although our code above does not provide a value for the field when inserting a record with $modelPosts->create (), the value of the field is automatically populated.

When the data table has a field named created, Created_at, or created_on, and the field type is an integer or a date, when the record is inserted into the datasheet. Fleaphp will automatically populate the field with the current time. Similarly, the updated, Updated_at, or updated_on fields are populated with the current time when the record is updated.

About Flea_db_tabledatagateway

Flea_db_tabledatagateway is a class that provides automated CRUD operations. The developer must derive its own class from the class. Each flea_db_tabledatagateway derived class corresponds to a data table. For example, the Posts class in the above code corresponds to the data table Posts. Flea_db_tabledatagateway derived classes are called table data portals in fleaphp applications.

Each table data entry needs to define a required member variable:

• $tableName Specify a data table for this table data entry

If the datasheet has more than one primary key field, use $primaryKey to specify the primary key field to use. When not specified with $primaryKey , Flea_db_tabledatagateway automatically determines the primary key field name based on the data table definition.

Once the above definition is complete, a table data entry class is ready. As long as the class is instantiated, you can perform various operations on the corresponding data table for that class.

Reading and updating records

Now we continue to modify the previous code to add the following:

/** * .... 接续上面的代码片段 */echo "

Now you can see two slightly different outputs by performing test1.php in the browser.

The first output is the record content that is fetched with the $modelPosts->find (). The second section of data is the $modelPosts->update () update and then removed the record content.

Comparing the two outputs, you can see that the title field and the updated field of the second output are modified.

Delete a record

There are two main ways to delete records, one by using the Remove () method of the table data entry, with a single record as the parameter. Another method is to use the REMOVEBYPKV () method to make parameters for the primary key value of the record.

// 取出所有 title 字段值为 'First post' 的记录$posts = $modelPosts->findAll(array('title' => 'First post'));// 删除这些记录foreach ($posts as $post) {    $modelPosts->remove($post);    // 或者使用    // $modelPosts->removeByPkv($post[$modelPosts->primaryKey]);}

In this chapter, we have a cursory look at the basic operations provided by the table data entry provided by fleaphp. In subsequent chapters, we will see other powerful features of the table data entry.