Dynamically create variables in while loop based on sql query

Question

SELECT a.uid, a.name_f, a.name_l, b.position FROM users a, room_members b WHERE a.uid = b.userid AND b.room_id=$room_id

I have the above query that selects the name, uid, profile pic and position of a certain user based on the room_id, and I want to return a while loop that assigns the following variables to any positions that returns in my query, dynamically.

$position1_name_f
$position1_name_l
$position1_uid
$position1_pic

$position2_name_f
$position2_name_l
$position2_uid
$position2_pic

So for example, if positions 4 and positions 8 are the only ones among the data returned, it would return the following variables and I would be able to use theses variables freely in my script.

$position4_name_f
$position4_name_l
$position4_uid
$position4_pic

$position8_name_f
$position8_name_l
$position8_uid
$position8_pic

There would be a maximum of 10 positions at the most. I desperately need those variables because I use them heavily with the layout of the site and there's no way for me to do a simple while loop echo; otherwise, everything would be a lot easier.

I tried using variable variables and I also tried deploy a counter but couldn't get either to work.

I started the following query and would really appreciate some help. Thanks

$room_members=mysql_query("SELECT a.uid, a.name_f, a.name_l, b.position FROM users a, room_members b WHERE a.uid = b.userid AND b.room_id=$room_id");
while($members_sql=mysql_fetch_array($room_members))
{
//Need some dynamic variables here...
}

Out of my last 11 questions, I have accepted 10 answers. So, thanks for the wonderful and incredibly useful suggestions but please don't offer advice without fully knowing the facts. — user1011713, Apr 18 '12 at 19:34
If anyone uses the answer from this questio, the following function works great while searching through the array: function search($array, $key, $value) { $results = array(); if (is_array($array)) { if (isset($array[$key]) && $array[$key] == $value) $results[] = $array; foreach ($array as $subarray) $results = array_merge($results, search($subarray, $key, $value)); } return $results; } courtesy of http://stackoverflow.com/questions/1019076/how-to-search-by-key-value-in-a-multidimensional-array-in-php — user1011713, Apr 18 '12 at 20:01
'this user has accepted an answer for 9 of 14 eligible questions' that is not 10 out of 11 ;-) — Daan Timmer, Apr 18 '12 at 21:49
Daan, I think you misunderstood what I said. I said, I have picked an answer for the LAST 10 OF 11 QUESTIONS that I have submitted. Understandable mistake on your end :) — user1011713, Apr 19 '12 at 01:58
Also "...simple reason that you had no clue what you were (trying to do)..." is why this website was created. Not sure where you're from but here in California, that kind of attitude would never fly. Please try to improve your attitude. — user1011713, Apr 19 '12 at 02:05

PeeHaa · Accepted Answer · 2012-04-18T19:20:09.063

2

You're doing it wrong!

That's what arrays are for:

$result = array();

$result[1] = array('name_f' => 'data',
                   'name_l' => 'data',
                   etc,
                   );

From your comment:

But how would I go about checking whether a position was set or returns back empty? I basically need if (isset(position_1)) {show position_1_name_f}

if (isset($result[1])) {
    echo $result[1]['name_f'];
}

So what you can do is the following:

$members = array();

$room_members = mysql_query("SELECT a.uid, a.name_f, a.name_l, b.position FROM users a, room_members b WHERE a.uid = b.userid AND b.room_id=$room_id");
while($members_sql=mysql_fetch_assoc($room_members)) {
    $members[] = $members_sql;
}

edited Apr 18 '12 at 19:20

answered Apr 18 '12 at 18:57

PeeHaa

71,436
58
190
262

But how would I go about checking whether a position was set or returns back empty? I basically need if (isset(position_1)) {show position_1_name_f} – user1011713 Apr 18 '12 at 19:07
Sorry, I'm still unclear about how I'd go assigning multiple arrays in the while loop. – user1011713 Apr 18 '12 at 19:16

score 1 · Answer 2 · answered Apr 18 '12 at 18:56

1

why not use arrays for this or objects with an implemented iterator?

should be much more easy :)

answered Apr 18 '12 at 18:56

Hajo

849
6
21

score 1 · Answer 3 · answered Apr 18 '12 at 18:59

1

You can build the variable name as a String like this:

$i = 1;
while($members_sql=mysql_fetch_array($room_members))
{
  $variablebase = 'position'.$i++.'_';
  $variablename = $variablebase.'name_f';
  $$variablename = $members_sql['name_f'];
}

After this $positionX_name_f are declared.

But wouldn't an array be a better solution?

answered Apr 18 '12 at 18:59

bardiir

14,556
9
41
66

Yuck! That last sentenced saved you there ;-) – PeeHaa Apr 18 '12 at 19:01
Hey... PHP is a language where `("61529519452809720693702583126814" == "61529519452809720000000000000000")` actually returns true, so don't get angry for people to do ugly things too :P – bardiir Apr 18 '12 at 19:07
This would return position_1_name_f but if position_1 isn't set, it would still set this variale based on another position being returned. I'd need position_10_name_f and position_4_name_f for example if those are the only ones returned. With your code, I think position_1_name_f and position_2_name_f would be set instead. – user1011713 Apr 18 '12 at 19:09
@bardiir my comment was in no way meant to be angry. P.S. is that the worst thing you can come up with about [what sucks in PHP](http://me.veekun.com/blog/2012/04/09/php-a-fractal-of-bad-design/). ;-) – PeeHaa Apr 18 '12 at 19:12
@user1011713 you should seriously consider using an array – bardiir Apr 18 '12 at 19:25
@RepWhoringPeeHaa nothing taken as angry and i've recently read the fractal of bad design post :D – bardiir Apr 18 '12 at 19:25

Dynamically create variables in while loop based on sql query

3 Answers3