Queries - putting them in a class?

I'm trying to put my SQL queries in a separate file in order to not repeat them endlessly. I tried to create a class and functions for this, 开发者_高级运维but something is going wrong. I have trouble figuring why. I even wonder if this is the good way to proceed, since I did not find any similar example on the net.

The original code and result I want to obtain are those ones:

//this works but must be refactored 
$book = $_POST['book'];

$select_titles = mysql_query("
SELECT booktitles.id_title, booktitles.title, booktitles.id_book
FROM booktitles
WHERE booktitles.id_book = $book
") or die(mysql_error());

while($selected_titles = mysql_fetch_assoc($select_titles))
{
    $id_title = $selected_titles['id_title'];
    $title = $selected_titles['title'];
    echo $title."<br/>";

    // now the second query, wich needs the $id_title

    $select_content = mysql_query("
    SELECT content.id_content, content.content, content.id_title
    FROM content
    WHERE content.id_title = $id_title
    ") or die(mysql_error());

    while($selected_content = mysql_fetch_assoc($select_content))
    {
        $id_content = $selected_content['id_content'];
        $content =  $selected_content['content'];
        echo $content."<br/>;
    }
}

I tried to transform this in that way (note that I can't merge the two queries like I did in the previous example, since I must be able to access them separately):

// file : query.php

class Queries
{
    public function selectTitles($id_book)
    {
        $select_titles = mysql_query("
        SELECT booktitles.id_title, booktitles.title, booktitles.id_book
        FROM booktitles
        WHERE booktitles.id_book = $book
        ") or die(mysql_error());

        while($selected_titles = mysql_fetch_assoc($select_titles))
        {
            $id_title = $selected_titles['id_title'];
            $title = $selected_titles['title'];
            echo $title."<br/>";
        }
    }

    public function selectContent($id_title)
    {
        $select_content = mysql_query("
        SELECT content.id_content, content.content, content.id_title
        FROM content
        WHERE content.id_title = $id_title
        ") or die(mysql_error());

        while($selected_content = mysql_fetch_assoc($select_content))
        {
            $id_content = $selected_content['id_content'];
            $content =  $selected_content['content'];
            echo $content."<br/>;
        }
    }
}

// file : books.php

require_once('query.php');
$book = $_POST['book'];

$display_titles = new Queries;
$display_titles->selectTitles($book);

while($id_title)
{
    $display_content = new Queries;
    $display_content->selectContent($id_title);

    // this does not work. Furthermore, I must be able to add something here (like a form which uses the values) --> I seem not allowed to do that

}

This does not work. Any idea on how to handle this would be much appreciated!

(Note: here is an answer to a similar question: modify php oop mysql query )

I'm guessing that scoping is your issue here (just a quick glance). $selected_titles is in scope before you extract all of your query stuff into it's own class, but once it's been extracted, it's scope is limited to that class alone. Once the method call is complete, that variable is gone and cannot be accessed. A better method might be to use return values instead. Depending on the size of the data being returned and the matter in which it's being accessed, you may want to use pass-by-reference instead of pass-by-value.

typo ?

in

WHERE booktitles.id_book = $book

there should be

WHERE booktitles.id_book = $id_book

I think it would be better to join your two queries into one (I am assuming content.id_content is unique and the rows match up) for example

SELECT booktitles.id_title, booktitles.title, booktitles.id_book, content.id_content, content.content
FROM booktitles
WHERE (booktitles.id_book = $book) AND (booktitles.id_title = content.id_title)

This way you would avoid having a SQL statement in your while loop and have only one while loop.

I think having your SQL in a separate functions or in classes is a reasonable thing to do, it means you know where to look for your SQL if you need to change it, and if you have the same SQL statement used in multiple times locations I find it makes life easier to change it once rather than several times. Although I find if you do it this way it helps to use it consistently for your project.