如何在php mysql中获取结果的所有行？

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?

在我的表中我有2条记录,其中companyid = 1,但当我运行下面的php for companyid = 1时,它只返回第一个!我怎样才能获取所有记录?

The php file:

php文件:

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

使用mysql_fetch_array也是如此。它返回{“product”:[{“pid”:“12371”,“productname”:“test”}],“success”:1}当我运行不带参数的查询时使用mysql_fetch_array从表中选择*它返回所有行..

4 个解决方案

#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!

正如NikiC指出你不应该再使用mysql_函数,你可以在PDO和mysqli中获取整个数组。这是一个使用mysqli-> fetch_all函数获取所有行的示例,希望这有帮助!

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

#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_

我建议你使用PDO而不是mysql_

if (!empty($result)) {

Could be is_resource($result)

可能是is_resource($ result)

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

另外,您应该至少使用mysqli或PDO而不是mysql_ *函数。

#4

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

我坚信使用Doctrine进行批处理或使用MySQL(PDO或mysqli)进行任何迭代都只是一种错觉。

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

@ dimitri-k提供了一个很好的解释,特别是关于工作单元。问题是导致错过:“$ query-> iterate()”,它并没有真正迭代数据源。它只是一个\ Traversable包装器,已经完全获取了数据源。

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

一个例子表明即使从图片中完全删除Doctrine抽象层,我们仍然会遇到内存问题:

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:

这里,令人失望的getIterator()方法:

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

您可以使用我的小库实际使用PHP Doctrine或DQL或纯SQL来传输繁重的表。但是你找到了合适的:https://github.com/EnchanterIO/remote-collection-stream

#1