This sections describes some basics regarding programming with the database components. Please do not use any other method to access your database. Using the standard functions of PHsPeed ensures that – together with the use of PDO – the application stays safe and is not prone to sql injection or XSS threats. 


DBTable

Suppose that you have created an onclick event on a button (button_1) and that you want to set the filter of a DBTable (dbtable_1). In the ‘working with events’ section, you can find how to invoke an event. 

The correct statement (for PHsPeed) would be:


$$dbtable_1->setFilter(‘some filter);  


or


$$dbtable_1->filter='some filter';


The main difference is that the first method does more than 'just assign the value'. It also ensures that the filter is stored into your session space and will reuse the filter over your transactions. Therefore this method is advised. The second way the value will only be available in the current running transaction.


In both cases you need to supply the full fiter like: 'pers_no=12345';


Another way to set filters is to use variables. You can apply these also in the QueryBuilder. I.e.: 


name > :name;


To match the variable use the following functions;



      public function setFilterStringField($field, $value) 

      public function setFilterNumberField($field, $value) 

      public function setFilterIntegerField($field, $value) 


I.e. 

   setFiltertSring(':name',  'myvalue');


If you are an experienced developer then you might wonder what the $$ means. You can find the explanation in the section 'the magic of $$'.



DBQuery

DBQuery is mostly used for (complex) queries and visualizing the results in a grid. It is important to know that you use parametrized queries if you use inserts, updates or deletes as that is more hacker proof. If you construct your sql dynamically and add variables directly then verify that this variable is sql injection safe.

The regular way of performing a query is to add SQL (or Query). 


$$dbquery_1->setSQL(‘select * from mytable where mykey=:mykey’);
$$dbquery_1->setStringFieldByName(‘:mykey’, $myvar);
$$dbquery_1->open();


First of all, why use setSQL and not $$dbquery_1->SQL=something? Actually embedded SQL requires a few steps, and one of them is that the SQL statement needs to be ‘prepared’. setSQL does everything to get your statement ready for execution. But you can use the direct assignment, but then you have to apply the prepare as well. Advise: use setSQL. 


In the above code we use a parameterized query. This is the most safe way of assigning and binding data to variables. Depending on the datatype you can use:


    public function SetLobFieldByName($theField, $theValue) 
    public function SetBoolFieldByName($theField, $theValue) 
    public function SetStringFieldByName($theField, $theValue) 
    public function SetIntegerFieldByName($theField, $theValue)

DBSimpleQuery

DBSimpleQuery is mainly used for internal processing. In principle it is similar as described above (suppose the simplefield is dbsquery_1)


$$dbsquery_1->setSQL(‘select * from mytable where mykey=:mykey’);
$$dbsquery_1->setStringFieldByName(‘:mykey’, $myvar);
$$dbsquery_1->open();


As there no datasource, you must obtain the data by yourself.

DBSimpleQuery: reading all data

Suppose that you have defined your query by using the Query Designer and want to fill a dropdown field with a lookup. The lookupfield is called ddcourse.

There is no setSQL as the query is already set in the query property of the simpleQuery ($$q). The simpleOpen will open the query. The parameter behind simpleOpen tell the system to retrieve the first row. If there is none then there is no loop.


 $r=$$q->simpleOpen(true); // open query and retrieve first row
  while(!$$q->eof) {                 // as long as there is a valid result

    $$ddcourse-> AddItem($r['courseid'], $r['coursedesc'].' '.$r['coursedate'].' '.$r['coursetime']);
    $r=$$q->simpleRead();

  }


Of course this is a very simple sample. If you need to set the query by code you would get:


$$q->setSQL=’select * from courses’;


Followed by the above code.

DBSimpleQuery: updates

Actually this is more of the same:


$$qp->setSQL("update participant_course set courseid=:cidnew where participantid=:pid  and courseid=:cid");

             $$qp->setStringFieldByName(':cidnew', $$ddcourse->value);
             $$qp->setStringFieldByName(':pid', $partid);
             $$qp->setStringFieldByName(':cid', $courseid);

             $$qp->simpleExecSQL();