generate->table datas missing with join request

Hi everybody,

I'm facing a weird issue with the table library. I'd like to retrieve all the datas of 2 tables, and display it in a "raw table". The goal is not to display a beautiful table, but to allow people to export datas in a excel file and then work on it.

Here is the controller:

Code:

public function view_all() {

$this->load->library('table');


$this->db->from ('patient as p');
$this->db->join ('operation as o',
"o.zkf_pat = p.zkp_pat " );
$query = $this->db->get();

Template::set('query', $query);
Template::set_view('all_operations');
Template::render();

Pretty basic i guess...

And my view:

Code:

<?php

echo $this->table->generate($query);

?>

i attached a screen capture of the result. As you can see, nothing in the zkf_pat column, only the titles (fake datas of course :p ) which should be in the next one ! And same for a few others columns...

I have to say i'm a bit confused, and i don't understand why some datas are missing, but not all of them.

I checked my phpmyadmin configuration, but found nothing special for me...

Is it from the library table, or maybe somewhere else (SQL query, sql table structure...no idea !)

Thanks for your help

M.

Attached Files

Thumbnail(s)
   

What does the data look like when you run the query elsewhere, or use print_r($query->result())? It would also be helpful to see the HTML for the generated table (copied from View Source in the browser), rather than a picture of the table.

If you want people to export the library in Excel, you may want to use a library to generate an xlsx or csv file rather than going through the trouble of generating an HTML table.

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide

What does the data look like when you run the query elsewhere, or use print_r($query->result())? It would also be helpful to see the HTML for the generated table (copied from View Source in the browser), rather than a picture of the table.

If you want people to export the library in Excel, you may want to use a library to generate an xlsx or csv file rather than going through the trouble of generating an HTML table.

Hi mwhitney,

Thanks for your answer, and sorry for answering back so late. i did a var_dump on ($query->result()), and the interesting thing is that i have an array with missing columns, even those who are not empty. The most striking one is the foreign key on which the two tables are linked...
I tried another way to display the data. Actually it's not very different: i'm doing a foreach loop through the datas generated. I did it this way because ultimately i'd like to create a check box list to display selected columns...

Here is the html generated:

<div id="datatable">

<table class="table table-striped table-hover table-condensed" id="datatable">

<th> fields=>extracted from a $fields = $this->db->list_fields('patient'); and same for table 'operation'. In bold, one of the missing column in the results

<tr>
<th>pat_zkp_pat</th><th>pat_zkf_pat</th><th>pat_nom</th><th>pat_prenom</th><th>pat_sex</th><th>pat_cepage</th><th>pat_dob</th><th>pat_medecin_referent</th><th>pat_mode_dialyse</th><th>pat_tparente</th><th>pat_poids</th><th>pat_taille</th><th>pat_cause_insuff_renale</th><th>pat_type_greffe</th><th>pat_donneur_receveur</th><th>pat_created_on</th><th>pat_modified_on</th><th>pat_created_by</th><th>pat_modified_by</th><th>pat_deleted</th><th>ope_zkp_ope</th><th>ope_zkf_pat</th><th>ope_titre</th><th>ope_ddate_operation</th><th>ope_ddate_entree</th><th>ope_ddate_sortie</th><th>ope_poids</th><th>ope_taille</th><th>ope_operateur</th><th>ope_anesthesiste</th><th>ope_cote_greffe_receveur_d_g</th><th>ope_rein_preleve_d_g</th><th>ope_rein_greff_d_g</th><th>ope_position_rein_receveur_d_g</th><th>ope_cote_rein_receveur_d_g</th><th>ope_tpolaire_o_n</th><th>ope_tcommentaire_polaire</th><th>ope_tcommentaire_rein</th><th>ope_creat_attendue</th><th>ope_cause_insuff_renale</th><th>ope_tincision</th><th>ope_tdebut_insufflation</th><th>ope_tfin_liberation_colique</th><th>ope_tcontrole_uretere</th><th>ope_tcontrole_artere</th><th>ope_tcontrole_veine</th><th>ope_theparine</th><th>ope_nheparine_mg</th><th>ope_tdissection_rein</th><th>ope_tclampage_uretere</th><th>ope_tclampage_artere</th><th>ope_tdeclampage_artere</th><th>ope_tclampage_veine</th><th>ope_tdeclampage_veine</th><th>ope_tfin_anastomose_ureterale</th><th>ope_tdebut_perfusion_rein</th><th>ope_liquide_perfusion_rein</th><th>ope_volume_liquide_perfusion_rein</th><th>ope_tduree_ischemie_chaude</th><th>ope_tfin_intervention</th><th>ope_complication_post_op</th><th>ope_detail_complication_post_op</th><th>ope_creat_sortie</th><th>ope_created_on</th><th>ope_modified_on</th><th>ope_created_by</th><th>ope_modified_by</th><th>ope_deleted</th><th>ope_transfusion_per_op</th><th>ope_nbre_culot_gr_per_op</th><th>ope_test</th> </tr>

the var_dump generated...:


<tr>
Array
(
[0] => stdClass Object
(
[zkp_pat] => 1
[zkf_pat] => 1
[nom] => last_name
[prenom] => first_name
[sex] => Homme
[cepage] =>
[dob] => dob
[medecin_referent] => Dr. x
[mode_dialyse] =>
[tparente] => sibling
[poids] => 23
[taille] => 1.90
[cause_insuff_renale] =>
[type_greffe] => data
[donneur_receveur] => data
[created_on] => timestamp
[modified_on] => timestamp
[created_by] => 1
[modified_by] => 1
[deleted] => 0
[zkp_ope] => 1

here, there might be [zkf_pat] => 1


[titre] => titre opération
[ddate_operation] => 2006-01-01
[ddate_entree] => 2000-01-01
[ddate_sortie] => 2001-01-01
[operateur] =>
[anesthesiste] =>
[cote_greffe_receveur_d_g] =>
[rein_preleve_d_g] => Droit
[rein_greff_d_g] =>
[position_rein_receveur_d_g] =>
[cote_rein_receveur_d_g] =>
[tpolaire_o_n] => Non
[tcommentaire_polaire] => ras
[tcommentaire_rein] =>
[creat_attendue] =>
[tincision] => 12:10:00
[tdebut_insufflation] => 12:11:00
[tfin_liberation_colique] => 12:12:00
[tcontrole_uretere] => 12:13:00
[tcontrole_artere] => 12:14:00
[tcontrole_veine] => 12:15:00
[theparine] => 12:16:00
[nheparine_mg] => 45
[tdissection_rein] => 12:17:00
[tclampage_uretere] => 12:18:00
[tclampage_artere] => 12:19:00
[tdeclampage_artere] =>
[tclampage_veine] => 12:20:00
[tdeclampage_veine] =>
[tfin_anastomose_ureterale] =>
[tdebut_perfusion_rein] => 12:22:00
[liquide_perfusion_rein] => Igl1
[volume_liquide_perfusion_rein] => 200
[tduree_ischemie_chaude] => 12:21:00
[tfin_intervention] => 12:25:00
[complication_post_op] => Non
[detail_complication_post_op] => RAS
[creat_sortie] => 43
[transfusion_per_op] => Oui
[nbre_culot_gr_per_op] => 3
[test] =>
)

[1] => stdClass Object
(
[zkp_pat] => 2
[zkf_pat] => 2
[nom] => last_name
[prenom] => first_name
[sex] =>
[cepage] =>
[dob] => dob
[medecin_referent] => Dr. x
[mode_dialyse] =>
[tparente] =>
[poids] =>
[taille] =>
[cause_insuff_renale] =>
[type_greffe] => data
[donneur_receveur] => data
[created_on] => 2015-10-07 23:22:49
[modified_on] => 2015-10-07 23:25:53
[created_by] => 1
[modified_by] => 1
[deleted] => 0
[zkp_ope] => 2
here, there might be [zkf_pat] => 2
[titre] => receveuse
[ddate_operation] => 2000-02-01
[ddate_entree] =>
[ddate_sortie] =>
[operateur] =>
[anesthesiste] =>
[cote_greffe_receveur_d_g] =>
[rein_preleve_d_g] =>
[rein_greff_d_g] =>
[position_rein_receveur_d_g] =>
[cote_rein_receveur_d_g] =>
[tpolaire_o_n] =>
[tcommentaire_polaire] =>
[tcommentaire_rein] =>
[creat_attendue] =>
[tincision] =>
[tdebut_insufflation] =>
[tfin_liberation_colique] =>
[tcontrole_uretere] =>
[tcontrole_artere] =>
[tcontrole_veine] =>
[theparine] =>
[nheparine_mg] =>
[tdissection_rein] =>
[tclampage_uretere] =>
[tclampage_artere] =>
[tdeclampage_artere] =>
[tclampage_veine] =>
[tdeclampage_veine] =>
[tfin_anastomose_ureterale] =>
[tdebut_perfusion_rein] =>
[liquide_perfusion_rein] =>
[volume_liquide_perfusion_rein] =>
[tduree_ischemie_chaude] =>
[tfin_intervention] =>
[complication_post_op] =>
[detail_complication_post_op] =>
[creat_sortie] =>
[transfusion_per_op] => Oui
[nbre_culot_gr_per_op] => 2
[test] =>
)

)

In bold, the number one equal to 'zkp_ope', and there might be another number right after 'zkf_pat' which is not selected from the join table query...


<tr><td>1</td><td>1</td><td>first_name</td><td>last_name</td><td>Homme</td><td></td><td>dob</td><td>Dr. x</td><td></td><td>sibling</td><td>23</td><td>1.90</td><td></td><td>data</td><td>data</td><td>2015-09-29 21:14:46</td><td>2015-10-09 14:51:15</td><td>1</td><td>1</td><td>0</td><td>1</td><td>titre opération</td><td>2006-01-01</td><td>2000-01-01</td><td>2001-01-01</td><td></td><td></td><td></td><td>Droit</td><td></td><td></td><td></td><td>Non</td><td> ras </td><td></td><td></td><td>12:10:00</td><td>12:11:00</td><td>12:12:00</td><td>12:13:00</td><td>12:14:00</td><td>12:15:00</td><td>12:16:00</td><td>45</td><td>12:17:00</td><td>12:18:00</td><td>12:19:00</td><td></td><td>12:20:00</td><td></td><td></td><td>12:22:00</td><td>Igl1</td><td>200</td><td>12:21:00</td><td>12:25:00</td><td>Non</td><td> RAS</td><td>43</td><td>Oui</td><td>3</td><td></td></tr>

....an the other row.....

</tr>

</table>
</div>

Ok, i found a possible explanation: tables having the same columns (exactly the same name), ci display it only in the first table, and not in the second one...
I tried the same SQL query in phpmyadmin, works perfectly=>i guess it might be something with CI (like settings or config file maybe ?!)
So i change the name of matching columns, and then they appear !!!

Any idea of what's going wrong ?
Thanks

Sorry, I thought I replied to this earlier, but I must have gotten pulled away while I was composing my response. You need to set aliases in your select statement if you want both columns displayed because PHP arrays require unique keys (and objects require unique property names), so the second instance of a column name will overwrite the value of the first instance.

If you want everything to be dynamic, you could generate the aliases as a combination of the table name and the column name (for example: "{$table_name}.{$column_name} as {$table_name}_{$column_name}"). You could just loop through the result of list_fields() adding each alias to an array, then pass the array to $this->db->select().

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide