PHP: letting your own function work with a while loop

Question

Hi i have this code

$qVraagGroepenOp = "SELECT * FROM $tabele WHERE $where";
$rVraagGroepenOp = mysql_query ( $qVraagGroepenOp );
$aVraagGroepenOp = mysql_fetch_assoc ( $rVraagGroepenOp )

and i converted that to a fucntion

vraagOp("testtable","testtable_ID = $id");

function vraagOp($table,$where)
{
    $qVraagOp = "SELECT * FROM $table WHERE $where";
    $rVraagOp = mysql_query( $qVraagOp );
    $aVraagOp = mysql_fetch_assoc( $rVraagOp );

    return $aVraagOp;
}

only the problem is when i need more then one row i need to use a while loop

$qVraagGroepenOp = "SELECT * FROM testtable where testtype = test";
$rVraagGroepenOp = mysql_query ( $qVraagGroepenOp );
while ( $aVraagGroepenOp = mysql_fetch_assoc ( $rVraagGroepenOp ) ) 
{
     echo "testing <br>";
}

it wont work anymore is there a trick to make my function work with this while loop?

this wont work but i want to reach to something like it

while (vraagOp("testtable","testtype = test")) 
{
   echo "testing <br>";
}

is this possible thanks matthy

Answer 1

This

while (vraagOp("testtable","testtype = test")) 
{
   echo "testing <br>";
}

will work if you change VraagOp() to return false when no matching record was found.

You would have to move the mysql_query() part out of VraagOp(), and just leave the mysql_fetch_assoc part in.

However, I can't really see what benefit there would be? You would just be building a (unnecessary) wrapper around mysql_fetch_assoc(), wouldn't you?

Answer 2

You'll need to turn vraagOp() into an iterator and then use foreach() in order to make this work.

Answer 3

This could be done with static variables in the function scope.

function vraagOp($table,$where)
{
    static $rVraagOp;
    if(!$rVraagOp){
        $qVraagOp = "SELECT * FROM $table WHERE $where";
        $rVraagOp = mysql_query( $qVraagOp );
    }
    return mysql_fetch_assoc( $rVraagOp );
}

That should do what you're after.

Answer 4

Your example won’t work because the condition is executed on every iteration. That means vraagOp("testtable","testtype = test") would be called with each iteration until it returns a falsely value. mysql_fetch_assoc does that but your function doesn’t.

Answer 5

This function will 'cache' the mysql result resource from each unique $table/$where combination, and fetch the next result from it on each subsequent call. It will return an associative array for each row, or false when there are no rows left.

function vraagOp($table, $where)
{
    // Holds our mysql resources in a map of "{$table}_{$where}" => resource
    static $results = array();

    $key = $table . '_' . $where;

    if (!isset($results[$key]))
    {
      // first call of this particular table/where
      $results[$key] = mysql_query("SELECT * FROM $table WHERE $where");
    }

    $row = mysql_fetch_assoc($results[$key]);

    if ($row === false)
      // remove this key so a subsequent call will start over with a new query
      unset($results[$key]);

    return $row;
}

// Usage

while ($row = vraagOp("table1", "where field > 7")) {
   print_r($row);
}

Answer 6

This function returns an array of rows - it's a generic pattern yu can use almost anywhere you get multiple rows from a query.

function vraagOp($table,$where)
{
    $sql= "SELECT * FROM $table WHERE $where";
    $query= mysql_query($sql);
    if ($query != false)
    {
        $result = array(); 
        while ( $row = mysql_fetch_assoc($query) )
            $result[] = $row;
        return $result;
    }

    return $false;
}
//usage
$rows = vraagOp($table,$where);
if ($rows)
{
    foreach ($rows as $row)
        use($row);
}