how to fetch all the row of the result in php mysql?

Question

In my table I have 2 records with companyid = 1 , but when I run the php below for companyid = 1 it returns only the first one !
How can I fetch all the records?

The php file:

if (isset($_GET["companyid"])) {
$companyid = $_GET['companyid'];

// get a product from products table
$result = mysql_query("SELECT * FROM `products`         
                        WHERE companyid = $companyid;");

if (!empty($result)) {      

    if (mysql_num_rows($result) > 0) {

   while($row = mysql_fetch_assoc($result)){
      $product = array();
      $product["pid"] = $row["pid"];
      $product["productname"] = $row["productname"];        
    }

   $response["product"] = array();

       array_push($response["product"], $product);

        // success
       $response["success"] = 1;

   echo json_encode($response);

    } else {
        // no product found
        $response["success"] = 0;
        $response["message"] = "No product found";

        // echo no product JSON
        echo json_encode($response);
    }
} else {
    // no product found
    $response["success"] = 0;
    $response["message"] = "No product found";

    // echo no users JSON
    echo json_encode($response);
}
} else {
$response["success"] = 0;
$response["message"] = "Required field(s) is missing";

// echoing JSON response
echo json_encode($response);
}

Using mysql_fetch_array is happening the same. it returns {"product":[{"pid":"12371","productname":"test"}],"success":1} when i run a query without parameters select * from table using mysql_fetch_array it returns all the rows ..

Answer 1

As NikiC pointed out you should not be using the mysql_ functions any longer, you can fetch the entire array in both PDO and mysqli, Here is a example to fetch all rows using the mysqli->fetch_all function, hope this helps!

//Database Connection
$sqlConn =  new mysqli($hostname, $username, $password, $database);

//Build SQL String
$sqlString = "SELECT * FROM my_table";

//Execute the query and put data into a result
$result = $sqlConn->query($sqlString);

//Copy result into a associative array
$resultArray = $result->fetch_all(MYSQLI_ASSOC);

//Copy result into a numeric array
$resultArray = $result->fetch_all(MYSQLI_NUM);

//Copy result into both a associative and numeric array
$resultArray = $result->fetch_all(MYSQLI_BOTH);

Answer 2

while ($row = mysql_fetch_assoc($result)) {
    echo $row["userid"];
    echo $row["fullname"];
    echo $row["userstatus"];
}

mysql_free_result($result);

php.net/mysql_fetch_assoc

I would recommend you to use PDO instead of mysql_

if (!empty($result)) {

Could be is_resource($result)

Answer 3

You need to loop through the result to pull all the rows:

while($row = mysql_fetch_assoc($result)){
//Do stuff 
}

On a side note, you should be using at least mysqli or PDO instead of mysql_* functions.

Answer 4

I strongly believe the batch processing with Doctrine or any kind of iterations with MySQL (PDO or mysqli) are just an illusion.

@dimitri-k provided a nice explanation especially about unit of work. The problem is the miss leading: "$query->iterate()" which doesn't really iterate over the data source. It's just an \Traversable wrapper around already fully fetched data source.

An example demonstrating that even removing Doctrine abstraction layer completely from the picture, we will still run into memory issues:

echo 'Starting with memory usage: ' . memory_get_usage(true) / 1024 / 1024 . " MB \n";

$pdo  = new \PDO("mysql:dbname=DBNAME;host=HOST", "USER", "PW");
$stmt = $pdo->prepare('SELECT * FROM my_big_table LIMIT 100000');
$stmt->execute();

while ($rawCampaign = $stmt->fetch()) {
    // echo $rawCampaign['id'] . "\n";
}

echo 'Ending with memory usage: ' . memory_get_usage(true) / 1024 / 1024 . " MB \n";

Output:

Starting with memory usage: 6 MB 
Ending with memory usage: 109.46875 MB

Here, the disappointing getIterator() method:

namespace Doctrine\DBAL\Driver\Mysqli\MysqliStatement

/**
 * {@inheritdoc}
 */
public function getIterator()
{
    $data = $this->fetchAll();

    return new \ArrayIterator($data);
}

You can use my little library to actually stream heavy tables using PHP Doctrine or DQL or just pure SQL. However you find appropriate: https://github.com/EnchanterIO/remote-collection-stream