Using Delphi with MySQL

We've finished setting up the server and the connector. So start Delphi and create a new application. Drop an ADOConnection and ADOQuery (rename it q1) component on the form. Then drop a listview (rename it lv), tmemo, and five buttons on the form.

You do not have to use ADO components if you don't want to. It is entirely optional. There are other components that you can use instead. 

Click on the listview, go to its Viewstyle property in the object inspector and select vsReport. Then double click on the listview component, and you should see a column editor window. Add the following fields:

•  Aid
  
•  Auth
  
•  Title
  
•  Article Text
  
•  Date Published

We don't actually want the "Aid" field to be displayed when the application runs, so on the forms' OnCreate event add the following code:

lv.Columns.Items[0].Width:=0;

This will hide the column at runtime. Close down the column editor.

Your form should look something like this:

Add a form to the project and drop three edits, a memo and two buttons onto it. We will use the second form to both insert new articles into the database and to edit the articles. Now, go back to form one, double click on the first button and add the following code:

procedure TForm1.BitBtn1Click(Sender: TObject);
var
li:tlistitem;
begin
q1.Close;
q1.SQL.Text:=memo1.text;
q1.Open;
while not q1.Eof do
begin
li:=lv.Items.Add;
li.Caption:=q1.fieldbyname('aID').Value;
li.SubItems.Add(q1.fieldbyname('auth').text);
li.SubItems.Add(q1.fieldbyname('title').text);
li.SubItems.Add(q1.fieldbyname('article_body').AsString);
li.SubItems.Add(q1.fieldbyname('date_published').AsString);
q1.Next;
end;
end;

The first thing that the code does is execute the query as contained in the memo. I've decided to put the query in the memo because it will give you the flexibility to retrieve the data in any way you like:

q1.Close;
q1.SQL.Text:=memo1.text;
q1.Open;

In our case, it retrieves all the articles in the database and stores them in the query object. The next step is to display the results in the listview by looping through the results and adding them to the listview one by one until it reaches the end of the file (Eof):

while not q1.Eof do
begin
li:=lv.Items.Add;
li.Caption:=q1.fieldbyname('aID').Value;
li.SubItems.Add(q1.fieldbyname('auth').text);
li.SubItems.Add(q1.fieldbyname('title').text);
li.SubItems.Add(q1.fieldbyname('article_body').AsString);
li.SubItems.Add(q1.fieldbyname('date_published').AsString);
q1.Next;
end;

Normally, I don't display the article ID on any component, but in this case it is practical to have it handy, as subsequent calls will require it.

The second button brings up the second form, which we will use to insert a new article:

procedure TForm1.BitBtn3Click(Sender: TObject);
begin
form2.Button1.Enabled:=false;
form2.show;
end;

The code basically uses the "show" procedure to make the second form visible.

Add the following code to the third button labeled "Delete article:"

procedure TForm1.BitBtn4Click(Sender: TObject);
begin
if aid <> 0 then
begin
q1.close;
q1.SQL.Text:='DELETE FROM articles WHERE aid=:num';
q1.Parameters.ParamByName('num').Value:=aid;
q1.ExecSQL;
showmessage('Message deleted');
end
else
showmessage('Please select an article to delete');
end;

The code first checks to see whether aid has a value in it. Aid is assigned a value when the user selects an article. If it does have a value, then the code runs a delete query and displays a message showing its status:

if aid <> 0 then
begin
q1.close;
q1.SQL.Text:='DELETE FROM articles WHERE aid=:num';
q1.Parameters.ParamByName('num').Value:=aid;
q1.ExecSQL;
showmessage('Message deleted');
end

Otherwise it shows an error message:

else
showmessage('Please select an article to delete');
end;

Add the following code to the "edit" button:

procedure TForm1.Button1Click(Sender: TObject);
begin
if lv.Items.Count > 0 then
begin
form2.BitBtn1.Enabled:=false;
form2.edit:=true;
form2.id:=aid;
form2.Edit1.Text:= lv.Selected.SubItems.Strings[0]; //name
form2.Edit2.Text:= lv.Selected.SubItems.Strings[1]; //title
form2.Edit3.Text:= lv.Selected.SubItems.Strings[3]; //date
form2.Memo1.Lines.Text:=lv.Selected.SubItems.Strings[2];  //text
form2.Show;
end
else
begin
showmessage('Please select an article to update.');
end;
end;

All that this code does is transfer the selected article items to the second form, where they will be updated and sent back to the database.