mysqli query only returning first row

Question

I am migrating from mysql to mysqli, and I am having trouble returning more than one row from the database in a query.

$db = new mysqli($hostname, $sql_us, $sql_us_pwd, $sql_db); // This is already connected

function db_query($db, $query, $type = 'object') {

    global $db;

    $result = $db->query($query);

    if ($type == 'assoc') {
        while($row = $result->fetch_assoc()) {
          return $row;
        }
    } else {    
        while($row = $result->fetch_object()) {
          return $row;
        }   
    }

    mysqli_free_result($result);

}



$query = "SELECT * FROM `users`";
$user = db_query($db, $query);
print_r($user); // This is only returning the first row of results

I'm obviously trying to make a function where I can query the database and either return the results in an associative array or as an object. What am I doing wrong?

Such a function have to support prepared statements or there is no use for it at all. — Your Common Sense
– Your Common Sense, Commented Jul 29, 2013 at 12:51

Code Lღver · Accepted Answer · 2013-07-29 12:46:09Z

2

Use this code:

$rows = array();
if ($type == 'assoc') {
    while($row = $result->fetch_assoc()) {
      $rows[] = $row;
    }
} else {    
    while($row = $result->fetch_object()) {
      $rows[] = $row;
    }   
}
return $rows;

You are using the return inside the while and return terminates the while loop after first iteration that's why you are getting only one row.

answered Jul 29, 2013 at 12:46

Code Lღver

15.6k16 gold badges61 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Padmanathan J · Accepted Answer · 2013-07-29 12:47:13Z

2

You need to store while loop values into array try this

$rows = array();
if ($type == 'assoc') {
    while($row = $result->fetch_assoc()) {
      $rows[] = $row;
    }
} else {    
    while($row = $result->fetch_object()) {
      $rows[] = $row;
    }   
}
return $rows;

answered Jul 29, 2013 at 12:47

Padmanathan J

4,6305 gold badges39 silver badges76 bronze badges

Comments

Prathik Rajendran M · Accepted Answer · 2013-07-29 12:47:15Z

2

When you return in a function it stops executing at that point and goes back to the where it was called from with the value that you are returning.

In your case when you do a return $row, you are getting out of the function as soon as the first row is read.

Fix is:

$result = array();
if ($type == 'assoc') {
    while($row = $result->fetch_assoc()) {
      $result[] = $row;
    }

} else {    

    while($row = $result->fetch_object()) {
      $result[] = $row;
    }   
}
return $row;

answered Jul 29, 2013 at 12:47

Prathik Rajendran M

1,1628 silver badges21 bronze badges

Comments

Daniel A. White · Accepted Answer · 2013-07-29 12:43:42Z

1

You are only returning the first row. You have to return an array.

answered Jul 29, 2013 at 12:43

Daniel A. White

192k49 gold badges389 silver badges473 bronze badges

Comments

George Kagan · Accepted Answer · 2013-07-29 12:47:06Z

1

$arr = array();
if ($type == 'assoc') {
  while($row = $result->fetch_assoc()) {
    $arr[] = $row;
  }
}
else {    
  while($row = $result->fetch_object()) {
    $arr[] = $row;   
  }
}
return $arr;

answered Jul 29, 2013 at 12:47

George Kagan

6,1448 gold badges50 silver badges50 bronze badges

Collectives™ on Stack Overflow

mysqli query only returning first row

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related