mysqli query only returning one result but multiple rows in num_rows

[simplypixie]

I am not new to OOP I am tidying up a very large site of mine and trying to create a much better structure (more in keeping with the whole point of OOP). I have decided to use mysqli (please don't suggest PDO as I have decided againast it) which is new to me and am a little confused to say the least with a problem I am having with a basic select * query.

In my database class I have the following functions (only the ones relevant to this thread):

public function selectAll($table, $fields = '*', $where = NULL, $order = NULL, $limit = NULL, $offset = NULL)
  {
    $sql = 'SELECT '.$fields.' FROM '.$table;

    if ($where != NULL) {
        $sql .= ' WHERE '.$where;
    }
    if ($order != NULL) {
        $sql .= ' ORDER BY '.$order;
    }
    if ($limit != NULL) {
        $sql .= ' LIMIT '.$limit;
    }
    if ($offset != NULL) {
        $sql .= ' OFFSET '.$offset;
    }

    return $this->doQuery($sql);
  }

  public function doQuery($sql)
  {     
    $query = $this->dbh->query($sql);
    $last_query = $sql;

    try {
        if (!$query) {

            throw new CustomException("Query failed: ".$last_query);

        } else {

            $array['result'] = $query->fetch_assoc();
            $array['total_rows'] = $query->num_rows;
            return $array;

        }
    } catch (CustomException $e) {

        echo $e->getErrorReport();

    }
  }

In my child class, I have the following:

public function listShows($status = 'All') {

        switch ($status) {
            case 'All':
                /* Get all shows in database */
                $where = "";
                $order = "name ASC";
                break;
            // All other cases removed to keep things short
        }

        return $this->selectAll($this->table, '*', $where, $order);

    }

And in my index.php test file I have:

$s = new Shows;
print_r($s->listShows());

// The print_r is just for my testing purposes and I have tried it within the classes - all the same results

When I open index.php I get an array but it is only an array of all the details for the first row returned by the query and yet it prints out a total of 33 rows from the num_rows (which is the correct number of rows in the table).

If I try and run the results from fetch_assoc through a while loop, I just get a looping list of the first record (i.e. no other records from the table and a never ending list of the same details).

I have echo'd the SQL and it is fine and rund correctly in the DB and I am getting no other errors

What am I missing?

[DarkMonarch]

if i remember correctly mysqli return an object of arrays.

so you need to run through arrays, then run again in the array.

while($row = $query->fetch_assoc()){        
        foreach($row as $key => $value){ ... do something

[DarkMonarch]

do a print_r($row) you'll see all the arrays it pops up.

[DarkMonarch]

wow my mind is fragmented today :( sorry about that.

you started your post about query
you need to put your query in ()
like so:

$db = new mysqli($host, $user, $pass, $dbname) or die($error);// declare db

$query = $db->query("SELECT * FROM sometable") or die($error);//use to query

[simplypixie]

My query is in () $query = $this->dbh->query($sql);
However, I have just printed the $rows within the while loop and I have all my records showing now, thank you.

Now to work out how to get the data to the index.php page and display it :-)

[simplypixie]

Actually, I have just got a basic while loop to work with the foreach from the database class - I wonder why it won't work elseshere.

[DarkMonarch]

return statement will break the function therefor the loop, so it probly return 1 array if place inside your doQuery function.

unless you concatenate the data before returning.

here what i did with a dynamic employee page: (its in french but doesnt matter)

public function tableau(){
        $db = new mysqli($this->host, $this->user, $this->pass, $this->data) or die($error);

        $query = $db->query("SELECT * FROM employes") or die($error);
        if($query->num_rows > 0){
            while($row = $query->fetch_assoc()){
                foreach($row as $key => $value)
                    $this->text .= $value.";";

                $this->text .= "|";
            }
        }

        $query->free();
        $db->close();

        return $this->text;
    }//End tableau

your calling function strip the data with the delimters ; and |

hope it helps

[simplypixie]

It's OK, I am using a basic while loop (as I say I have got that working now where it wouldn't before and therefore don't need a foreach within it - did wonder why I would in the first place really) and allocating all rows to a new array and returning that. It is all working now.

If anyone else has this problem, this is what I changed/got working:

public function doQuery($sql)
  {     
    $query = $this->dbh->query($sql);
    $last_query = $sql;

    try {

        if (!$query) {

            throw new CustomException("Query failed: ".$last_query);

        } else {

            $rows = array();
        while($row = $query->fetch_assoc())
        {
            $rows[] = $row;
        }
                return $rows;

        }
    } catch (CustomException $e) {

        echo $e->getErrorReport();

    }
  }

And run the foreach in the page where the data is to be displayed (in brief):

$s = new Shows;
$shows = $s->listShows();

foreach ($shows as $show) {
echo $show['name'].'<br>';
}