Reputation: 9855
In my table 'user_sightings' I allow multiple entries for each user, so it looks as follows...
------------------------
ID | user_ID | postcode
1 39 ab12cd
2 39 sk91ab
3 39 ab44ix
After running my query I try to print out each postcode with the following...
echo $row["postcode"];
only this only prints out the first instance for that user, Is their a way I can get all instances for the user and dynamically set them as variables?
Query:
$sth = $conn->prepare("SELECT * FROM directory, user_sightings WHERE directory.user_active != '' AND directory.ID = :uid AND user_sightings.user_ID = :uid");
$sth->execute(array(':uid' => $UID));
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
echo $row["postcode"];
}
Upvotes: 0
Views: 95
Reputation: 34063
You need a while
loop to fetch the rows, then you can use variable assignment in PHP to assign a variable to each user:
while($row ... {
${$row[user_ID]} = $row[postcode];
}
You will need a way to uniquely identify the variables, because then the last instance of that user_ID
will be the value of that variable I would recommend using an array. Perhaps something like:
${'user_' . $row[user_ID]}[] = $row[postcode];
This way, $user_39
will become an array containing (ab12cd, sk91ab, ab44ix)
.
Or you can throw all your data into an array like so:
$user_ID = $row[user_ID];
$array[$user_ID][] = $row[postcode];
Either of those two options would go inside your while
loop.
Clarification
If you want to call the postcode for a particular user, first put the data into a multi-dimensional array:
while($row ... {
$user_ID = $row[user_ID];
$array[$user_ID][] = $row[postcode];
}
The postcodes will then be stored like so (following example in question):
echo $array[39][0];
//ab12cd
echo $array[39][1];
//sk91ab
echo $array[39][2];
//ab44ix
[39]
represents the user_ID
Upvotes: 1
Reputation: 7228
Using PDO
$sth = $conn->prepare("SELECT * FROM directory, user_sightings WHERE directory.user_active != '' AND directory.ID = :uid AND user_sightings.user_ID = :uid");
$sth->execute(array(':uid' => $UID));
$sth->setFetchMode(PDO::FETCH_ASSOC);
while($row = $sth->fetch()) {
echo $row['postcode'] . "\n";
}
Upvotes: 0