Help with function to call database rows

[rbarnett]

I created a function that returns all users from a database table, however, it only returns the first row.  What am I doing wrong?  When I run this independent of a function it displays all of the rows fine.  Thanks.

function getAll() {
  require_once('../../../moodle2_connect.php');
  $query = "select username from moodle2_user";
  $result = mysql_query($query) or die(mysql_error());
  while($row = mysql_fetch_array($result)){
  $items = $row['username'];
  return $items;
  }
}

[hoogie]

I believe that a function terminates as soon as you give it the "return" command.  So this function would terminate after returning the first result.

 

A different way of doing this that might solve your problem is to use the while loop to put all of the results into a list or an array, and then when the loop completes, return that list or array.

[rbarnett]

Thanks Hoogie.  I didn't know that about a return.  I'm probably not understanding you but to put it in a list I did the following:

function getAll() {
  require_once('../../../moodle2_connect.php');
  $query = "select username from moodle2_user";
  $result = mysql_query($query) or die(mysql_error());
  while($row = mysql_fetch_array($result)){
  $items = $row['username'];
  }
   return  $items;
}

Essentially putting the return statement outside the loop.  But this now gives me the last record.

[hoogie]

Yes, now you're getting the last result because every time you loop, you are assigning a different value to $items.  The first time through, it assigns the first value.  The second time, it overwrites the first value with the second value, so now all that $items contains is the second value.  Does that make sense?

 

So what you need to do is store the results in an array, which can store more than one variable at a time.

 

I haven't done this before, but try this:

 

function getAll() {
  require_once('../../../moodle2_connect.php');
  $query = "select username from moodle2_user";
  $result = mysql_query($query) or die(mysql_error());
  $items = mysql_fetch_array($result)
   return  $items;
}

 

Now to display your results you'll need to do this:

 

$items_array = getALL(X);

foreach ($items_array as $item) {
    echo $item;
}

 

As I said, I haven't tried this.

[rbarnett]

Thank you.  That does make a lot more sense.  Your example does not work but it gives me something to go on.  I'll keep working on it.  Thanks again.

[hoogie]

Maybe you figured this out already, but I'm home now and can actually test my solutions before posting them.

 

This has been working for me:

function getAll() {
  require_once('../../../moodle2_connect.php');
  $query = "select username from moodle2_user";
  $result = mysql_query($query) or die(mysql_error());
  $items = array();
  while ($row = mysql_fetch_array($result)) {
    $items[] = $row['username'];
  }  
  return  $items;
}

 

And to display:

foreach (getALL() as $item) {
    echo $item;
}

[rbarnett]

Yes.  Thank you.  I really appreciate it.