Infinite looping problem w/ mysql_fetch_array

[OhSqueezy]

I'm relatively new to using PHP and I know nothing about object-oriented programming... I'm working on a new site and in order to cut down the time spent typing similiar queries over and over, I decided to implement a function that builds a mysql query after providing an array of column headers and a table name. But it doesn't work. Here is the function:

function mysqlBuildQ( $table, $cols, $order='', $desc=0, $where ) {
$select = "SELECT";
 
// expand $cols array
for ( $i=0; $cols[$i] != ''; $i++ ) {
             if ( $i != 0 ) { $select = $select . ','; }
$select = $select . " $cols[$i]";
}
 
$select = $select . " FROM ";
$select = $select . $table;
if ( $where ) { $select = $select . " WHERE $where"; }
if ( $order ) { $select = $select . " ORDER BY $order"; }
if ( $desc ) { $select = $select . " DESC"; }
$query = mysql_query($select);
return mysql_fetch_array($query);
} 

$where and $order are strings that will have the where clause and order column stored respectively. I tried to equate a $row variable to this fuction and loop through it but it ends up running infinitely (though the information echoed is correct):

$news_cols = array('ID', 'Headline', "DATE_FORMAT(DateTime_Updated, '%m/%d %l:%i %p')");
while ( $news_row = mysqlBuildQ('News', $news_cols, 'DateTime_Updated', 1) ) {
             $news_id      = $news_row[0];
             $news_headline = $news_row[1];
             $news_date     = $news_row[2];
             echo("<option value=\"$news_id\">$news_headline | $news_date</option>");
} 

Is there something I can do to this code to acheive my goal or am I going about it the wrong way?

[brainpipe]

Each time the loop cycles, it's going to rebuild and re-run your query. You probably want something more like this (but I haven't tested this):

$results = mysqlBuildQ(...);
for ($i=0; $i<count($results); $i++) {
    $news_id = $results[$i][0];
    // more stuff here
} 

My advice is to play around with that until you understand what it's doing, and then take a look at mysql_fetch_row() for a more optimal solution.

[mjlivelyjr]

I would recommend what brainpipe said with a few differences.

First is to use two functions instead of one. The first one will be your mysqlBuildQ() function with slight modifications.

function mysqlBuildQ( $table, $cols, $order='', $desc=0, $where ) {
    $select = "SELECT";

    // expand $cols array
    for ( $i=0; $cols[$i] != ''; $i++ ) {
                 if ( $i != 0 ) { $select = $select . ','; }
    $select = $select . " $cols[$i]";
    }

    $select = $select . " FROM ";
    $select = $select . $table;
    if ( $where ) { $select = $select . " WHERE $where"; }
    if ( $order ) { $select = $select . " ORDER BY $order"; }
    if ( $desc ) { $select = $select . " DESC"; }
    $query = mysql_query($select);
    return $query;
} 

You'll notice the only thing changed is what was returned. I am returning the whole result set.

The second function we'll call mysqlReturnResult()

function mysqlReturnResult($resultSet) {
  return mysql_fetch_array($resultSet);
} 

Then you can change your loop code to the following.

$news_cols = array('ID', 'Headline', "DATE_FORMAT(DateTime_Updated, '%m/%d %l:%i %p')");
$queryResult = mysqlBuildQ('News', $news_cols, 'DateTime_Updated', 1);
while ( $news_row = mysqlReturnResult($queryResult)) {
             $news_id      = $news_row[0];
             $news_headline = $news_row[1];
             $news_date     = $news_row[2];
             echo("<option value=\"$news_id\">$news_headline | $news_date</option>");
} 

I thought I would post an article about data access objects that might interest you:
http://www.phppatterns.com/index.php...leview/25/1/1/
What you are doing is already a rudimentary version of what harry suggests in this article.

[OhSqueezy]

OK, here's what I ended up with:

function mysqlBuildArray( $table, $cols, $order='', $desc=0, $where ) {
  $query = "SELECT";
 
  // expand $cols array
  for ( $i=0; $cols[$i] != ''; $i++ ) {
    if ( $i != 0 ) { $query = $query . ','; }
    $query = $query . " $cols[$i]";
  }
 
  $query = $query . " FROM ";
  $query = $query . $table;
  if ( $where ) { $query = $query . " WHERE $where"; }
  if ( $order ) { $query = $query . " ORDER BY $order"; }
  if ( $desc ) { $query = $query . " DESC"; }
  $result = mysql_query($query);
 
  // build array to $array
  $num_fields = mysql_num_fields($result);
  for( $i=0; $row = mysql_fetch_array($result); $i++ ) {  
     for( $j=0; $j<$num_fields; $j++ ) {
        $array[$i][$j] = $row[$j];
     }
  }
  return $array;
} 

The function builds an array which contains the results of the query and it's working well. I'm still having trouble understanding how mysql_fetch_array() works, but I realized that it returns an array similar to this: ([0] -> 1, [1] -> 'News Headline', [2] -> '12/17 4:11 PM'). Therefore, I couldn't use count() to loop through the array because it returns the number of fields, not rows (or at least my function was doing this). Also, I realized I could divide count by mysql_num_fields(), but I would have to return that number as well, so I decided to build a new array within the function.

Anyway, thanks to you both. brainpipe, your solution pointed me in the right direction and this is only a temporary solution while I work my way through the article you suggested, mjlivelyjr.