2. PHPRunner General questions
  3. count of records
This topic is locked

count of records

 		2/28/2006 2:13:01 AM
PHPRunner General questions
A
 arrowhot author

I have two tables, a parent and a child....or master, whatever.....
One is for brewers, with a brewery number, and another is for coasters, with a related brewery number that printed the coaster. (a 1 brewer to many coasters relationship...kinda like beer.)
(Any of you who are beer drinkers might want to browse my site if you've had a few...)
When I list some brewers it has a column for "coasters", which is a clickable link to go see a list of coasters that were produced by that brewer, with a "return to master table' link.
When I list the brewers, instead of just showing the word "coasters" all the way down the column, I'd like to put that word at the top of the column (perhaps as a sortable field?) and show the actual record of coasters count that match for that brewer.
I can write the query in SQL and test it with PHPmyadmin, but where do I insert this code into PHPrunner to make it work with the list program? And is it easy to change the header and have it sortable?
I could probably hack this into the resulting code but that would be messy every time I want to change something.
I'm running V3 beta (with no issues) soon to upgrade.
Thanks!
Gil
(Sergey, it's a fantastic product, .....by the way).
Sergey Kornilov admin 2/28/2006

Gil,
sure, you can do this.
Here is the recommended way:

  1. Setup master-details relationship as you already did.
  2. Modify SQL query for brewery table - add count of coasters field. Give it an alias, i.e. coastercount.
  3. Proceed to Formattingtab and select View type - Hyperlinkfor brewery tabe primary key field. Let it be BreweryID.
  4. Set the following URL prefix:
    coasters_list.php?masterkey=

for this field and choose Display field content - coastercount.

Don't forget to add BreweryID field to List page on Choose fieldstab.

5. Build the pages. BreweryID field is displayed as Hyperlink now and shows count of coasters.
Now modify generated brewery_list.php file to remove old coasters link.

You'll need to change WriteTableHeader and loopRSfunctions in this file.
A
 		arrowhot author 2/28/2006

Hi Sergey,
Maybe you can help me out...I'm having trouble figuring out how to structure the query syntax....
Here's a short form of the SQL syntax in my current phprunner build:
select `bNumber`,

`bName`,

`bCode`

From `brewery`
to that, I want to add a count(*) of records that are in the 'coaster' table where coasters.brewery=brewery.bnumber
and alais it as 'coastercount' for display on a list page.
Can you help me to rewrite this query to work in your example, so it doesn't corrupt anything in my current application? Also, do I need to add 'brewery' to the front of each field, so they would be like 'brewery.bName' for example?
(learning php and mysql...)
Thanks,
Gil
A
 		arrowhot author 3/6/2006



Thanks Sergey! I'll give it a try.
Gil
Sergey Kornilov admin 3/7/2006

Gil,
please see my example:

select (select count(*) from coasters where coasters.brewery=brewery.bnumber) as coasters.coastercount,

brewery`.bNumber`,

brewery`.`bName`,

brewery`.`bCode`

from brewery



However this query will work correctly only in MySQL 5.x.