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 ..

• Marvo: You need to loop through the results and read in each row. Change your if (mysql_num_rows...) call to a for loop.
• Hans Z: don't do $result = mysql_fetch_assoc($result); you're destroying your $result data when you do that. rename it $row or something
• nickb: possible duplicate of Fetch all rows based on the query into an array
• orourkek: Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information.
• NikiC: Also, if you're using PDO you can just call fetchAll and be done with it :)

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);

• amurrell: fetch_all didn't initially work on ubuntu for me - I needed to do: sudo apt-get install php5-mysqlnd and restart apache
• Kaspar Lee: $result = $this->sqlConn->query($sqlString); did not work for me, I had to use $result = $sqlConn->query($sqlString);
• Fouad: @amurrell I had to install php5-mysqlnd as well, thank you!

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.

• mprog: I have edited the php code above.. using the loop I still get the same result

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