How to connect MySQL in Prado Framework

Hello!

This is my first article tutorial in this blog, today my friend have a problem connecting SQL databases with PRaDO framework. I have used this framework several years ago, and it’s quite easy to make a website with Prado. This time I will write how to connect MySQL with Prado, insert data, and display it in the web page.

First, I’m using XAMPPLITE as webserver, XAMPPLITE is lite version of XAMPP, but if you are using XAMPP it’s ok. Download XAMPP.

After you finish the XAMPP download, continue with download Prado framework. Download Prado.

Install the XAMPP, and extract the prado framework in htdocs folder.

The optional prado-cli.php PHP script file in the framework directory provides command line tools to perform various tedious takes in Prado. The prado-cli.php can be used to create Prado project skeletons, create initial test fixtures, and access to an interactive PHP shell. We will use command line tool to generate our prado project.

1. Make setPath

First I made setpath.bat to declare PHP path, so we can called any file in PHP path easier.

Make a file setpath.bat ( I put this file in C:\)

PATH=C:\xampplite\php\;

set the path to your php directory.

2. Generate the Project

Open the command prompt, and execute the setpath.bat by typing the filename setpath.bat

Go to htdocs

Let’s generate project, execute this line

php C:\xampplite\htdocs\prado\framework\prado-cli.php -c pradocontact

When the generate success the command line will display line like this

3. Open in browser

Open your browser and navigate to http://localhost/pradocontact/ if you see the prado welcome page, congratulations you have been generate prado project successly.

4. Create Database

Navigate to http://localhost/phpmyadmin/ and create new database. Type “pradocontact” in create new database field and hit create button.

Then make a new table called “contact” with 2 field (id and name). Set the id field type into tinyint, auto_increment, and as primary key. Set the field name type as varchar and give the length 100.

CREATE TABLE `contact` (
`id` TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 100 ) NOT NULL
) ENGINE = MYISAM ;

After the table created, insert 2 rows data into contact table.
INSERT INTO `contact` ( `id` , `name` )
VALUES (
'1', 'leo'
), (
'2', 'ganda'
);

5. Set connection in application.xml

Browse your project folder and open the protected folder C:\xampplite\htdocs\pradocontact\protected

You will see a file “application.xml”, open it and edit.

We make a ConnectionString to mysql database, dbname is our database that used for this project. Set the username and password with your database account.

6. Create Active Record

To use active record, I will make a new folder called database inside the protected folder to store the ActiveRecord file. After the database folder created, open the application.xml once again, and declare the database path.

Your application.xml should be look like this

After you finish editing the application.xml, let’s generate Active Record from our database in MySQL.
Open the command prompt again, go to C:\xampplite\htdocs\pradocontact\ and execute php C:\xampplite\htdocs\prado\framework\prado-cli.php shell .

execute generate contact Application.database.ContactRecord

Open your database folder with explorer, and you will find a new file called ContactRecord.php, try to open it.

7. Test The Active Record

Ok, let test our Active Record using command line tools, type “ContactRecord::finder()->findAll()” and hit enter. See the image below.

The command line return an array with 2 record from the mySQL database and that means our activeRecord running well!

8. Edit Home.page

paste this code on your Home.page file

<%@ Title="Contact List" %>

<h1>Contact List</h1>

<a href="<%= $this->Service->constructUrl('insert')%>">Create New Contact</a>
<br/>

<com:TDataGrid ID="UserGrid"
DataKeyField="id"
AutoGenerateColumns="false"
>

<com:TBoundColumn
HeaderText="ID"
DataField="id" />

<com:TBoundColumn
HeaderText="Name"
DataField="name" />

</com:TDataGrid>


9. Make new file Home.php

Paste this code on your Home.php

UserGrid->DataSource=ContactRecord::finder()->findAll();
        // binds the data to interface components
        $this->UserGrid->dataBind();
    }
}
?>

10. Make a new file insert.page


<%@ Title="Insert Contact" %>

<com:TForm>

<h1>Insert Contact</h1>

<span>Name:</span>
<br/>
<com:TTextBox ID="Name" />

<com:TButton Text="Create" OnClick="createButtonClicked" />

</com:TForm>


11. Make a new file insert.php

class insert extends TPage
{
    
    public function createButtonClicked($sender,$param)
    {
        if($this->IsValid)  // when all validations succeed
        {
            // populates a UserRecord object with user inputs
            $ContactRecord=new ContactRecord;
            $ContactRecord->name=$this->Name->Text;
            
 
            // saves to the database via Active Record mechanism
            $ContactRecord->save();
 
            // redirects the browser to the homepage
            $this->Response->redirect($this->Service->DefaultPageUrl);
        }
    }
}

?>

Navigate your browser to http://localhost/pradocontact/ you will see this page

Ok, thanks for reading. Have a nice day!

Download Source
Download SQL