Unable to execute query in php & mysql

Question

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.

Is the query successful and you're not getting any results or is there an error? — Scott Helme
– Scott Helme, Commented Nov 25, 2013 at 7:44
Simple commands work the above way, but this complex one doesn't. Is there any syntax problems? — pk10
– pk10, Commented Nov 25, 2013 at 7:46
What output would you expect? if the query was successful, then $results->execute() will simply return a statement instance. You're not assigning that to any var, so there's no way you can get to the data — Elias Van Ootegem
– Elias Van Ootegem, Commented Nov 25, 2013 at 7:50

linuxatico · Accepted Answer · 2013-11-26 08:51:41Z

3

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.

edited Nov 26, 2013 at 8:51

answered Nov 25, 2013 at 7:44

linuxatico

1,89630 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Elias Van Ootegem Over a year ago

$results is the statement instance, an execute call returns a bool, it should read $stmt = $conn->prepare(); if ($stmt->execute()){ $data = $stmt->fetchAll();}

linuxatico Over a year ago

Right, I saw $results->execute() and understood $results->executeQuery($query); That way is correct my answer

Elias Van Ootegem Over a year ago

PDO doesn't have an executeQuery method. besides, a prepare call returns an instance of PDOStatement, which only has an execute method, returning a bool. Your answer is wrong Please correct, or I'll have to down-vote. Check my answer for the correct usage of prepared statements...

linuxatico Over a year ago

I'm sorry but I can't find the answer you're talking about. Will fix my answer asap

GolezTrol · Accepted Answer · 2013-11-25 07:44:34Z

0

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

answered Nov 25, 2013 at 7:44

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

2 Comments

pk10 Over a year ago

I am checking them on another page :)

GolezTrol Over a year ago

Well, anyway you're ignoring the boolean result of $results->execute();

Collectives™ on Stack Overflow

Unable to execute query in php & mysql

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related