0

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.

Liton Kumar
  • 1
  • 3

1 Answers1

1

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);
$stmt->execute();
$result = $stmt->get_result();
$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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • it's not working. can you please check your answer and edit it with my question code? thanks. – Liton Kumar Jun 10 '22 at 02:51
  • Are you getting any errors? See https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-in-different-environments for how to enable error reporting for mysqli. – Barmar Jun 10 '22 at 02:58
  • i don't found a good solution in your link. i'm new in web development. so please try to help me that i will understand easily. – Liton Kumar Jun 10 '22 at 03:17
  • I don't understand. Don't you see `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` in the first answer? That enables reporting of mysqli errors. Add that to your script and tell me what error you're getting when you try to run my query. – Barmar Jun 10 '22 at 15:02
  • I'm sorry, I left out a step: `$result = $stmt->get_result();` Try the updated code. – Barmar Jun 10 '22 at 15:04