This topic is locked

List of dates

3/19/2018 9:00:14 AM
PHPRunner General questions
J
jackheitzer@gmail.com author

Hi,
I would like to get some tips of you guys how to achieve the following
I have a list with this week's occupation of an orchestra.




SELECT

bezetting.ID1,

bezetting.dirigent,

bezetting.key1,

bezetting.keycomp,

bezetting.fluit,

bezetting.hoorn,

bezetting.gitaar,

bezetting.bas,

bezetting.drums,

bezetting.perc1,

bezetting.perc2,

bezetting.perc3,

`show`.ID,

`show`.aanvang,

`show`.datum,

month(datum) AS maand,

year(datum) AS jaar,

week(datum) AS week

FROM bezetting

RIGHT OUTER JOIN `show` ON `show`.ID = bezetting.ID1

ORDER BY `show`.datum, `show`.aanvang


I would like to have a list of dates for each individual person when they played that week.
Can anyone give me tips on how or where to start?
Best regards, Jack

admin 3/20/2018

Do you have an example of what kind of output you looking to get?

J
jackheitzer@gmail.com author 3/21/2018

Hi Sergey,
Thank you very much for your time.
Here's an example of what I am looking for.


(This image should represent 2 pages)
The drop-down boxes at the top should be occupied by the names of the people who appear in the list (see original post).
From whatever name you choose from the drop-down list (lookupWizard), a list of dates show up.
Would this be possible with PHPrunner 9.8 or do I need the enterprise edition?
Thanks,
Jack

admin 3/21/2018

You need to write custom code for this kind of dropdown. Dropdown should list of musicians names and on selection should refresh the page showing a search results page for the query like this:

WHERE Gitaar='Jack' or Bas='Jack' or KeyComp='Jack' ...


You can check this for inspiration but it is going to be more complicated as you need to account for multiple fields:

https://xlinesoft.com/phprunner/docs/add_dropdown_list_box_with_values_for_search.htm

J
jackheitzer@gmail.com author 3/22/2018



You need to write custom code for this kind of dropdown. Dropdown should list of musicians names and on selection should refresh the page showing a search results page for the query like this:

WHERE Gitaar='Jack' or Bas='Jack' or KeyComp='Jack' ...


You can check this for inspiration but it is going to be more complicated as you need to account for multiple fields:

https://xlinesoft.com/phprunner/docs/add_dropdown_list_box_with_values_for_search.htm


Thanks!

jadachDevClub member 3/22/2018

Why not just use a report?

Y
YCH 3/22/2018

Could a pivot table query be of any help ?

J
jackheitzer@gmail.com author 3/22/2018



Why not just use a report?


I've tried a report but I can only process one column at a time. Or do you know of other options to get the job done?
Thanks, Jack

J
jackheitzer@gmail.com author 3/22/2018



Could a pivot table query be of any help ?


How would I do that?
Thanks, Jack

Y
YCH 3/22/2018



How would I do that?
Thanks, Jack


I am not so sure a pivot table query is still the right way.
But you could make a "view" with a "select ...union all... " query as data source like this:
*

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.dirigent as persoon

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.key1

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.keycomp

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.gitaar

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.bas

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.hoorn

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.fluit

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.drums

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.perc1

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.perc2

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.perc3

FROM bezetting
;
****

I did not test this.
*


You would end up with this select list :

persoon Id datum aanvang
arthur 1 donderdag 22 maart 2018 20:00 arthur 2 woensdag 21 maart 2018 20:00
arthur 4 vrijdag 23 maart 2018 20:00
arthur 5 zaterdag 24 maart 2018 15:00
... guillaume 1 donderdag 22 maart 2018 20:00
...
hilde 3 donderdag 22 maart 2018 20:00
hilde 4 vrijdag 23 maart 2018 20:00
...jack 4 vrijdag 23 maart 2018 20:00
jack 3 donderdag 22 maart 2018 20:00
jack 2 woensdag 21 maart 2018 20:00
jack 1 donderdag 22 maart 2018 20:00
jack 5 zaterdag 24 maart 2018 15:00
...
jan 5 zaterdag 24 maart 2018 15:00
...
joeke 4 vrijdag 23 maart 2018 20:00
...
johannes 2 woensdag 21 maart 2018 20:00
johannes 1 donderdag 22 maart 2018 20:00
...

mark 5 zaterdag 24 maart 2018 15:00
mark 3 donderdag 22 maart 2018 20:00
mark 2 woensdag 21 maart 2018 20:00
...

rené 3 donderdag 22 maart 2018 20:00
rené 1 donderdag 22 maart 2018 20:00
...
rogier 4 vrijdag 23 maart 2018 20:00
rogier 3 donderdag 22 maart 2018 20:00
...
rosite 2 woensdag 21 maart 2018 20:00
rosite 5 zaterdag 24 maart 2018 15:00
***
this could be the view data source for your page.

J
jackheitzer@gmail.com author 3/26/2018



I am not so sure a pivot table query is still the right way.
But you could make a "view" with a "select ...union all... " query as data source like this:
*

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.dirigent as persoon

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.key1

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.keycomp

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.gitaar

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.bas

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.hoorn

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.fluit

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.drums

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.perc1

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.perc2

FROM bezetting

union all
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.perc3

FROM bezetting
;
****

I did not test this.
*


You would end up with this select list :

persoon Id datum aanvang
arthur 1 donderdag 22 maart 2018 20:00 arthur 2 woensdag 21 maart 2018 20:00
arthur 4 vrijdag 23 maart 2018 20:00
arthur 5 zaterdag 24 maart 2018 15:00
... guillaume 1 donderdag 22 maart 2018 20:00
...
hilde 3 donderdag 22 maart 2018 20:00
hilde 4 vrijdag 23 maart 2018 20:00
...jack 4 vrijdag 23 maart 2018 20:00
jack 3 donderdag 22 maart 2018 20:00
jack 2 woensdag 21 maart 2018 20:00
jack 1 donderdag 22 maart 2018 20:00
jack 5 zaterdag 24 maart 2018 15:00
...
jan 5 zaterdag 24 maart 2018 15:00
...
joeke 4 vrijdag 23 maart 2018 20:00
...
johannes 2 woensdag 21 maart 2018 20:00
johannes 1 donderdag 22 maart 2018 20:00
...

mark 5 zaterdag 24 maart 2018 15:00
mark 3 donderdag 22 maart 2018 20:00
mark 2 woensdag 21 maart 2018 20:00
...

rené 3 donderdag 22 maart 2018 20:00
rené 1 donderdag 22 maart 2018 20:00
...
rogier 4 vrijdag 23 maart 2018 20:00
rogier 3 donderdag 22 maart 2018 20:00
...
rosite 2 woensdag 21 maart 2018 20:00
rosite 5 zaterdag 24 maart 2018 15:00
***
this could be the view data source for your page.


Hi,
I'm looking into this and it seems to help a lot. Thanks!
Jack