Monday, June 25, 2012

Joint two tables in MySQL

We have table edu_payments:
CREATE TABLE  `cnsh_lms`.`edu_payments` (
  `payment_id` smallint(5) unsigned NOT NULL auto_increment,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `approved` tinyint(4) NOT NULL default '0',
  `transaction_id` char(100) NOT NULL,
  `member_id` mediumint(8) unsigned NOT NULL,
  `course_id` mediumint(8) unsigned NOT NULL,
  `amount` decimal(7,2) NOT NULL default '0.00',
  PRIMARY KEY  (`payment_id`)
We have another table edu_courses
CREATE TABLE  `cnsh_lms`.`edu_courses` (
  `course_id` mediumint(8) unsigned NOT NULL auto_increment,
  `member_id` mediumint(8) unsigned NOT NULL default '0',
  `title` varchar(255) NOT NULL,
  PRIMARY KEY  (`course_id`)

They have same course_id. Final display is  title in the second table and timestamp, approved and amount in the first table. I use LEFT JOIN in this example.
    $sql = 'SELECT p.course_id, p.approved, p.amount, p.timestamp, c.title  FROM edu_payments p LEFT JOIN edu_courses c  ON  p.course_id =c.course_id WHERE p.member_id ='.$_SESSION['member_id'].' AND  p.course_id = '.$module_id;
 $result = mysql_query($sql, $db);
 $page_content .= '   
     <br /><br />
      <b>My  payments:</b>
        <table class="data static" rules="rows" summary="">
            <th scope="col">Module Title</th>
            <th scope="col">Module Fee </th>
            <th scope="col">Approved </th>
            <th scope="col">Date</th>
 while($row = mysql_fetch_assoc($result)){
        $page_content  .= '<tr>';      
        $page_content  .= '<td>'.substr($row['title'],0,8).'</td><td>$'.$row['amount'].'</td>';
        $page_content  .= '<td>'.$row['approved'].'</td>';
         $page_content  .= '<td>'.$row['timestamp'].'</td>';
         $page_content  .= '</tr>';
      $page_content  .= '</table>';
      echo  $page_content ;

No comments:

Post a Comment