How to output multiple rows from an SQL query using the mysqli object
- by Jonathan
Assuming that the mysqli object is already instantiatied (and connected) with the global variable $mysql, here is the code I am trying to work with.
class Listing {
private $mysql;
function getListingInfo($l_id = "", $category = "", $subcategory = "", $username = "", $status = "active") {
$condition = "`status` = '$status'";
if (!empty($l_id)) $condition .= "AND `L_ID` = '$l_id'";
if (!empty($category)) $condition .= "AND `category` = '$category'";
if (!empty($subcategory)) $condition .= "AND `subcategory` = '$subcategory'";
if (!empty($username)) $condition .= "AND `username` = '$username'";
$result = $this->mysql->query("SELECT * FROM listing WHERE $condition") or die('Error fetching values');
$this->listing = $result->fetch_array() or die('could not create object');
foreach ($this->listing as $key => $value) :
$info[$key] = stripslashes(html_entity_decode($value));
endforeach;
return $info;
}
}
there are several hundred listings in the db and when I call $result-fetch_array() it places in an array the first row in the db.
however when I try to call the object, I can't seem to access more than the first row.
for instance:
$listing_row = new Listing;
while ($listing = $listing_row-getListingInfo()) {
echo $listing[0];
}
this outputs an infinite loop of the same row in the db. Why does it not advance to the next row?
if I move the code:
$this->listing = $result->fetch_array() or die('could not create object');
foreach ($this->listing as $key => $value) :
$info[$key] = stripslashes(html_entity_decode($value));
endforeach;
if I move this outside the class, it works exactly as expected outputting a row at a time while looping through the while statement.
Is there a way to write this so that I can keep the fetch_array() call in the class and still loop through the records?
