rows from DB --> variables

How to create a number of variables, based on the numbr of rows returned

[Grenz]

Hi all

Im still new to php.
I need a function to my website, where I call my DB and for each row returned, values are stored as variables.
The variables are sent to my webpage (in flash) for later use.

Example:

2 rows in the DB:
id=1, name=joe, date=01.03
id=2, name=al, date=02.04

The variables defined should then be:

id1=1
id2=2

name1=joe
name2=al

date1=01.03
date2=02.04

If there where 3 rows the variables should be something like:

id1=1
id2=2
id3=3
... etc etc.

I have made the connection to the DB, retrieve 1 row, and make the variables for the 1 row. But I dont know how to make the function so it assigns the variables for each row in the DB.

Any suggestions or links to tutorials etc.?

Thanks in advance.

[rocknbil]

Your mistake, I think, is storing them in variables in the first place. As you loop through each row, the variable value will be replaced by the next one.

You may consider building something on the fly. I'm not sure how you're getting the data to Flash, but here's one example, in which you build an XML string as you traverse the rows:

$xml=NULL; 
$query = "select id,name,`date` from table"; 
// Note that "date" is a very bad name for a database  
//table field, it is a data type 
$result = mysql_query($query); 
while ($row=mysql_fetch_array($result)) { 
 $xml .= '<userid="' . $row['id'] . '" name="' . $row['name'] . 
 '" date="' . $row['date'] . '">'; 
} 
if ($xml) { 
$xml = '<?xml version="1.0" encoding="ISO-8859-1"?> 
<userData>' . 
$xml . 
'</userData>'; 
} 

You would then proceed to write the XML file out to a file in a location known by your Flash.

Note that $row, and it's subsequent members ($row['id'], $row['name'] and $row['date']) are overwritten each time the loop is traversed and only the last record's value will exist after the while loop. We use them, repopulate them, then we're done with them.

This is why you might not want to create persistent variables that live after the loop, this will make for a memory hog if you have thousands of variables.

[Readie]

If you want to get them as variables and not an XML string despite Rocknbil's advice above, the following method should suffice:

-----

$sql = 'SELECT * FROM table_name ORDER BY id ASC';
$result = mysql_query($sql);
$rows = mysql_num_rows($result);
 
for($i = 0; $i < $rows; $i++) {
$entryid[$i] = mysql_result($result,$i,"id");
$entryname[$i] = mysql_result($result,$i,"name");
$entrydate[$i] = mysql_result($result,$i,"date");
}

And you can then call a variable by saying:

$entryid[3]

Which will get you the id for the third entry into the database.

[Readie]

Which will get you the id for the third entry into the database.

Sorry, $entryid[3] will actually return the FOURTH entry into the database table, as [0] is the first.

[Grenz]

Hi
Thanks for the reply, and sorry for my late comment.
I have implemented the above code and it works.

However I need to extend it a bit.

In my flashfile, a number of containers, equal to the number of rows in the DB is created (this is working).

Each container then loads an image, and the name of the image is taken from the DB, using the above code.

But I dont know how to send the variables properly.

In order to pass the variables to flash, the php syntax is: echo "&flashvariablename=$phpvariablename";

This also works fine. The problem is what to do whit the "i".

In my flashfile, I use a similar code to dynamically create the containers, so I need something from PHP, where "i" variables are sent, fx:

echo "&imagename+i=$entryid[$i]";

If I understand it correctly, this would send "i" variables with the result from the DB.

Is this correct? or do you know how to do it?

[Grenz]

Actuallyt, I just got a better suggestion. How about:

echo for ($i =1; $i < $rows; $i++) {
"&imageid+$i=$entryid[$i]";
}
Shouldnt this send "i" variables, all called imageid1, imageid2 etc, and each variable would contain the corresponding result from theDB row?