Dynamic variables not geting values assigned

[michaelmoxley]

So I have this odd issue that I am looking for some help on. I have an array that is dynamically generated that I call $lookup. This array is a list of column names that I want to lookup in a MySQL db.

 

For example, I have a MySQL table (user) with columns : id, first_name, last_name, email. The array $lookup will have (id,last_name, email):

 

$lookup = array('id','last_name', 'email');

foreach($lookup as $value) {
$query = "SELECT $value FROM user WHERE id = $id LIMIT 1";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
while($row = mysql_fetch_object($result)){
echo $row->$value.":".$value;}

 

The result is odd in that I get an output of the following

1:id

:last_name

:email

 

For some reason, the value for last_name and email do not show. I have tried doing the foreach call inside the while, but I get the same result. I also tried $row->{$value}, but that did not help. I am a bit confused on this one, and could use some help. Thanks

[Muddy_Funster]

no no no, your calling a query inside a loop. If I had a ruller and could reach your knuckles you'de be bleeding just now. Looping through the array is not what you want to do. You want to use the PHP function implode(). This lets you take an array and flatten it into a string, using the delimiter of your choice. So...

$lookup = array('id','last_name','email');
$value = implode(", ", $lookup);
$query = "SELECT ($value) FROM user WHERE id = $id LIMIT 1";
$result = mysql_query($query) or die("Well that didn't work...and here's why : <br />".mysql_error());
while($row=mysql_fetch_array($result){
foreach ($lookup as $key => $value){
echo "{$row[$value]} : $value    ";
}
}

That's untested but removes the query from inside the loop, reducing the transactions on the database.

[Psycho]

Well, first of all that is a really bad way to get the data you want from a table. You should NEVER run queries within loops. And, in this case, there is a very, very easy solution. Just use your $lookup array to create ONE query with all the fields you want. I'll show you some code for that at the end. Also, with respect to your code, you would do yourself a great favor by giving your variables meaningful names. In the above example $value doesn't contain a "value" it contains the name/label/field of a value. It was kind of awkward reading the code for me. Also, your query has a LIMIT 1 on it but the processing logic uses a loop to get the records. That isn't needed.

 

As to your question, it's difficult to say what the problem is. To be honest, I've never used a variable to the right of the 'arrow' for an object property $row->$value. But, I will assume it is valid. You don't show the rest of the foreach loop. So, if the value of $id changes in the loop it could be that the 2nd and 3rd iterations are not matching any records in the DB.

 

$fields = array('id', 'last_name', 'email');

$selectList = implode(', ', $fields);
$query = "SELECT $selectList FROM user WHERE id = $id LIMIT 1";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

if(mysql_num_rows($result))
{
  $row = mysql_fetch_object($result);
  foreach($fields as $field)
  {
     echo $row->$field . ":" . $field;
  }
}

 

EDIT: muddy beat me to it, but I'll post this anyway

Edited January 16, 2013 by Psycho

[Jessica]

I've never used a variable to the right of the 'arrow' for an object property $row->$value. But, I will assume it is valid. 

 

Just wanted to say I have done that before and it is perfectly valid, as long as it's a valid property on the object. It does work. Your assumption is correct.

 

[michaelmoxley]

no no no, your calling a query inside a loop. If I had a ruller and could reach your knuckles you'de be bleeding just now. Looping through the array is not what you want to do. You want to use the PHP function implode(). This lets you take an array and flatten it into a string, using the delimiter of your choice. So...

$lookup =

Bloody knuckles aside I did try an application like you wrote first, but when that did not work I reversed the calls.. Didn't know that was bad, but will avoid those in the future !

 

{$row[$value]}

 

Now that is what made the difference! I was trying $row->{$value} and the {$row->[$query]} hammered this home. Perfect and thanks. Now to finish this and get some neosporin on these bleeding knucles of mine.

Edited January 16, 2013 by michaelmoxley

[michaelmoxley]

Thanks all!

Edited January 16, 2013 by michaelmoxley

[Muddy_Funster]

You are very welcome.

 

Just to say, I used the $row[$value] rather than the $row->$value because I used mysql_fetch_array() rather than the mysql_fetch_object() that was in your original code. mysql_fetch_array() fetches the dataset as an array, rather than an object, so the use of object syntax such as $obj->param wouldn't work. For the most part I would suggest avoiding mysql_fetch_object(). It's not that there is anything at all wrong with using it, it just tends to over complicate things in my opinion. There are some cases when it is the better way to go, but for a small and shallow dataset, such as the one in your example, it's not really giving you any benifits.

 

Good luck, and I hope the wounds heal without scaring (be thankfull you weren't using select * as well )