storing data in an array

[mystrymaster]

up until now I have if I wanted data from a db would access it like this

$sql = mysql_query("SELECT ID, name, location FROM users where ID = $userid");
WHILE ($result = mysql_fetch_array($sql)) {
echo "<table>\n<tr>\n<td>\n$result[name]</td>\n
<td>$result[location]</td>\n</tr>\n</table>\n<BR>";
}

now while this indeed does give me the data I need I would like to seperate the sql from the layout a little more and store the results of the sql into an array and access it as I need it.

Is this possible? Could you point me in the right direction with this?

As soon as this post is made I am going to do a little more research on it and see what I can come up with also.

Thanks in advance

[cgraz]

what do you mean? $result is an array. Look at the function you're using that is filling it with values: mysql_fetch_array/b

Cgraz

[mystrymaster]

yeah I know but what I mean basically then is there a way to access that information outside of a while loop so I can have the sql in the begining of the page and then access the data porion of it later on outside the while loop.

after reading the article on arrays here I found this statement

$ArrayInventory = array(); 
$ArrayInventory["arrayNovels"] = array("Andromeda Strain", "Rainbow Six", "Lord of the Rings"); 
$ArrayInventory["arrayKidsBooks"] = array("Jack and Jill", "Clifford the Big Red Dog", "How Stuff Works"); 
$ArrayInventory["arrayMagazines"] = array("Motorcycle World", "Guitar One", "Car and Driver"); 


foreach($ArrayInventory as $ArrName => $ArrElem) { 
echo "<br>".$ArrName."<br><br>"; 
foreach($ArrElem as $Element) { 
echo $Element."<br>"; 
} 
} 

what I need is to be able to acces that information later instead of in the for while loop.

here is where I am at let me know if I am on the right path and where to go from here to get at that data because I am stuck

$result = mysql_query("SELECT * FROM holeblue");
WHILE ($row = mysql_fetch_array($result)){
	$i= 0;
	$name = "course$i";
	$arraycourse[$name] = array("$row[0]", "$row[1]", "$row[2]","$row[3]", "$row[4]");
	$i++;
	}

now later on I want to be able to simply pull the piece of data within the html output?

does this make any sense?

[cgraz]

i only skimmed through this, but after you use the values of $row and loop through them, you can use mysql_data_seek to reset the data pointer, then be able to loop through the results again later. IT would look something like

while($row = mysql_fetch_array($sql)) {
   // code here
}  

// reset pointer (i believe this will work)
mysql_data_seek($sql, 0);

// now use again

while($row2 = mysql_fetch_array($sql)) {
   // code here
}

Cgraz

[mystrymaster]

llright I tried it and unfortunately it did not work

Any other ideas?

got it - you gave me the push in the righth direction

here is the code that will do what I wanted

$row = mysql_fetch_row($result);
		echo $row[0];
		echo "<BR>";
		echo $row[1];
		echo "<BR>";
		echo $row[2];
		echo "<BR>";
		echo $row[3];
		echo "<BR>";
		echo $row[4];
		echo "<BR>";
mysql_data_seek($result, 1);

$row2 = mysql_fetch_row($result);
		echo $row2[0];
		echo "<BR>";
		echo $row2[1];
		echo "<BR>";
		echo $row2[2];
		echo "<BR>";
		echo $row2[3];
		echo "<BR>";
		echo $row2[4];
		echo "<BR>";

the mysql_data_seek command has to be set to the row number that I want and I had to use mysql_fetch_row instead of array so I would only get one row at a time

thank you very much for pointing me in the right direction yet not giving me a correct answer 🙂

[cgraz]

you must've done something wrong. I used the following code on my server and got the proper output:

<?  include("1/lib/global_functions.php");
    include("admin/lib/admin_functions.php");


$query = mysql_query("SELECT * FROM $table") or die(mysql_error());

while($row = mysql_fetch_array($query)) {
   echo $row["model"] . "<br>";
}

echo "<br>Now I reset the internal pointer<br><br><br><br>";

mysql_data_seek($query, 0);

while($row = mysql_fetch_array($query)) {
   echo $row["model"] . "<br>";
}

?>

Outputted

3000GT
3000GT
350Z
Escalade
Prelude
330ci
Ram
Jimmy

Now I reset the internal pointer

3000GT
3000GT
350Z
Escalade
Prelude
330ci
Ram
Jimmy

Cgraz

[mystrymaster]

yeah but you got the same data twice I want different data each time

[cgraz]

ou could just use the names of the fields in your database

for example, you have 3 fields: field1, field2, and field3

while($row = mysql_fetch_assoc($sql)) {
   echo "First field: " . $row["field1"] . "<br>\n";
   echo "Second field: " . $row["field2"] . "<br>\n";   

   echo "Third field: " . $row["field3"] . "<br>\n";
}

Will output field1, field2, and field3 of every row in your database (or atleast the rows that were returned).

Cgraz

[mystrymaster]

correct but I want different rows each time

I want all the fields associated with row 1 then row 2 but completely outputted completely seperate from each other

[cgraz]

yeah, the code i gave you would lookk like this (brackets are invisible, just so you can see the rows)

[row1]
field1: value
field2: value
field3: value
[row2]
field1: value
field2: value
field3: value
[row3]
field1: value
field2: value
field3: value

And so on. If you want to use them in different parts of the page, then you will need to use mysql_data_seek so that it starts at the row you want.

Cgraz

[ahundiak]

Cycling through a result set and storing the records in an array for later use works fine.

$result = mysql_query('SELECT * FROM person');
while($person = mysql_fetch_object($result))
{
$persons[$person->person_id] =& $person;
}
mysql_release($result);

Now later in the code you can get a person by their id without any sql involvement at all.

$person =& $persons[27];
echo $person->first_name;

(I used objects instead of arrays because the syntax is cleaner but arrays will work the same).