[SOLVED] assign variable to mysql row (id = value)

[ahs10]

i have a table in a mysql database that has two columns, id and value.  i would like to create a variable name out of each id and give it the corresponding value.  how do i do that?  below i have the script that will echo the text in th format i want, now if i can turn that echo into actually assigning a value to a variable name, i'll be set.

 

$result = mysql_query("SELECT * FROM reporting");

 

while ($row = mysql_fetch_assoc($result)) {

echo $row['id'] . " = " . $row['value'];

}

[MadTechie]

am i correct in saying you wish to "update" the values ?

 

if so..

your need to look into MySQL a little more

for example

$result = mysql_query("UPDATE reporting SET `Value`='NewValue' WHERE ID=2 ");

[ahs10]

no i don't want to update anything in the database, the SELECT is correct.

 

i want to create a variable with the name that is equal to the contents of $row['id'] and then assign it the value of $row['value'].  i want it to do this for each row in the table.

[MadTechie]

OK you could do this

 

$id =$row['id'];

$$id = $row['value'];

 

BUT i assume that id is a number and a variable can NOT start with a number and even then you will probably end up in a mess with this..

 

may i ask why you wish to do this ?

[trq]

i want to create a variable with the name that is equal to the contents of $row['id'] and then assign it the value of $row['value'].

 

Providing $row['id'] is not a number (variable names cannot be numbers) this will work for you.

 

<?php

  $result = mysql_query("SELECT * FROM reporting");

  while ($row = mysql_fetch_assoc($result)) {
    ${$row['id']} = $row['value'];
  }

?>

 

Why you would want to do this is behond me though.

[MadTechie]

OK Thorpe is cleaner but we're on the same page and neither know why you would want this!

[ahs10]

i'm really amazed that this is not common.  i need to perform some math with the values in the database.  the id's are always the same, but the values UPDATED by another php script.  i want this script to fetch those values and assign a variable name to each of them so i can make some calculations to spit out in an excel spreadsheet.

 

is there a better way to accomplish this?

 

thanks a lot for these answers.  i haven't tried the cleaner one yet, but i'm quite sure it will work.

[MadTechie]

it just seams strange, as their alway the same i assume you have a fixed number of records.. which makes me wonder why you don't have these as fields instead!,

[ahs10]

nope, not a fixed number of records.  the ids always stay the same, but there's a new one added every few weeks.

[trq]

nope, not a fixed number of records.  the ids always stay the same, but there's a new one added every few weeks.

 

Then I really see no point in doing what your doing. This will meen you'll actually need to modify your code all the time. An example, say you want to display the id's a1 and a2.

 

<?php

  $result = mysql_query("SELECT * FROM reporting");

  while ($row = mysql_fetch_assoc($result)) {
    ${$row['id']} = $row['value'];
  }

  echo $a1;
  echo $a2;

?>

 

Now what happens when you want to echo a5? You need to modify your code. Makes no sense.

[ahs10]

i don't get it.  why would i need to modify my code if a new row is added?  it's selecting everything in the table as a row, then assiging the values to a variable.  the name of the variable is $row['id'] and the value of that variable is $row['value'].  if a new row is added to the database, why wouldn't it fetch it too?

[ahs10]

as i hit post i caught the misunderstanding here and now realize i did not explain this entirely. i like your profile avatar, so let me splain lucy.

 

so this table that's the topic o convo, is never echoed. i believe that's where you were thinking i would change my code, to add to the list that echos the results. it's all used for simple math computations, and with conformity in my naming convention of variables and id's, i can add rows to this table and it all work smoothly, without any code altering.

[trq]

i would like to create a variable name out of each id and give it the corresponding value.

 

the name of the variable is $row['id'] and the value of that variable is $row['value'].

 

These two comments contridict each other.

 

mysql_fetch_assoc() returns an array. Each element of this array represents a field in your database. For each consecutive call to mysql_fetch_assoc() you get a new row. In your first post you said you wanted to 'create a variable name out of each id and give it the corresponding value'. You don't need to.

 

You really need to explain exactly what it is your wanting to do. Posting your code would also help.

 

[trq]

Maybe all you really want is...

 

<?php

$result = mysql_query("SELECT * FROM reporting");

while ($row = mysql_fetch_assoc($result)) {
  $row['id'] = $row['value'];
}

?>

 

But even that makes little sense. Why not just do your calculations using $row['value'] instead of $row['id']?

[ahs10]

my fault again, i forgot to mark this as solved (i'm usually really good about that too).  the two statements you quoted from me don't contradict each other.  that was me trying to verbalize what your code does.  problem solved, my bad.

 

anyweezer, it totally floors me that the usefullness is escaping you.  yes, to post my code would do much better than me trying to verbalize it, but in order to do that i have to do too much to it in order to make it acceptable for a public forum.  feel free to email if you'd like to discuss this further though.  thanks for your help!

[trq]

If that last piece of code is seriously all you needed then instead you could simply use.

 

<?php

// you can assign names to your fields within the query.
$result = mysql_query("SELECT value AS id FROM reporting");

while ($row = mysql_fetch_assoc($result)) {
  // now $row['id'] is equivelent to what was $row['value'] in my previous post.
}

?>

[ahs10]

keep cleaning it, there'll be nothing left.  i actually have to go with the other code cuz i'm adding a string of text onto the variable name as well.  oh nevermind....

 

thanks again! i'll also be checking out your book this weekend