I would solve this in a different way. Right now, you're having issues because you're database schema is not normalized. Instead, I would start off by altering the schema. Right now, you have this:
CREATE TABLE foo {
`autonum` INT(12) NOT NULL AUTO_INCREMENT,
`id` INT(12) NOT NULL,
`name` VARCHAR(255) NOT NULL,
`metaValue` VARCHAR(255) NOT NULL,
PRIMARY KEY(autonum)
)
Instead, I would split it out into two tables:
CREATE TABLE user {
`id` INT(12) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
PRIMARY KEY(id)
)
CREATE TABLE user_meta {
`user_id` INT(12) NOT NULL,
`value` VARCHAR(255) NOT NULL
)
Ignoring the fact that this looks like an EAV table (which it isn't, meta is just mis-named due to the OP's naming), it should be clear how this is easier. So let's look at your record, and build up the table:
#User
id | name
1 | Rose
2 | Gary
#User Meta
user_id | value
1 | drinker
1 | Nice Person
1 | Runner
2 | Player
2 | Funny
Now that we have the data, let's look at how we'd extract it. Let's assume that we always want to print out all users (limiting it would be simple, but for clarity, we can just do all for now). So, I would setup a simple class around each table here, caching the data into a single query.
class User implements IteratorAggregate {
protected $data = array();
public function __construct() {
$query = mysql_query("SELECT id, name FROM user");
while ($row = mysql_fetch_assoc($query)) {
$this->data[$row['id']] => $row['name'];
}
}
public function getIterator() {
return new ArrayObject($this->data);
}
}
class User_Meta {
protected $data = array();
public function __construct() {
$query = mysql_query("SELECT user_id, value FROM user_meta");
while ($row = mysql_fetch_assoc($query)) {
if (empty($this->data[$row['user_id']])) {
$this->data[$row['user_id']] = array();
}
$this->data[$row['user_id']][] = $row['value'];
}
}
public function getDataForUserId($id) {
return empty($this->data[$id]) ? array() : $this->data[$id];
}
}
Now, constructing the output becomes trivial:
$users = new User;
$meta = new User_Meta;
echo "<table>
<thead>
<th>Name</th>
<th>MetaValue1</th>
</thead>
<tbody>";
foreach ($users as $id => $name) {
echo "<tr>
<td>".htmlspecialchars($name)."</td>
<td>
<ul>";
foreach ($meta->getDataForUserId($id) as $metaValue) {
echo "<li>" . htmlspecialchars($metaValue) . "</li>";
}
echo " </ul>
</td>
</tr>";
}
echo "</tbody></table>";
Now, with all of that said, I would personally implement it a bit different, using first-class business objects and data mappers for everything.
And I did commit a few felonies here, but I did it for simplicity. Here's what I would have done differently:
- Not do logic in the constructor of an object. Instead, I would have injected it or used a factory to pre-populate the data.
- Abstract out the concept of the relationships into separate business objects.
- Separate out the pull logic from the display logic.
- Name things better (metavalue is not a good name for a field).
But it really depends on the exact requirements of the application. There is no one (or two) right ways to do this. Each of the possible ways has pros and cons. Learn to weigh the tradeoffs so that you can pick the right one for your requirements...
