My db table structures:
table1:- wp_eemail_newsletter_sub
id| eemail_name_sub |eemail_email_sub |eemail_mobile_sub |patient_date
------------------------------------------------------------------------
1 | user1 | user1@gmail.com | 9999999999 | 2014-5-14
---------------------------------------------------------
2 | user2 | user2@gmail.com | 754712456 |2014-8-11
table2:- wp_online_counsultation_query
id| online_consult_file | online_consult_filename | online_consult_datetime | consult_patientid
======================================================================================================
1 | filepath | filename1 | 2014-8-12 | 999999999
-------------------------------------------------
2 | filepath | filename2 | 2014-8-14 | 754712456
-------------------------------------------------
3 | filepath | filename3 | 2014-8-14 | 999999999
-------------------------------------------------
4 | filepath | filename4 | 2014-8-22 | 999999999
What is happening in db is , when a patient is registered his details will enter into table1 and the mobile number of patient is used as unique id to identify each patients. so after registered the admin can upload file reports for a patient from admin part and the file and filepath are stored in table2 with the patient mobile number. so one patient have more than one file reports. Now i need to display them as a table in site page, for that i used the format:
SerialNo: | Name | Email | Mobile | Test Date | Reports
========================================================
1 | user1 | user1@gmail.com | 9999999999 | 2014-5-14 | filename1 , filename3, filename4
----------------------------------------------------------------------------------------
2 | user2 | user2@ymail.com | 754712456 | 2014-8-11 | filename2
I tried the join query :
SELECT DISTINCT wp_eemail_newsletter_sub.eemail_name_sub,wp_eemail_newsletter_sub.eemail_email_sub,wp_eemail_newsletter_sub.eemail_mobile_sub,wp_eemail_newsletter_sub.patient_date,wp_online_consultation_querys.online_consult_file,wp_online_consultation_querys.online_consult_filename FROM wp_eemail_newsletter_sub LEFT JOIN wp_online_consultation_querys ON wp_eemail_newsletter_sub.eemail_mobile_sub=wp_online_consultation_querys.consult_patientid ORDER BY wp_online_consultation_querys.online_consult_datetime desc
This results me with this table :
user1 | user1@gmail.com | 999999999 | 2014-5-14 | filename1
user1 | user1@gmail.com | 999999999 | 2014-5-14 | filename3
user1 | user1@gmail.com | 999999999 | 2014-5-14 | filename4
user2 | user2@gmail.com | 754712456 | 2014-8-11 | filename1
can any one please tell me how can i display the query result data in php in the form of the desired table format described above.??
Edit:- using the answer by jimmy
The PHP code i used to display the result in site is :
$test = 'SELECT DISTINCT wp_eemail_newsletter_sub.eemail_name_sub, wp_eemail_newsletter_sub.eemail_email_sub, wp_eemail_newsletter_sub.eemail_mobile_sub, wp_eemail_newsletter_sub.patient_date,wp_online_consultation_querys.online_consult_file,GROUP_CONCAT( wp_online_consultation_querys.online_consult_filename) As s FROM wp_eemail_newsletter_sub LEFT JOIN wp_online_consultation_querys ON wp_eemail_newsletter_sub.eemail_mobile_sub= wp_online_consultation_querys.consult_patientid GROUP BY wp_eemail_newsletter_sub.eemail_mobile_sub ORDER BY wp_online_consultation_querys.online_consult_datetime desc';
$testdatea = $wpdb->get_results($test);
<table width="100%" class="widefatt" id="straymanage">
<thead>
<tr>
<th width="2%" align="left"></th>
<th width="3%" align="left">Sno</th>
<th width="5%" align="left">ID</th>
<th width="8%" align="left">Name</th>
<th width="8%" align="left">Email</th>
<th width="5%" align="left">Mobile</th>
<th width="10%" align="left">Test</th>
<th width="7%" align="left">Test Date</th>
<th width="7%" align="left">Latest Reports</th>
<th width="6%" align="left">Action</th>
</tr>
<thead>
<tbody>
<?php
if ( ! empty($testdatea) )
{
?>
<?php
$i = 1;
foreach ( $testdatea as $data ) {
?>
<tr class="<?php if ($i&1) { echo'alternate'; } else { echo ''; }?>">
<td align="left"><input class="case" name="chk_delete[]" id="chk_delete[]" type="checkbox" value="<?php echo(stripslashes($data->eemail_id_sub)).'_'.(stripslashes($data->eemail_email_sub)).'_'.(stripslashes($data->eemail_mobile_sub)); ?>" /> </td>
<td align="left"><?php if(($_GET['paginate']) != '') { echo ($limit * ($_GET['paginate']-1))+ $i; } else echo $i; ?></td>
<td align="left"><?php echo($data->eemail_patient_id); ?></td>
<td align="left"><?php echo(stripslashes($data->eemail_name_sub)); ?></td>
<td align="left"><?php echo(stripslashes($data->eemail_email_sub)); ?></td>
<td align="left"><?php echo(stripslashes($data->eemail_mobile_sub)); ?></td>
<td align="left"><?php echo(stripslashes($data->diagnosis_name)); ?></td>
<td align="left"><?php echo(date("d M ,Y", strtotime($data->patient_date))); ?></td>
<td align="left"><a href="javascript:void(0);" onclick="showdiv('<?php echo $data->online_consult_file;?>')"><?php echo(stripslashes($data->s));?></td>
<td align="left">
<a title="Delete" onClick="javascript:_subscriberdealdeletebylabs('<?php echo($data->eemail_patient_id); ?>')" href="javascript:void(0);">Delete</a> </td>
</tr>
<?php $i = $i+1; } ?>
</tbody>
<?php
}
else
{
?>
<tr><td colspan="5">No data found</td></tr>
<?php
} ?>
</table>
The Query gives me the table in correct format with files in ” filename1,filename3,filename4″ for one user. but i need them to be like
<a href="filepath">filename1</a>,<a href="filepath">filename3</a>
please help me to solve this issue?
For better coding practices I would suggest one should follow this:-
1) include GROUP_CONCAT(online_consult_file) AS FILEPATH in SELECT
2) now you would get data like filepath,filepath,filepath in FILEPATH and you had filename1,filename3,filename4 in FILENAME
3) Now you could explode both in php separated by comma so you can get two arrays $FILEPATH and $FILENAME
4) Now you could use for loop :-
You need to do GROUP BY eemail_mobile_sub
and GROUP_CONCAT(online_consult_filename)