CODEWITHSUNDEEP
phpmysql
pk10
pk10

Reputation: 523

Unable to execute query in php & mysql

Given below is my code for a query. I am using PDO.

The search.php page:

<?php

    //Retrieving input values
    if(!empty($_POST)) {

        //Extracting input field values
        $book_id = $_POST['search_book_id'];
        $title = $_POST['search_title'];
        $author_name = $_POST['search_author_name'];

        //Calling function to execute the query
        $results_search_book = search_book($book_id, $title, $author_name);


        //Echoing out the query results

        if(count($results_search_book) > 0) {

          echo "<tbody>";


          foreach ($results_search_book as $row) {
            echo "$rowStart";

            echo "$row[TITLE]";

            echo "$rowEnd";
          }
          echo "</tbody>";
        } else {
            echo "<tfoot>";
            echo "<td colspan='4'>The query returned an empty set!</td>";
            echo "</tfoot>";
        }

    } else {
        echo "<tfoot>";
        echo "<td colspan='4'>No content to display, you haven't run a query yet!<br>Error custom message!</td>";
        echo "</tfoot>";
    }


  ?>

Here is the functions.php page which connects to the db and the query. I am trying to call the function search_book($book_id, $title, $author_name); and returning the result set to print them.

Here is the functions.php page: 

function search_book($x, $y, $z) {
    $conn = connect_db();

    $book_id = $x;
    $title = '%' . $y . '%';
    $author_name = '%' . $z . '%';

    echo $book_id;
    echo $title;
    echo $author_name;
    try {

        //Preapring the query
        $results = $conn->prepare("SELECT TITLE, A.BRANCH_NAME, A.BRANCH_ID, NO_OF_COPIES, COUNT(BOOK_LOANS.BOOK_ID) AS NUM_OUT, NO_OF_COPIES - COUNT(BOOK_LOANS.BOOK_ID) AS NUM_AVAIL, AUTHOR_NAME
                                    FROM
                                        (SELECT TITLE, BOOK.BOOK_ID, BOOK_COPIES.BRANCH_ID, BRANCH_NAME, NO_OF_COPIES, BOOK_AUTHORS.AUTHOR_NAME
                                        FROM BOOK,BOOK_COPIES,LIBRARY_BRANCH,BOOK_AUTHORS
                                        WHERE BOOK_COPIES.BOOK_ID=BOOK.BOOK_ID AND BOOK_AUTHORS.BOOK_ID = BOOK.BOOK_ID AND (BOOK.TITLE LIKE :title OR BOOK.BOOK_ID = :book_id OR BOOK_AUTHORS.AUTHOR_NAME LIKE :author_name)
                                        AND BOOK_COPIES.BRANCH_ID=LIBRARY_BRANCH.BRANCH_ID) AS A
                                        LEFT OUTER JOIN BOOK_LOANS ON A.BOOK_ID=BOOK_LOANS.BOOK_ID AND A.BRANCH_ID=BOOK_LOANS.BRANCH_ID
                                        GROUP BY A.BOOK_ID, A.BRANCH_ID");


        //Executing the query
        $results->bindParam(':book_id', $book_id, PDO::PARAM_STR);
        $results->bindParam(':title', $title, PDO::PARAM_STR);
        $results->bindParam(':author_name', $author_name, PDO::PARAM_STR);
        $results->execute();

        $data = $results->fetchAll();
        return $data;


    } catch(PDOException $e) {
        echo 'ERROR: Book Search query failed!';
    }
}

I am getting all the rows in return whereas I just need the qualified ones.

Upvotes: 0

Views: 1690

Answers (2)

linuxatico
linuxatico

Reputation: 1896

This solves the first problem, you missed the last and (maybe) most important part: the fetch 

$returnValues = array();
if ($stmt = $results->execute())
{
    while ($data = $stmt->fetch()) 
    {
      $returnValues[] = $data;
    }
}
return $returnValues;

The problem of the retrieving all results nor only what you are looking for, now that you posted more code, lies in the sql query, I think you should consider to restart from the begining and think better at the result you want to accompish: I don't think you need 2 subqueries, 3 natural join and 1 LEFT OUTER JOIN to complete your task.

Upvotes: 3

GolezTrol
GolezTrol

Reputation: 116100

Because you don't output anything. You just execute the query and don't even check for results.

Upvotes: 0

Related Questions