Fetch two different table data in single page using PHP and MySql | 2my4edge

17 December 2014

Fetch two different table data in single page using PHP and MySql

Hi, Today i'm going to tell you one of the useful tip to fetch data from database, fetching database data is simple one as said in last post, and here i'm going to fetch two different table data in single page using UNION operator, UNION operator is for combines the result of two or more SELECT statements. 

fetch-two-different-table-data-in-single-page
DOWNLOAD                              LIVE DEMO

SQL UNION SYNTAX
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.

SQL UNION ALL SYNTAX
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Note: The column names in the result-set of a UNION are usually equal to the column names in the first SELECT statement in the UNION.

TABLE SAMPLES

TABLE 1 (volunteer table)
volunteer table

TABLE 2 (donor table)
donor table
Insert some data in the two tables, if you want know insert coding click here for insert fetch delete update basic.

DB FILE
<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_DATABASE', '2my4edge');
$connection = mysql_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD) or die(mysql_error());
$database = mysql_select_db(DB_DATABASE) or die(mysql_error());
?>

FETCH VOLUNTEER DATA
<div class="left_content">
<ul class="ulclass">
<h4 align="center"> Voluteer details </h4>
<?php

        $selectvolunteer=mysql_query("SELECT * FROM volunteer ORDER BY vid DESC");
        $i=1;
        while($volunteerrow=mysql_fetch_array($selectvolunteer))
        
        {
          $vid=$volunteerrow['vid'];
          $vname=$volunteerrow['v_username'];
          $vemail=$volunteerrow['v_email'];
          $vtime=$volunteerrow['created'];
                      ?>
<li class="liclass">
  <p> Volunteer Name : <strong>
    <?php echo $vname; ?>
    </strong>
  </p>
  <p> Volunteer Email : <strong>
    <?php echo $vemail; ?>
    </strong>
  </p>
  <p> Created on : <strong>
    <?php echo $vtime; ?>
    </strong>
  </p>
</li>
<?php } ?>
</ul>
</div>

volunteer results
the fetch data will be like the above image.

FETCH DONOR DATA
<div class="middle_content">
<ul class="ulclass">
<h4 align="center"> Donor details </h4>
<?php
      $selectdonor=mysql_query("SELECT * FROM donor ORDER BY did DESC");
      while($donorrow=mysql_fetch_array($selectdonor))
      
      {
        $did=$donorrow['did'];
        $dname=$donorrow['d_username'];
        $demail=$donorrow['d_email'];
        $dtime=$donorrow['created'];
                    ?>
<li class="liclass">
  <p> Donor Name : <strong>
    <?php echo $dname; ?>
    </strong>
  </p>
  <p> Donor Email : <strong>
    <?php echo $demail; ?>
    </strong>
  </p>
  <p> Created on : <strong>
    <?php echo $dtime; ?>
    </strong>
  </p>
</li>
<?php } ?>
</ul>
</div>
donor results
the donor table data is fetched, and combine of two table data.

UNION for FETCH FROM TWO TABLES
<div class="right_content">
<ul class="ulclass">
<h4 align="center"> Over all Users </h4>
  <?php
        $selectuser=mysql_query("SELECT * FROM Volunteer UNION 
                    SELECT * FROM donor ORDER BY created DESC ");
        while($userrow=mysql_fetch_array($selectuser))
        
        {
          $uid=$userrow['vid'];
          $uname=$userrow['v_username'];
          $uemail=$userrow['v_email'];
          $utime=$userrow['created'];
                      ?>
  <li class="liclass">
    <p> User Name : <strong>
      <?php echo $uname; ?>
      </strong>
    </p>
    <p> User Email : <strong>
      <?php echo $uemail; ?>
      </strong>
    </p>
    <p> Created on : <strong>
      <?php echo $utime; ?>
      </strong>
    </p>
  </li>
  <?php } ?>
</ul>
</div>
overall results
the result will be like the above, the combines of the two different table from database using php and mysql. i hope this post is really helpful to you..


RELATED POST :








4 comments:

  1. Does union works on tables with different number of columns?

    ReplyDelete
  2. How can i edit this data????

    ReplyDelete
  3. Hello , i have two table with two session point
    $_SESSION["ins_id"]=$row['user_id' ];
    $_SESSION["insert_id"]=$row['years_id'];

    1.)
    if(isset($_SESSION['ins_id']))
    {

    $_POST['id']= $_SESSION['ins_id'];
    if(isset($_POST['id']) && !empty($_POST['id']))
    {

    $query = $db->query("SELECT * FROM subcatg WHERE user_id = ".$_POST['id']."");

    2.)if(isset($_SESSION['insert_id']))
    {

    $_POST["years_id"]= $_SESSION['insert_id'];
    if(isset($_POST["years_id"]) && !empty($_POST["years_id"])){

    $query = $db->query("SELECT * FROM years WHERE years_id = ".$_POST['years_id']." ");


    how to combine pls help me......

    ReplyDelete

^