Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.
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
bonzo_bcn is currently offline  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.

Report message to a moderator

Re: How can I achieve retrieving data from different tables and marking them as printed? [message #1817 is a reply to message #1816] Tue, 11 November 2008 07:41 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
Senior Member
I do not see the problem. Once the export has been performed you simply update each record to show that it has been exported, then when you return to the selection screen you exclude those items which have already been exported.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: How can I achieve retrieving data from different tables and marking them as printed? [message #1818 is a reply to message #1816] Tue, 11 November 2008 10:39 Go to previous messageGo to next message
bonzo_bcn is currently offline  bonzo_bcn
Messages: 152
Registered: June 2008
Senior Member
But is the approach of populating a table with other table data correct?
Ideally a view would do the job as it's 'real time' but you can't put pk in mysql views.

Report message to a moderator

Re: How can I achieve retrieving data from different tables and marking them as printed? [message #1819 is a reply to message #1818] Tue, 11 November 2008 10:52 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
Senior Member
Why can't you perform a SELECT with JOINs to construct the data array that you need. In other words, perform the same SQL that you have to construct your view, but without without using a view.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

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 messageGo to next message
bonzo_bcn is currently offline  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

Report message to a moderator

Re: How can I achieve retrieving data from different tables and marking them as printed? [message #1821 is a reply to message #1820] Tue, 11 November 2008 11:09 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
Senior Member
If your data is coming from several different tables, and each table has a different primary key then your data array needs to include the table name. This will then enable you to iterate through the data array and perform a separate update for each table.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: How can I achieve retrieving data from different tables and marking them as printed? [message #1822 is a reply to message #1816] Tue, 11 November 2008 11:21 Go to previous messageGo to next message
bonzo_bcn is currently offline  bonzo_bcn
Messages: 152
Registered: June 2008
Senior Member
Another problem I face is that all the fields should be searchable, but as you can see, some fields come from different tables.
So what would you suggest?: add the table name to the select statement, then extend the select statement of a class to show this data?

even if I add the table name, I can't define a primary key. I think one of the key problems is not having a primary key.

Report message to a moderator

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 messageGo to next message
AJM is currently offline  AJM
Messages: 2371
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

Report message to a moderator

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 messageGo to next message
bonzo_bcn is currently offline  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?

Report message to a moderator

Re: How can I achieve retrieving data from different tables and marking them as printed? [message #1827 is a reply to message #1816] Wed, 12 November 2008 16:07 Go to previous messageGo to next message
bonzo_bcn is currently offline  bonzo_bcn
Messages: 152
Registered: June 2008
Senior Member
I'm almost there...
Radicore is including the prefix table in the where clause, for example:
part_trob_xref.trobada_id
I don't know where it's getting the prefix, but is there any way to tell radicore not to include it?

Report message to a moderator

Re: How can I achieve retrieving data from different tables and marking them as printed? [message #1828 is a reply to message #1827] Wed, 12 November 2008 17:16 Go to previous message
AJM is currently offline  AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
Senior Member
You need to step through with your debugger to see where the incorrect WHERE string is being generated, then you can discover why it is being generated.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Previous Topic: $where clause problem with list1 and read
Next Topic: Performance problem
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ] [ PDF ]

Current Time: Sat Nov 30 07:11:41 EST 2024

Total time taken to generate the page: 0.01533 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.1.3.
Copyright ©2001-2023 FUDforum Bulletin Board Software