2. PHPRunner General questions
  3. Problem with joining values in a special table view
This topic is locked

Problem with joining values in a special table view

 		7/12/2006 4:43:21 AM
PHPRunner General questions
D
 dieter author

This SQL Statement is in a table View of the Table _posteingang:
select `_person`.`ID`,

`Name`,

`Vorname`,

`Geschlecht`,

`Geburtsdatum`,

`Grund`,

`Wohnort`,

`Straße`,

`Absender`,

`Datum`,

`PEID`,

`Vorgang`,

`Person`,

`tgbNr`,

`Sb`,

`Oe`,

`Bemerkungen`,

`Art`,

`erledigt`

From `_person` inner join `_posteingang` on (`_person`.`ID`=`_posteingang`.`PEID`)
In the generated Page Copy of _posteingang I would like to view One Field where the personal fields Name, Vorname, Geburtsdatum Wohnort are joined. Is there a possibility to do that, perhaps with Concat or something or with a Table event (listpage on load) ?
thanks
Dieter
J
 		Jane 7/12/2006

Hi,
use & for concat two or more fields.

See my example:

select `_person`.`ID`,

`Name`&' '&`Vorname`&' '&`Geburtsdatum` as `full info`,

`Geschlecht`,

`Grund`,

`Wohnort`,

`Straße`,

`Absender`,

`Datum`,

`PEID`,

`Vorgang`,

`Person`,

`tgbNr`,

`Sb`,

`Oe`,

`Bemerkungen`,

`Art`,

`erledigt`

From `_person` inner join `_posteingang` on (`_person`.`ID`=`_posteingang`.`PEID`)
D
 		dieter author 7/12/2006

Hi,

use & for concat two or more fields.

See my example:



Does not work.

The field full info only displays a Zero (0) ?
J
 		Jane 7/12/2006

Sorry for my fault.

Here is the correct query:

select `_person`.`ID`,

concat(`Name`,' ',`Vorname`,' ',`Geburtsdatum`) as `full info`,

`Geschlecht`,

`Grund`,

`Wohnort`,

`Straße`,

`Absender`,

`Datum`,

`PEID`,

`Vorgang`,

`Person`,

`tgbNr`,

`Sb`,

`Oe`,

`Bemerkungen`,

`Art`,

`erledigt`

From `_person` inner join `_posteingang` on (`_person`.`ID`=`_posteingang`.`PEID`)
D
 		dieter author 7/12/2006

Sorry for my fault.

Here is the correct query:



Now it works fine but on las Question:
Is it possible to have the Field formated ?

A line break between Name Vorname (line break) Geburtsdatum

Or Geburtsdatum (which is a Datefield) formatted as dd.mm.yyyy

or Name in bold ?
J
 		Jane 7/12/2006

You can use html tag to format full info field.

Here is an example:

...

concat('<i>',`Name`,'</i>
',`Vorname`,'
',`Geburtsdatum`) as `full info`,

...



And then select HTML in the View as dropdown box on the Formatting tab.
To format datetime field use date_format MySQL function.
D
 		dieter author 7/13/2006

You can use html tag to format full info field.

Here is an example:
And then select HTML in the View as dropdown box on the Formatting tab.
To format datetime field use date_format MySQL function.


Hello Again,
now it works well but when i like to sort in listview I only see a picture named file.gif

A search in this field is also not possible.
How to do ?
Best greets
Dieter
J
 		Jane 7/13/2006

Hi,
sorting works correct for concat fields on my test box.

Could you give me a URL to your pages where I can see this List page?
You need to modify include/dbcommon.php file for search.

Open this file, find function GetFullFieldName and replace following line:

if("TableName"==$table && $field=="full info") return "`TableName`.`full info`";



with this one:

if("TableName"==$table && $field=="full info") return "concat('<i>',`Name`,'</i>
',`Vorname`,'
',`Geburtsdatum`)";



where TableName is your actual table name.
D
 		dieter author 7/14/2006

Hi,

sorting works correct for concat fields on my test box.

Could you give me a URL to your pages where I can see this List page?
You need to modify include/dbcommon.php file for search.

Open this file, find function GetFullFieldName and replace following line:
with this one:
where TableName is your actual table name.



Now every works fine besides the date format.
In the Table Person I have formated the field Geburtsdatum as short Date (dd.mm.yyyy =German) and the look is corect. In the "Concat Field" full info is the format yyyy-mm-dd 00:00:00 and not like above German.
Best Greetings and a beautiful weekend
Dieter
Alexey admin 7/14/2006

Dieter,
use DATE_FORMAT function to format your date field.

I.e.

DATE_FORMAT(`Geburtsdatum`,'%d.%m.%Y')



instead of just

`Geburtsdatum`
D
 		dieter author 7/14/2006

Dieter,

use DATE_FORMAT function to format your date field.

I.e.
instead of just


Where (which Step) should I use This ?
J
 		Jane 7/14/2006

Use this function in your SQL query:

...

concat('<i>',`Name`,'</i>
',`Vorname`,'
',DATE_FORMAT(`Geburtsdatum`,'%d.%m.%Y') as `full info`,

...



Make the same changes in the include/dbcommon.php file:

if("TableName"==$table && $field=="full info") return "concat('<i>',`Name`,'</i>
',`Vorname`,'
',DATE_FORMAT(`Geburtsdatum`,'%d.%m.%Y')";
D
 		dieter author 7/14/2006

Use this function in your SQL query:

Make the same changes in the include/dbcommon.php file:


Thank You
You are great!
Dieter