Simple Mysql example

In a previous blog post, I was unable to use HTML5 Builder to connect to MYSQL and show a edit grid of some kind.

So in order to get some experience, I am taking some small steps.

I should say up front that I do not think this is the "right" way to do this.  I think I am supposed to use a thing called a "Data Snap" and a "T something" but the Embarcadero documentation is very poor so I suspect it will take me a while to work that out.

1 THE END OBJECTIVE
is to have a screen with a input box where I put a number.  We will then jump to Mysql and search a table for the record where the index column matches the data entered by the user.

We enter the "4",
Click "Button1"
The program looks up the mysql table and populates the 4 hello and bla from the mysql database.









2 GET MYSQL SORTED
I have WAMP server running on my development machine.
Use PHPMYADMIN to create a table called testtable within the database called test.  Then put in a couple of rows of data.

3 WRITE A STAND ALONE PROGRAM IN PHP
I wrote a small PHP script to recover and print the data.
This was to ensure that I could connect to mysql on the machine and not have password issues and so on.

<?PHP
// 1 Connect to Mysql
$host_name = "localhost";
$dbuser = "root";  
$dbpass = "ahha";
$dbname = "test";
$result = @mysql_pconnect($host_name, $dbuser, $dbpass);
 if (!$result)
      {
      echo "<BR>user_management_system.php  ";
      echo "<BR>cant connect to MYSQL";
      echo "<BR>mysql_errno".mysql_errno().": ".mysql_error()."<BR>";
      return false;
      }
 else
      {
       echo "\n<BR> Connected to MYSQL";
       };
// 2 Select the database
if (!@mysql_select_db($dbname))
      {
      echo "<BR>test1.php  ";
      echo "<BR>cant select the $dbname database";
      echo "<BR>mysql_errno".mysql_errno().": ".mysql_error()."<BR>";
      return false;
      }
else
    {
    Echo "\n<BR> Selected $dbname ";
    };

// 3 Look up the table in the database
$query = "select * from testtable";
$result = mysql_query($query);
if (!($result))   
        {echo "<br><B>test1.php</B>query did not happen result is false
         <BR>query= $query
         <BR>result= $result
         <BR>mysql error no= ".mysql_errno().": ".mysql_error()."<BR>";
        };
$number_of_results = mysql_num_rows($result);
echo "\n<BR>number_of_results = $number_of_results";

// 4 Print it out
echo "<TABLE border=1>";
for ($i=0; $i<$number_of_results; $i++)
    {
     $row = mysql_fetch_array($result, MYSQL_ASSOC);
     if ($i==0)  // Print the heading as the first row of output.
        {
         echo "\n<TR>";
         foreach($row as $key => $value)
            {
              echo "<TD> $key </TD>";
            };
          echo "\n</TR>";
        };
     // Now print the data from the table
     echo "\n<TR>";
     foreach($row as $key => $value)
        {
        echo "<TD> $value </TD>";
        };
     echo "\n</TR>";
   
   
    };
echo "</TABLE>";
?>

4 DESIGN THE PAGE IN EMBACADERO HTML5 BUILDER
Over on the right hand side of the user interface, see "Tool Palette",
Open it and then expand the "Standard Section"
Then drag and drop the controls as shown.

5 CODE
In the design window, double click "Button1" that you placed earlier.
This will cause HTML5 Builder to open in the "Code" window
 and you will see it has created some code and the cursor is positioned in the ON CLICK handler for Button1.

First I typed in the function called "getmydata($myindex)"  down the bottom.  (The complete listing is below).
Then inside the event handler for Button1, I access the data that the user typed intyo Edit1
            $myindex = $this->Edit1->Text;
Then I call getmydata and pass it $myindex ie the info the user typed in.
I get back an array with values for 'col1', 'col2' and 'col3' these are the rathere mundame names I gave the columns in my mysql table.

Then I replace (is that the way you say it) the text for the three lables
        $this->Label1->Caption =     $row['col1'];
        $this->Label2->Caption =     $row['col2'];
        $this->Label3->Caption =     $row['col3'];

The complete program is as shown next.  Note that some of this is created by Embacadero HTML5 builder and some is the stuff I typed in.

<?php
require_once("rpcl/rpcl.inc.php");
//Includes
use_unit("forms.inc.php");
use_unit("extctrls.inc.php");
use_unit("stdctrls.inc.php");
use_unit("db.inc.php");
use_unit("dbtables.inc.php");
use_unit("actnlist.inc.php");

//Class definition
class Page3 extends Page
{
    public $Memo1 = null;
    public $Label3 = null;
    public $Label2 = null;
    public $Edit1 = null;
    public $Button1 = null;
    public $Label1 = null;
    function Button1Click($sender, $params)
        {
        $myindex = $this->Edit1->Text;
        $row = getmydata($myindex) ;
        $this->Label1->Caption =     $row['col1'];
        $this->Label2->Caption =     $row['col2'];
        $this->Label3->Caption =     $row['col3'];
        }
}

global $application;

global $Page3;

//Creates the form
$Page3=new Page3($application);

//Read from resource file
$Page3->loadResource(__FILE__);

//Shows the form
$Page3->show();
// ---------------------------------------------------------------------------

function getmydata($myindex)
{

// 1 Connect to Mysql
$host_name = "localhost";
$dbuser = "root";
$dbpass = "fmmcdj";
$dbname = "test";
$result = @mysql_pconnect($host_name, $dbuser, $dbpass);
 if (!$result)
      {
      echo "<BR>user_management_system.php  ";
      echo "<BR>cant connect to MYSQL";
      echo "<BR>mysql_errno".mysql_errno().": ".mysql_error()."<BR>";
      return false;
      }
 else
      {
       //echo "\n<BR> Connected to MYSQL";
       };
// 2 Select the database
if (!@mysql_select_db($dbname))
      {
      echo "<BR>test1.php  ";
      echo "<BR>cant select the $dbname database";
      echo "<BR>mysql_errno".mysql_errno().": ".mysql_error()."<BR>";
      return false;
      }
else
    {
    //Echo "\n<BR> Selected $dbname ";
    };

// 3 Look up the table in the database
$query = "select * from testtable where col1='$myindex'";
$result = mysql_query($query);
if (!($result))
        {echo "<br><B>test1.php</B>query did not happen result is false
         <BR>query= $query
         <BR>result= $result
         <BR>mysql error no= ".mysql_errno().": ".mysql_error()."<BR>";
        };
$number_of_results = mysql_num_rows($result);
//echo "\n<BR>number_of_results = $number_of_results";
// 4 Get the data
$row = mysql_fetch_array($result, MYSQL_ASSOC);
// 5 return
return $row;
};
?>