limit on mysqli to php data

Question

my problem is that i have a large db (10000+ rows) and want to get them all at once with php and mysqli. but when i print all the results with var_dump i get only the firt row. it is like that is the only thing that has come over. so my question is: is there a limit to a php mysql connection? or is it a error in my code.

this is my code:

$link = mysqli_connect("localhost","root","","ows_index2");
$info_wu = mysqli_query($link,"SELECT `hostname`, `page` from `pages`");
$row = mysqli_fetch_assoc($info_wu);
var_dump $row;

(with php brackets and the log in is good because i get the first row)

Answer 1

mysqli_fetch_assoc does only fetch one row at once. And there ain't something that fetches all rows with mysqli AFAIK (not a full mysqli pro). (Edit: There is)

I suggest you turn this into an iterator and then use that iterator like an array:

/**
 * Iterator that fetches each iteration value from a
 * function until it is not string and equals false.
 */
class FetchIterator extends NoRewindIterator
{
    /**
     * @var string
     */
    private $fetchCallback;
    /**
     * number of the current iteration
     * @var int
     */
    private $virtual;
    /**
     * cache of the current value
     * @var mixed
     */
    private $current;

    /**
     * @param string $fetchCallback
     */
    public function __construct($fetchCallback)
    {
        $this->fetchCallback = $fetchCallback;
        $this->virtual = 0;
    }

    /**
     * Return the current element
     * @link http://php.net/manual/en/iterator.current.php
     * @return mixed Can return any type.
     */
    public function current()
    {
        $this->virtual || $this->next();
        return $this->current;
    }

    /**
     * Return the key of the current element
     * @link http://php.net/manual/en/iterator.key.php
     * @return scalar scalar on success, integer
     * 0 on failure.
     */
    public function key()
    {
        $this->virtual || $this->next();
        return $this->virtual - 1;
    }

    /**
     * Checks if current position is valid
     * @link http://php.net/manual/en/iterator.valid.php
     * @return boolean The return value will be casted to boolean and then evaluated.
     * Returns true on success or false on failure.
     */
    public function valid()
    {
        $this->virtual || $this->next();
        return $this->validate();
    }

    /**
     * @return bool
     */
    private function validate()
    {
        return FALSE != $this->current || is_string($this->current);
    }

    /**
     * Move forward to next element
     * @link http://php.net/manual/en/iterator.next.php
     * @return void Any returned value is ignored.
     */
    public function next()
    {
        if ($this->virtual && ! $this->validate()) {
            return;
        }
        $this->fetch();
        $this->virtual++;
    }

    /**
     * fetch value from callback. can be called
     * after assigning a new callback while
     * in iteration.
     */
    public function fetch()
    {
        $func = $this->fetchCallback;
        $this->current = $func();
    }

    /**
     * number of times the fetch callback function
     * has been called so far.
     *
     * @return int
     */
    public function getCallCount()
    {
        return $this->virtual;
    }

    /**
     * @return callback
     */
    public function getFetchCallback()
    {
        return $this->fetchCallback;
    }

    /**
     * Set callback for subsequent iterations.
     *
     * @param callback $fetchCallback
     * @return FetchIterator
     */
    public function setFetchCallback($fetchCallback)
    {
        $this->fetchCallback = $fetchCallback;
        return $this;
    }
}

Usage:

$info_wu = mysqli_query($link,"SELECT `hostname`, `page` from `pages`");
$fetchFunction = function() use ($info_wu) {
    return mysqli_fetch_assoc($info_wu);
}
$it = new FetchIterator($fetchFunction);
$rows = iterator_to_array($it);

The variable $rows now is an array containing one row per each element. Instead of iterator_to_array you can use as well foreach and process each row on it's own.

The iterator code might look a bit too much for your case only, it's a more general one that can be used for many cases with database result operations. A related blog post is: Some PHP Iterator Fun that shows how to iterate over the same iterator multiple times.

Answer 2

If you have PHP 5.3+, you can use the new mysqli_fetch_all() instead of mysqli_fetch_assoc().

$rows = mysqli_fetch_all($info_wu, MYSQLI_ASSOC);

var_dump($rows);

Answer 3

It is an error in your code.

This:

$row = mysqli_fetch_assoc($info_wu);
var_dump($row);

Should be:

while($row = mysqli_fetch_assoc($info_wu))
    var_dump($row);