Mysql--> Navigate, Edit, Post, Delete

[mhmda]

Hi all,

I have just started using D4php, actually this is my first experience in WEB development, till now I used other products from (CodeGear), Delphi and C++ Builder.
My first project was to create a basic database program from "0" and the goal or the purpose is:

1. Connect to MySQL database.
2. Connect to Table.
3. Display data.
4. Let user: Navigate throw the records.
5. Let user do the basic things: new,edit,delete.
6. Find a way to use the project and run it without D4php

Before starting to explain every step you need to install MySQL, you may check this link: MySQL :: MySQL Downloads
and install the Wamp server check this link Download WampServer 2 - Download PHP, apache, MySQL
We need these when we get to step 6.

1. Let's create a Table in MySQL, I use SQLYog, it's a GUI for managing MySQL (you may try the trial): Download - SQLyog MySQL Frontend, MONyog MySQL Monitoring Tool
I have created a Database named "users" and a table named "test". when you create the database and the table you may change their properties for those who use language different from English like Arabic or chinese, I use UTF-8 (unicode). the default user name for MySQL database is "root" without password (empty). and I have created three fields:
A. id: id (integer) for the student (the user can't change it) it's special field to manage the data later.
B. name (char): holds the student full name.
C. info (blob): it's rich text that is display using richedit control.
In order to make the connection we need:
A. MySQLDatabase component named: MySQLDatabase1.
B. MySQLTable component named: MySQLTable1
And will use these two components to display data and nivigate throw, and for other actions: save/modify we will use:
A. Database component named: Database1.
B. Query component named: Query1.
Well your going to ask "why using so mutch components?? why not doing all actions in one table/query??"
The answer is: It is worked just this way, actually before posting this issue I have search this forum for anwser to my question and I found people who had the same problem on doing all action in one table or one query but the answers that I found were not usefull and till the release of V.3 of D4php (I realy don't know when...) I do it this way, so let's get to job.

A. MySQLDatabase1:
- Connected=true
- Database name: users
- host: local host (you have to run the Wamp server) or the address of your server.
- User name: root (case sensitive)
- User Password: leave it empty (blank)

B. MySQLTable1:
- Active=true
- Database=MySQLDatabase1
- Limit count=-1
- Limit start=-1
- Table name=test

A. Database1:
- Connected=true
- Database name: users
- host: local host (you have to run the Wamp server) or the address of your server.

B. Query1:
- Active=true
- Limit count=-1
- Limit start=-1
- SQL=select * from test
- Table name: test

And now drag a text box and richedit (leave the default names) and put them in your page, also drag a hiddenfield named: HiddenField1.
And we gonna use these buttons:
A. new
B. save
C. delete
D. next
E. previous

---- Don't use datasource ------------------------------------------
After fighting many hours with datasoure I found it usefull just with datagrid under certian circumstances...
And don't use ->next() and ->prior() functions. these functions will work just once and after that "0", why? I really don't know :-)

Let's get to code:

--------------------------------------------------------------------------
1. MySQLTable1AfterOpen (event):

$this->Edit1->Text=$this->MySQLTable1->fieldget("name");
$this->id->Value=$this->MySQLTable1->fieldget("id");
$this->RichEdit1->Text=$this->MySQLTable1->fieldget("info");
$this->HiddenField1->Value=1;

--------------------------------------------------------------------------
2. nextClick (event):

$this->HiddenField1->Value++;//(you may validate bofore doing this)
$this->MySQLTable1->RecNo=$this->HiddenField1->Value;
$this->Edit1->Text=$this->MySQLTable1->fieldget("name");
$this->RichEdit1->Text=$this->MySQLTable1->fieldget("info");
$this->id->Value=$this->MySQLTable1->fieldget("id");

--------------------------------------------------------------------------
3. previousClick (event):

$this->HiddenField1->Value--; //(you may validate bofore doing this)
$this->MySQLTable1->RecNo=$this->HiddenField1->Value;
$this->Edit1->Text=$this->MySQLTable1->fieldget("name");
$this->RichEdit1->Text=$this->MySQLTable1->fieldget("info");
$this->id->Value=$this->MySQLTable1->fieldget("id");

--------------------------------------------------------------------------
4. saveClick (event):

$i=9;//this is the id of the student
//you may get this value from settings table for example
//and increase it by 1 every time you use it
//for our example i'll use static value (won't work twice
//you don't wanna two students with the same id !!!!
$tmp1=$this->Edit1->Text;
$tmp2=$this->RichEdit1->Text;
$this->Query1->SQL="insert into test (name,info,id) values
('$tmp1','$tmp2','$i')";
$this->Query1->refresh();
//do a refresh
$this->MySQLTable1->close();
$this->MySQLTable1->open();
}
else //MySQLTable1->State==dsBrowse
{
$i=$this->id->Value;
$tmp1=$this->Edit1->Text;
$tmp2=$this->RichEdit1->Text;
$this->Query1->SQL="update test set name='$tmp1',info='$tmp2' where id='$i'";
$this->Query1->refresh();
}

--------------------------------------------------------------------------
5. newClick (event):

$this->MySQLTable1->append();//just for checking the "State"
$this->Edit1->Text="";//clear data
$this->RichEdit1->Text="";//clear data

--------------------------------------------------------------------------
5. deleteClick (event):

$i=$this->id->Value;
$this->Query1->SQL="delete from test where id='$i'";
$this->Query1->refresh();
//do a refresh
$this->MySQLTable1->close();
$this->MySQLTable1->open();

Let us run it.....
1. We already installed the Wamp server and it's default directory: c:\wamp. so you may create a new directory "test" under: c:\wamp\www, and we gonna put all files there using D4php.
2. From D4php menu choose "Tools" --> "Deployment Wizard" and choose our destination Dir. c:\wamp\www\test and the D4php will copy all needed files to this Dir.
3. open IE or Firefox or... and in the addres bar type: localhost/test/unit1.php (unit1.php is the name of the unit you work on).

I know that you may modify the code to improve it, but I want it to be that way to make easy to people who want to understand and create a basic program that would run 100%.

It's late for me now I'm gonna take my little baby to her bed and I'm gonna sleeeeeeeeeep.... I hope it gonna be usefull to you.