CODEWITHSUNDEEP
phpmysqli
Yousef Altaf
Yousef Altaf

Reputation: 2763

select from 2 tables and search for a value

I have two tables:

  1. bookingbook
  2. borrowbook

and I need to search the column bookId in each table if it's exist or not

My code

<?php
    while ($book = mysqli_fetch_assoc($results)) {
        $bokId = $book['id'];

        $checkBorrow = $db->prepare('SELECT
        a.bookId, b.bookId
        FROM
        bookingbook AS a
        INNER JOIN borrowbook AS b ON (a.bookId = b.bookId)
        WHERE a.bookId=? OR b.bookId=?
        ');
        $checkBorrow->bind_param('ii', $bokId, $bokId);
        $checkBorrow->execute();
        $res = $checkBorrow->get_result();
        $numRows = mysqli_num_rows($res);


        if ($numRows > 0) {
            $states = "Not Available";
        } elseif ($numRows <= 0) {
            $states = "Available";
        }
    }

?>

Result always comes Available even if it's not

bookingbook Table
id - insId - bookId - studentId
bookingbook Table
id - insId - bookId - studentId - borrowDate - restoreDate

Upvotes: 1

Views: 57

Answers (1)

Rajdeep Paul
Rajdeep Paul

Reputation: 16963

This is what you can do to get the number of rows returned,

while ($book = mysqli_fetch_assoc($results)) {
    $bokId = $book['id'];

    // Prepare query
    $checkBorrow = $db->prepare('SELECT * FROM bookingbook LEFT JOIN borrowbook ON 1=1 WHERE bookingbook.bookId=? OR borrowbook.bookId=? UNION SELECT * FROM bookingbook RIGHT JOIN borrowbook ON 1=1 WHERE bookingbook.bookId=? OR borrowbook.bookId=?');

    // Binds variables to prepared statement
    $checkBorrow->bind_param('iiii', $bokId, $bokId, $bokId, $bokId);

    // Execute query
    $checkBorrow->execute();

    // Store the result (to get properties)
    $checkBorrow->store_result();

    // Get the number of rows
    $numRows = $checkBorrow->num_rows;

    if ($numRows) {
        $states = "Not available";
    } else{
        $states = "Available";
    }

    // free results
    $checkBorrow->free_result();

    // close statement
    $checkBorrow->close();
}

Caution: You're mixing the procedural and object oriented style of mysqli. In the while loop condition you're using procedural style, whereas inside the while loop block you're using object oriented style of mysqli. Please pick one style and stick to it.

Edited:

Updated SQL query

// Prepare query
$checkBorrow = $db->prepare('
SELECT * FROM bookingbook LEFT JOIN borrowbook ON 1=1 WHERE bookingbook.bookId=? OR borrowbook.bookId=? 
UNION 
SELECT * FROM bookingbook RIGHT JOIN borrowbook ON 1=1 WHERE bookingbook.bookId=? OR borrowbook.bookId=?');

// Binds variables to prepared statement
$checkBorrow->bind_param('iiii', $bokId, $bokId, $bokId, $bokId);

Upvotes: 1

Related Questions