get multiple table with same columns data-CodePudding

i have three table with same value of columns. example: say i have three table "table1", "table2", "table3" and each columns has "id", "title", "description", "category", "date", "thumbnail", "admin". now i'm trying to get all data from those three table. but there is a think, i want to check with if statement. if table1 not match with id, check table2. if table2 not match with id, check table3 and at last show the data. please check my below code, i'm trying to get data from those three table:

<?php
            include('config/database.php');
            $id=$_GET['single'];
            $query=mysqli_query($conn,"select * from table1, table2, table3 where id='$id'  ");
            while($row=mysqli_fetch_array($query)){
            $title=$row['title'];
            $date=$row['date'];
            $admin=$row['admin'];
            $thumbnail=$row['thumbnail'];
            $description=$row['description'];
            $category=$row['category'];
         }
          ?>

please help me to get all data from those three table with if statement it will be better to understand if you post an answer. thank you in advance.

CodePudding user response：

Use a UNION of 3 queries.

$sql = "
    SELECT * FROM (
        SELECT 1 AS tnum, * FROM table1 WHERE id = ?
        UNION ALL
        SELECT 2 AS tnum, * FROM table2 WHERE id = ?
        UNION ALL
        SELECT 3 AS tnum, * FROM table3 WHERE id = ?
    ) AS x
    ORDER BY tnum
    LIMIT 1";
$stmt = $conn->prepare($sql);
$stmt->bind_param('iii', $id, $id, $id);
$result = $stmt->execute();
$row = $result->fetch_assoc();
if ($row) {
    $title = $row['title']
    $date=$row['date'];
    $admin=$row['admin'];
    $thumbnail=$row['thumbnail'];
    $description=$row['description'];
    $category=$row['category'];
}

Adding the tnum column to the result orders them so the table1 data is preferred, then table2, finally table3.

CodePudding user response：

<?php

include "config/database.php";
$id = $_GET["single"];

$query1 = "SELECT * from table1 where id='$id'";
$res1 = $db->query($query1);
foreach ($res1 as $row) {
  $id2 = $row["id"];
  if ($id == $id2) {
    $title = $row["title"];
    $date = $row["date"];
    $admin = $row["admin"];
    $thumbnail = $row["thumbnail"];
    $description = $row["description"];
    $category = $row["category"];
    // Then echo data
  } elseif ($id != $id2) {
    $query2 = "SELECT * from table1 where id='$id'";
    $res2 = $db->query($query2);
    foreach ($res2 as $row2) {
        $id3 = $row["id"];
        if ($id == $id3) {
            $title = $row["title"];
            $date = $row["date"];
            $admin = $row["admin"];
            $thumbnail = $row["thumbnail"];
            $description = $row["description"];
            $category = $row["category"];
            // then echo data
        } elseif ($id != $id3) {
            $query3 = "SELECT * from table1 where id='$id'";
            $res3 = $db->query($query2);
            foreach ($res3 as $row3) {
                $id3 = $row3["id"];
                if ($id == $id3) {
                    $title = $row["title"];
                    $date = $row["date"];
                    $admin = $row["admin"];
                    $thumbnail = $row["thumbnail"];
                    $description = $row["description"];
                    $category = $row["category"];
                    // then echo data
                }
            }
        }
    }
}

} ?>