How to use DbHelper() to retrive data?

[YogiYang]

Hello,

After reading on forums I found out that we can use DbHelper() to execute any arbitrary SQL statements on database and retrieve its results.

But how can we show the retrived result in a well formatted table (list view like) where in users can click on a column header to sort it, search in it, etc. along with Pagination in Customize View? Again I need to show this in a page in provided space only. It should not sprawl outside given space; for this probably I will have to use <div> tag

Are there any detailed tutorials on this or any help material.

TIA

Yogi Yang

[kirondedshem]

use DbHelper() to execute any arbitrary SQL statements on database and retrieve its results
if you have a custom file you can do like:
$project_db =& DbHelper();
$the_reslut = $project_db->LoadRecordset("select id,code,name from product");
foreach( $the_reslut as $the_row)
{
//get value from array
//$the_name = $the_row["name"];
}

in a well formatted table (list view like)
In my understanding, as long as the list is not drawn by phpmaker linking to the database table by itself, then you have to manage all that on your own.
If you are using a custom file then you will have to manage the sorting of the table,pagination etc on your own

-If you are ok with a default listview like list( with styles but nopagination or sorting), then you can use
echo $project_db->ExecuteHtml("select id,code,name from product");
to return you query in a well styled table.

But if you want pagination, you loop through the records and then use bootstrap tables or any other jquery pagination libraries to auto paginate for you.

On another note if all you want is to draw a table but pagination and sorting is neccessary why can't connect the table and use a phpmaker list view and just play with its width to limit its space.

[sangnandar]

Phpmaker is basically a set of class/functions stacked in proper order to generate your (php) apps.
Dbhelper() is just the subset. Provide some basic class/function to integrate custom files.
If you need all table functionality (select, sort, conditional row format, etc) then go with the complete set.
If you need to integrate custom file or work with a "non-table" php, go with dbhelper().

[YogiYang]

sangnandar wrote:
If you need all table functionality (select, sort, conditional row format,
etc) then go with the complete set.

Can you please explain this in greater details.

TIA

Yogi Yang

[mobhar]

YogiYang wrote:
Can you please explain this in greater details.

It means, you should use the built-in PHPMaker feature for generating pages that have complete functionalities, such as List, Sort, Filter, conditional row format, Add, Edit, Delete, etc.

[kirondedshem]

what this means is:
By default phpmaker can create list pages with all its features when the list is coming from a table i the database.

For cases where the data you want to display is not in one table bu tou still want your list to have all the phpmaekr features then you a number of options.

1. asses the data you want to show, ie look at the various tables or views that you need to connect so as to have your quary.
   In most cases you can Then make a view out of that query and connect phpmaker to that.
   You even use master/detail relationship from tables and views of ineterest if need be.
   Basically witha well organised database, you can mostly create views or relationships between tables and views to get yo data.
   So this will still ensure your resulting list page has the sorting, export etc

2. In rare cases when the desired data can not be geerated from just one query or can not be put into one view.
   Or even if it can, The resulting view or single query is so slow to the extent that its better to do most of the quering using php.
   First use a basic query that will ensure the record count on the resulting list is wahta you want and Then You make create custom fields for other columns which you can then populate programatically using row_rendering.

3. In even rarerer cases when the sql needed to generate the list is not something always predefined. ie cases where you would maybe want a list where the number of columns varies according to a use case, or the sql itself can also change at runtime depending on the use case.
   You can overide the sql used to generate the page
   You can then even create custom fileds to act as place holders, then determine required query at runtime and override the sql itsself in page_load event to match your exact needs.
   You can them decode which custom field to asign which values as well as column names, and which custom fileds you dont want to show, etc etc.

There is alot of creativity needed here, so it might not be straight forward.

You can look at my post here where I was also facing a similar problem and when someone gave me a hint on custom fields I made use of them to draw a report with dynamic cnumber of columns, dynamic column names depending, and dynamic sql query to pick deisred value for each column. viewtopic.php?f=4&t=40910

[teskakun]

we use ew_LoadRecordset()

sample:

$rs = ew_LoadRecordset("SELECT your query here");

$ROWS = $rs->GetRows();

if (count($ROWS) > 0) {
foreach($ROWS AS $ROW){
?>
<?php echo $ROW['Item'];?>
<?php echo $ROW['ItemCode'];?>
<?php echo $ROW['ItemDesc'];?>
<?php echo $ROW['Qty'];?>

} // end of foreach rows

} // end of if count rows