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.
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)
TABLE 2 (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>
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>
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>
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 :
Does union works on tables with different number of columns?
ReplyDeletenot work ?
ReplyDeleteHow can i edit this data????
ReplyDeleteHello , i have two table with two session point
ReplyDelete$_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......