Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

mysql display from two tables

Codes here !

Moderators: macek, egami, gesf

mysql display from two tables

Postby noasido » Tue Apr 23, 2013 1:49 am

I want a function to display values from two tables:
Table1: member_payments
ID memberID attendance
1 24 54
2. 24 45
28 24 49
21 26 35
29 25 53

Table2: member_children
ID memberID name week date
1 24 Monica 1 2013-04-09
2 24 Stephen 2 2013-04-09
3 24 Michael 1 2013-04-02
13 25 Stephen 4 2013-03-12
10 24 greaces 3 2013-03-17
14 26 Millicent 3 2013-03-12
15 26 Regina 1 2013-04-09
16 26 Joe 4 2013-04-09
19 24 myself 3 2013-04-28

I wrote
<?php //query for table 1
$selectQuery = "select * from member_payments where memberID = ".$_GET['mid'];

$resMemberPayments = mysql_query($selectQuery) or die('Error Loading GNCs: Contact Systems administrator!!!');
$totalMemberPayments = mysql_affected_rows();
$rowResMemberPayments= mysql_fetch_assoc($resMemberPayments);

//Function to display values from table 2 based on memberID and date
function fetch_teachers($manager_id,$date){
$data=array();
$selectQueryM = "SELECT member_children.memberID, member_children.name,member_children.date, member_payments.amount FROM member_children JOIN member_payments on member_children.memberID =member_payments.memberID WHERE member_children.memberID=" .$_GET['mid'];
$resMemberChild = mysql_query($selectQueryM) or die('Error Loading Members: Contact Systems administrator!!!');
$totalMemberChild = mysql_affected_rows();
$rowResMemberChild= mysql_fetch_assoc($resMemberChild);
$cnt = 1;
if($totalMemberChild>0){
do{
$data= $rowResMemberChild['name'].",";
$cnt++;
}while($rowResMemberChild= mysql_fetch_assoc($resMemberChild));
}else{
echo 'None <br />';
}
return $data;
}
// array
$tempArray = array(
$rowResMemberPayments['month'],$rowResMemberPayments['attendance'],
fetch_teachers( $rowResMemberChild['manager_id']),//fetch_teachers shold display values from table 2
getMonth( $rowResMemberPayments['date']),
$rowResMemberPayments['ID']
);
$contributions_[] = $tempArray;
?>

<div class="tbl_wrapper">
<?php if(count($contributions_)>0){?>
<table width="800" border="0" align="center" cellpadding="1" cellspacing="1">
<tr class="header">
<td align="center"> Month </td>
<td align="center"> Attendance </td>
<td align="center">Teacher(s) present </td>
<td align="center">Date of Attendance </td>
<td align="center">Edit</td>
</tr>
<?php $cnter=1; $lastEntry = 0;foreach($contributions_ as $val){?>
<?php $class = ($cnter%2==0) ? 'evenRow' : 'oddRow';?>

<tr <?php echo 'class="'.$class.'"';?>>
<?php if(is_array($val)){ $intcnt = 0;
$lastEntry = (count($val)-1);
foreach($val as $entry){?>

<td align="right"><?php if($intcnt!=$lastEntry) echo $entry;
else echo '<a href="?page=central&act=gncrec&mid='.$_GET['mid'].'&sub=edit&ed&pe='.$entry.'#ed_py">'.$edLinkImg.'</a>';?></td>

<?php $intcnt++;}
}?> </tr>
<?php $cnter++; }?>

</table>
<?php }else echo 'No GNC record made for this Category';?>
</div>
The fetch_teachers( $rowResMemberChild['manager_id']), only display one name in all the (Teacher(s) present) fields
any help please?


I want the function fetch_teachers to display the names based on memberid and date
and put those names in one table ($rowResMemberChild['manager_id']) such that it will be displayed: eg. Monica, stephen
noasido
New php-forum User
New php-forum User
 
Posts: 1
Joined: Mon Apr 22, 2013 9:28 am

Re: mysql display from two tables

Postby johnj » Tue Apr 23, 2013 6:02 am

after the 'where' condition in the query, do a 'ORDER BY' based on id and date.
johnj
php-forum Super User
php-forum Super User
 
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 7 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.