douguan3470
2014-03-20 08:37
浏览 314
已采纳

使用mysqli从数据库中获取所有结果

please check out my code below. With that class I am able to display results like so:

$connectTest    = new testResults();
$test       = $connectTest->grabResults(test, id, id);

echo $test['id'];
echo $test['name'];
echo $test['address'];

In my database I have several fields in the "test" table. I go to my page using index.php?id=1. With this I am displaying just the results from one row because it grabs all results WHERE id = 1.

What I need is the class below to display multiple results. It just displays one row. But if I have multiple rows with id = 1 I would like to display these results, but I cannot get it to work. I have tried a lot of things but I always end up with just one result.

class:

class testResults
{

    public function grabResults($table, $field, $id)
    {
        $result = $this->db->mysqli->query("SELECT * FROM $table WHERE $field = $id");

        $resultData[] = array();

        if(!$result)
        {
            return false;
        }

        while($row = $result->fetch_assoc())
        {
            $rows[] = $row;
        }

            foreach ($rows as $resultData)
            {
                return $resultData;
            }
    }
}

Edit:

Array ( [id] => 25 [name] => test [status] => 1 )
Array ( [id] => 25 [name] => test [status] => 3 )
Array ( [id] => 25 [name] => test [status] => 5 )
Array ( [id] => 25 [name] => test [status] => 4 )

Array ( [id] => 26 [name] => test [status] => 1 )
Array ( [id] => 26 [name] => test [status] => 3 )

Array ( [id] => 27 [name] => test [status] => 1 )
Array ( [id] => 27 [name] => test [status] => 3 )
Array ( [id] => 27 [name] => test [status] => 5 )
Array ( [id] => 27 [name] => test [status] => 4 )
Array ( [id] => 27 [name] => test [status] => 2 )
Array ( [id] => 27 [name] => test [status] => 4 )
Array ( [id] => 27 [name] => test [status] => 1 )

I am getting results as above, any way to easily display these results in an echo? For each id there are different results, so results will vary with each query. So I would like to display results in a table for example like so:

echo '<table>
<tr>
<td>$id</td>
<td>$name</td>
<td>$status</td>
</tr>
</table>';

So all results will be displayed like in a while loop.

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • duanchu0031 2014-03-20 08:39
    已采纳

    You can just return the array from function and then loop in your script

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

    The you can loop in your script

    $test = $connectTest->grabResults(test, id, id);
    foreach($test as $value)
    {
         print_r($value);
    }
    

    Upon OP edit

    If you need to print them separate you can access all elements with variable name and scopes with keys as follow

    $test = $connectTest->grabResults(test, id, id);
    echo '<table>';
    foreach($test as $value)
    {
         echo '<tr>
                 <td>'.$value['id'].'</td>
                 <td>'.$value['name'].'</td>
                 <td>'.$value['status'].'</td>
               </tr>';
    }           
    echo '</table>';
    
    打赏 评论
  • douchujian8124 2014-03-20 08:39

    It looks like you are returning a single row of your results with this bit of the function:

            foreach ($rows as $resultData)
            {
                return $resultData;
            }
    

    You should just return the whole thing instead.

        while($row = $result->fetch_assoc())
        {
            $rows[] = $row;
        }
        return $rows;
    
    打赏 评论
  • dongyu9263 2014-03-20 08:44

    return inside foreach() iteration means stop right after first iteration. Therefore you will be always getting only the first result.

    You'd better write this as:

       public function grabResults($table, $field, $id)
       {
            $result = $this->db->mysqli->query("SELECT * FROM $table WHERE $field = $id");
    
            $rows = array();
    
            while($row = $result->fetch_assoc()) {
                $rows[] = $row;
            }
    
            return $rows;
        }
    
    打赏 评论
  • dongzuan4917 2018-09-15 12:30

    Since you are passing the full resultset to another layer for processing, you can skip the loop to generate an array of associative arrays from the resultset.

    class testResults {
        public function grabResults($table, $field, $id) {
            // For the record, I feel a prepared statement is in order here...
            $result = $this->db->mysqli->query("SELECT * FROM $table WHERE $field = $id");    
            if (!$result) {
                return false;
            }
            return $result->fetch_all(MYSQLI_ASSOC);  // just in case you wanted to see the column names
        }
    }
    

    Then when you want to generate an html table from the returned array of associative arrays, use implode() as a flexible solution that doesn't care if you ever change the number of columns being passed in -- it will handle an indefinite number of columns.

    if ($resultset = grabResults("tests", "name", "test")) {
        echo "<table>";
            foreach ($resultset as $i => $row) {
                // if (!$i) { echo "<tr><th>" , implode("</th><th>", array_keys($row)) , "</th></tr>"; }
                echo "<tr><td>" , implode("</td><td>", $row) , "</td><tr>"
            }
        echo "</table>";
    }
    
    打赏 评论

相关推荐 更多相似问题