Counting number of SELECTED rows in Oracle with PHP

Question

I'm doing this project for university, which is basically a movie database and for a couple of queries I need to know how many rows were selected. For now, there's 2 situations where I need this:

• Display a single movie information. I want the count of selected rows to know if the database contains the selected movie by the user. Or is there a better solution for this?
• That selected movie has genres, I need to know how many so that I can construct a string with the genres separated by | without adding one to the end of the string.

With MySQL this is easy, I just query the database and use mysql_num_rows() but oci_num_rows() doesn't work quite the same for the SELECT statement.

The only solution I found with OCI/PHP is this:

if(is_numeric($mid) && $mid > 0) {
        $stid = oci_parse($db,
            'SELECT COUNT(*) AS NUM_ROWS
            FROM movies
            WHERE mid = '.$mid
        );

        oci_define_by_name($stid, 'NUM_ROWS', $num_rows);
        oci_execute($stid);
        oci_fetch($stid);

        if($num_rows > 0) {
            $stid = oci_parse($db,
                'SELECT title, year, synopsis, poster_url
                FROM movies
                WHERE mid = '.$mid
            );

            oci_execute($stid);

            $info = oci_fetch_assoc($stid);

            $stid = oci_parse($db,
                'SELECT COUNT(*) AS NUM_ROWS
                FROM genres g, movies_genres mg
                WHERE mg.mid = '.$mid.' AND g.gid = mg.gid'
            );

            oci_define_by_name($stid, 'NUM_ROWS', $num_rows);
            oci_execute($stid);
            oci_fetch($stid);

            $stid = oci_parse($db,
                'SELECT g.name AS genre
                FROM genres g, movies_genres mg
                WHERE mg.mid = '.$mid.' AND g.gid = mg.gid');

            oci_execute($stid);

            $genres_list = null;

            while($row = oci_fetch_assoc($stid)) {
                $genres_list .= $row['GENRE'];

                if($num_rows > 1) {
                    $genres_list .= ' | ';
                    $num_rows--;
                }
            }

            $Template->assignReferences(array(
                'Index:LinkURI'    => $link_uri,
                'Movie:Title'      => $info['TITLE'],
                'Movie:Year'       => $info['YEAR'],
                'Movie:GenresList' => $genres_list,
                'Movie:Synopsis'   => $info['SYNOPSIS'],
                'Movie:PosterURL'  => $info['POSTER_URL'] // FIX: Handle empty poster link
            ));

            $Template->renderTemplate('movieinfo');
        } else {
            // TODO: How to handle this error?
        }
    } else {
        // TODO: How to handle this error?
    }

But I don't like it. I always need to make 2 queries to count the rows and then select the actual data and there's too many lines of code just to count the rows.

This code doesn't show it (haven't done it yet cause I'm looking for a better solution) but I'll also need to do the same for the movie directors/writers.

Is there any better and simpler solution to accomplish this or this is the only way?

I could add separators in the fetch loop until it finishes and then use PHP functions to trim the last separator from the string, but for this project I'm forced to use SEQUENCES, VIEWS, FUNCTIONS, PROCEDURES and TRIGGERS. Do any of these help solving my problem?

I know what SEQUENCES are, I'm using them already but I don't see how can they help.

For VIEWS, they probably wouldn't simplify the code that much (it's basically a stored query right?). For FUNCTIONS, PROCEDURES and TRIGGERS, as far as I understand them, I can't see how can they be of any help either.

Solutions?

Answer 1

Why do the initial count at all? Just issue your select for the movie title. If it's not found, do your error processing. If it is, continue on! If you really need the count, use an analytic to add the count to your query:

'SELECT title, year, synopsis, poster_url
      , COUNT(*) OVER (PARTITION BY mid) mcount
   FROM movies
  WHERE mid = '.$mid

The same goes for your genre selection.

EDIT:

Oracle documentation on Analytic Functions link. I found that analytics are a bit difficult to get from the Oracle docs. Analytic functions by Example by Shouvik Basu provides some simple guidance as to how to use them and helped me quite a bit.