Home » RADICORE development » Application Development » How can I achieve retrieving data from different tables and marking them as printed?
How can I achieve retrieving data from different tables and marking them as printed? [message #1816] |
Tue, 11 November 2008 06:50 ![Go to next message Go to next message](/theme/default/images/down.png) |
bonzo_bcn
Messages: 152 Registered: June 2008
|
Senior Member |
|
|
I'm really desperated with this issue, maybe a more experienced programmer can help.
I have data from different tables:
players who play in teams
coaches of teams
players who have signed up for activities
players who have signed up for events
my client asks for a screen to show selected data of all of them, so that he can print labels and export to csv, but once they have been printed, they should be marked as 'printed' or 'exported'.
The select to show this data is an union between 4 selects.
My first approach was too create a view in mysql, but as it didn't have primary keys, radicore messes up, which I believe is normal.
My second aproach was to fill a table with all this data, then the user exports and prints the data of this table, and the record is updated as printed or exported. The problem I'm facing with this is that once the data is saved from the original data into this table, if they change any field of the source, the data in the table doesn't change. OTOH I don't like this approach as it can easily lead to inconsistencies.
If I can't sort out this, I will put an export and print button on every source screen, but then the client should go and look into four different places instead of one.
(I've spent more time with this than with the whole application).
If anyone has any idea on how to solve this please let me know.
|
|
|
|
|
|
Re: How can I achieve retrieving data from different tables and marking them as printed? [message #1820 is a reply to message #1816] |
Tue, 11 November 2008 11:04 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
bonzo_bcn
Messages: 152 Registered: June 2008
|
Senior Member |
|
|
the problem is that it gets data from different places and there isn't a possible primary key as some fields that could make the pk can be null depending on the case.
This is the select:
SELECT participante.fotopart, participante.participante_id,participante.nompart,
participante.apellido1,participante.apellido2,participante.fecnac,
participante.catsalut, categoria.nomcat,temporada.nomtemp,
deporte.nomdep,entidad.noment,equipo.nomequi
FROM participante
JOIN part_equipo_xref ON participante.participante_id=part_equipo_xref.participante_id
JOIN equipo ON part_equipo_xref.equipo_id = equipo.equipo_id
JOIN entidad ON equipo.entidad_id= entidad.entidad_id
JOIN deporte ON equipo.deporte_id = deporte.deporte_id
JOIN categoria ON equipo.categoria_id = categoria.categoria_id
JOIN temporada ON temporada.temporada_id = equipo.temporada_id
WHERE tippart='E'
UNION
SELECT participante.fotopart, participante.participante_id,participante.nompart,
participante.apellido1,participante.apellido2,participante.fecnac,
participante.catsalut, categoria.nomcat,temporada.nomtemp,
deporte.nomdep,entidad.noment,equipo.nomequi
FROM participante
JOIN equipo ON participante.participante_id = equipo.entrenador_id
JOIN entidad ON equipo.entidad_id= entidad.entidad_id
JOIN deporte ON equipo.deporte_id = deporte.deporte_id
JOIN categoria ON equipo.categoria_id = categoria.categoria_id
JOIN temporada ON temporada.temporada_id = equipo.temporada_id
WHERE tippart='N'
UNION
SELECT distinct participante.fotopart, participante.participante_id,participante.nompart,
participante.apellido1,participante.apellido2,participante.fecnac,
participante.catsalut, null,temporada.nomtemp,
actividad.nomact,null,null
FROM participante
inner JOIN part_actividad_fe_lu_xref ON participante.participante_id=part_actividad_fe_lu_xref.participante_id
inner JOIN actividad_fe_lu ON part_actividad_fe_lu_xref.actividad_fe_lu_id= part_actividad_fe_lu_xref.actividad_fe_lu_id
inner JOIN actividad ON actividad.actividad_id = actividad_fe_lu.actividad_id
inner JOIN temporada ON actividad.temporada_id = temporada.temporada_id
UNION
SELECT distinct participante.fotopart, participante.participante_id,participante.nompart,
participante.apellido1,participante.apellido2,participante.fecnac,
participante.catsalut, null,temporada.nomtemp,
trobada.nomtro,null,null
FROM participante
inner JOIN part_trob_xref ON participante.participante_id=part_trob_xref.participante_id
inner JOIN trobada ON trobada.trobada_id= part_trob_xref.trobada_id
inner JOIN temporada ON trobada.temporada_id = temporada.temporada_id
|
|
|
|
|
Re: How can I achieve retrieving data from different tables and marking them as printed? [message #1823 is a reply to message #1822] |
Tue, 11 November 2008 11:34 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
It is not possible to select a record from the database, then update it to say that it has been processed, if it does not have a primary key.
If you build a database table which does not have a primary key then you are asking for trouble.
If you are trying to merge data from several tables and treat them as if they come from a single table then this is only possible if those tables have identical structures. This is one of the limitations of a UNION in SQL. If they do not have identical structures then you must use alias names in the select list to force the list of column names to be identical.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
Re: How can I achieve retrieving data from different tables and marking them as printed? [message #1826 is a reply to message #1816] |
Wed, 12 November 2008 15:27 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
bonzo_bcn
Messages: 152 Registered: June 2008
|
Senior Member |
|
|
Ok, so I did this:
function _cm_pre_getData ($where, $where_array, $fieldarray=null)
{
// construct default SELECT and FROM clauses using parent relations
$this->sql_select = "participante.fotopart, participante.participante_id,participante.nompart,
CONCAT(participante.apellido1,' ', participante.apellido2) as apellido1,participante.fecnac,
participante.catsalut, categoria.categoria_id,temporada.temporada_id,
deporte.deporte_id,
entidad.entidad_id,
equipo.equipo_id,
0 as actividad_id,
0 as actividad_fe_lu_id,
0 as trobada_id,
'E' as tipo,
part_equipo_xref.impreso,
part_equipo_xref.exportado";
// $this->sql_groupby = null;
// $this->sql_having = null;
$this->sql_from = "participante
JOIN part_equipo_xref ON participante.participante_id=part_equipo_xref.participante_id
JOIN equipo ON part_equipo_xref.equipo_id = equipo.equipo_id
JOIN entidad ON equipo.entidad_id= entidad.entidad_id
JOIN deporte ON equipo.deporte_id = deporte.deporte_id
JOIN categoria ON equipo.categoria_id = categoria.categoria_id
JOIN temporada ON temporada.temporada_id = equipo.temporada_id
WHERE tippart='E'
UNION
SELECT participante.fotopart, participante.participante_id,participante.nompart,
CONCAT(participante.apellido1,' ', participante.apellido2) as apellido1,participante.fecnac,
participante.catsalut, categoria.categoria_id,temporada.temporada_id,
deporte.deporte_id,
entidad.entidad_id,
equipo.equipo_id,
0 as actividad_id,
0 as actividad_fe_lu_id,
0 as trobada_id,
'N',
equipo.impreso_ent,
equipo.exportado_ent
FROM participante
JOIN equipo ON participante.participante_id = equipo.entrenador_id
JOIN entidad ON equipo.entidad_id= entidad.entidad_id
JOIN deporte ON equipo.deporte_id = deporte.deporte_id
JOIN categoria ON equipo.categoria_id = categoria.categoria_id
JOIN temporada ON temporada.temporada_id = equipo.temporada_id
WHERE tippart='N'
UNION
SELECT participante.fotopart, participante.participante_id,participante.nompart,
CONCAT(participante.apellido1,' ', participante.apellido2) as apellido1,participante.fecnac,
participante.catsalut, categoria.categoria_id,temporada.temporada_id,
deporte.deporte_id,
entidad.entidad_id,
equipo.equipo_id,
0 as actividad_id,
0 as actividad_fe_lu_id,
0 as trobada_id,
'D',
equipo.impreso_del,
equipo.exportado_del
FROM participante
JOIN equipo ON participante.participante_id = equipo.delegado_id
JOIN entidad ON equipo.entidad_id= entidad.entidad_id
JOIN deporte ON equipo.deporte_id = deporte.deporte_id
JOIN categoria ON equipo.categoria_id = categoria.categoria_id
JOIN temporada ON temporada.temporada_id = equipo.temporada_id
WHERE tippart='D'
UNION
SELECT distinct participante.fotopart, participante.participante_id,participante.nompart,
CONCAT(participante.apellido1,' ', participante.apellido2) as apellido1,participante.fecnac,
participante.catsalut, 0,temporada.temporada_id,
0,
0,
0,
actividad.actividad_id,
part_actividad_fe_lu_xref.actividad_fe_lu_id,
0,
'A',
part_actividad_fe_lu_xref.impreso,
part_actividad_fe_lu_xref.exportado
FROM participante
inner JOIN part_actividad_fe_lu_xref ON participante.participante_id=part_actividad_fe_lu_xref.participante_id
inner JOIN actividad_fe_lu ON part_actividad_fe_lu_xref.actividad_fe_lu_id= part_actividad_fe_lu_xref.actividad_fe_lu_id
inner JOIN actividad ON actividad.actividad_id = actividad_fe_lu.actividad_id
inner JOIN temporada ON actividad.temporada_id = temporada.temporada_id
UNION
SELECT distinct participante.fotopart, participante.participante_id,participante.nompart,
CONCAT(participante.apellido1,' ', participante.apellido2) as apellido1,participante.fecnac,
participante.catsalut, 0,temporada.temporada_id,
0,
0,
0,
0,
0,
trobada.trobada_id,
'T',
part_trob_xref.impreso,
part_trob_xref.exportado
FROM participante
inner JOIN part_trob_xref ON participante.participante_id=part_trob_xref.participante_id
inner JOIN trobada ON trobada.trobada_id= part_trob_xref.trobada_id
inner JOIN temporada ON trobada.temporada_id = temporada.temporada_id";
return $where;
} // _cm_pre_getData
Then I did the following:
function _cm_changeConfig ($where, $fieldarray){
$this->primary_key = array('participante_id','categoria_id','temporada_id','deporte_id','entidad_id','equipo_id','actividad_fe_lu_id','trobada_id');
return $fieldarray;
}
I get to see the data in the list1 transaction, however, when I select several records for printing or exporting to csv, the where clause messes up and adds strange things.
With the debugger I found that in _cm_pre_getData
$where contains ( participante_id='' AND categoria_id='' AND temporada_id='' AND deporte_id='' AND entidad_id='' AND equipo_id='' AND actividad_fe_lu_id='' AND trobada_id='' )
Why aren't the fields populated with the data I selected?
What else should I modify in order to make this 'pseudo' class work?
|
|
|
|
|
Goto Forum:
Current Time: Sat Aug 24 09:04:00 EDT 2024
Total time taken to generate the page: 0.08059 seconds
|