SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member OhSqueezy's Avatar
    Join Date
    Jul 2001
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Infinite looping problem w/ mysql_fetch_array

    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:

    PHP Code:
    function mysqlBuildQ$table$cols$order=''$desc=0$where ) {
    $select "SELECT";
     
    // expand $cols array
    for ( $i=0$cols[$i] != ''$i++ ) {
                 if ( 
    $i != ) { $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):

    PHP Code:
    $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?

  2. #2
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    Palo Alto
    Posts
    179
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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):
    PHP Code:
    $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.
    I think there is a world market for maybe five computers.
    - Thomas Watson, chairman of IBM, 1943.

  3. #3
    SitePoint Enthusiast mjlivelyjr's Avatar
    Join Date
    Dec 2003
    Location
    Post Falls, ID, US
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    function mysqlBuildQ$table$cols$order=''$desc=0$where ) {
        
    $select "SELECT";

        
    // expand $cols array
        
    for ( $i=0$cols[$i] != ''$i++ ) {
                     if ( 
    $i != ) { $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()
    PHP Code:
    function mysqlReturnResult($resultSet) {
      return 
    mysql_fetch_array($resultSet);

    Then you can change your loop code to the following.
    PHP Code:
    $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.

  4. #4
    SitePoint Member OhSqueezy's Avatar
    Join Date
    Jul 2001
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, here's what I ended up with:

    PHP Code:
    function mysqlBuildArray$table$cols$order=''$desc=0$where ) {
      
    $query "SELECT";
     
      
    // expand $cols array
      
    for ( $i=0$cols[$i] != ''$i++ ) {
        if ( 
    $i != ) { $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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •