migration issue MySql to Postgre

[dwlamb]

I am converting a PHP application from MySQL to PostgresSQL. The MySQL works flawlessly. The database for this application is a one to many relationship. I have a while loop nested inside another while loop

<?php while ($movie_row = pg_fetch_array($movie, PGSQL_ASSOC)) { ?>
 [html code to echo various elements of $movie_row]
 [a record in this part will have multiple records of information
    from another table in the db which the following code will 
    fetch and process]
<?php $movie_file_result = pg_query ($conn, "SELECT DISTINCT(cd_name)
            FROM movie_files 
            WHERE data_id=".$movie_row[id]."
            ORDER BY cd_name");?>
            <div class="files">
              <p>Find in:</p>
              <ul class="file_list">
<?php while ($filesrow = pg_fetch_array($movie_file_result, PGSQL_ASSOC)) { ?>
PROBLEM RESIDES HERE (see below)
                <li class="cd_name">
                  <?php echo $filesrow[cd_name]."\n" ?>
                </li>

<?php }?>
 [remainder of html code to iterate elements of $movie_row]
<?php }?>

For the nested while using $filesrow an infinite loop happens. Debugging in Netbeans sometimes the infinite loop happens only on the second while loop. Other times, I get a page full of the first layer while loop.

[cereal]

The pg_fetch_array() function takes 3 arguments, not 2, so change it to:

while ($filesrow = pg_fetch_array($movie_file_result, NULL, PGSQL_ASSOC))

Documentation:

• http://www.php.net/manual/en/function.pg-fetch-array.php

Bye!

[dwlamb]

Thanks for the help. I interpreted the second argument of the three as optional. From the documentation:

row
Row number in result to fetch. Rows are numbered from 0 upwards. If omitted or NULL, the next row is fetched.

In case anyone else reads this thread, don't omit the second argument.

[cereal]

True, in this case the documentation is not very clear. But the second argument can be omitted if you pass only the first one:

$row = pg_fetch_array($movie_file_result);

this is possibile because the default value of the third argument is PGSQL_BOTH:

array pg_fetch_array ( resource $result [, int $row [, int $result_type = PGSQL_BOTH ]] )

Which will return an associative and indexed array, in an style similar to this:

array(
    0 => array(
        0 => 'value',
        'title' => 'value',
    ),
    1 => array(
        1 => 'value 2',
        'title' => 'value 2',
    )
);