[SOLVED] passing mysql resultset to a php function...is it possible?

[neogenx]

I'm just wondering if its possible to pass a mysql resultset (resource handler) to a php function, then from there the php function manipulate the data in the result set.

 

<code>

mysql_connect(" ....");

 

$result = mysql_query("select * from mytable");

 

mysql_close();

 

passResultToFunction($result);

 

function passResultToFunction($mysqlResultSet){

 

$temp = mysql_fetch_array($mysqlResultSet);

 

  ... manipulate the result set that been passed in ...

 

}

</code>

 

from the logic stated above, php just return an error message saying that the variable "$mysqlResultSet" is not a valid mysql resource.

 

can someone confirm if passing its is possible to pass mysql resultset to a php function.

[corbin]

A MySQL result set is just a resource if I remember correctly.

 

 

With buffered queries (the default kind) the result resource isn't actually the entire result set, but rather a 'pointer' to it, so when you are closing the mysql connection, you are breaking the link.

[neogenx]

If closing the connection breaks the link, then I don't understand why this work:

 

<?php
mysql_connect("connection info");

$result = mysql_query("select * from mytable");

mysql_close();

$temp = mysql_fetch_array($result);

echo $temp['samplerow']; // assuming there are data.

// result of $temp['samplerow'] would output "Hello World"
?>

 

so I don't think closing the connection has to do with it.

[corbin]

Try to fetch more than 1 row from it.  Try to fetch like... 20 rows from it, and it will probably error.

 

 

Try removing the mysql_close and see what happens.

 

 

 

Also, try adding var_dump($mysqlResultSet); and see what it says.

[corbin]

Oh.... I just noticed something.

 

 

You are selecting a database, yes?

 

 

Edit:  Just tested it myself.  Turns out it is possible to act on a result set after the connection is closed.  Weird.  I just realized I had buffered and unbuffered queries backwards lol.  Unbuffered are the ones where all of the data isn't transfered at once.

 

 

Anyway, I think your query is failing to begin with.

[neogenx]

Oh.... I just noticed something.

 

 

You are selecting a database, yes?

 

 

Edit:  Just tested it myself.  Turns out it is possible to act on a result set after the connection is closed.  Weird.  I just realized I had buffered and unbuffered queries backwards lol.  Unbuffered are the ones where all of the data isn't transfered at once.

 

 

Anyway, I think your query is failing to begin with.

 

Yes I am selecting a database before the actual mysql_query.

 

Try to fetch more than 1 row from it.  Try to fetch like... 20 rows from it, and it will probably error.

 

 

Try removing the mysql_close and see what happens.

 

 

 

Also, try adding var_dump($mysqlResultSet); and see what it says.

 

with the example code in my previous post, it fetched the data just fine, I am able to fetch over 162 records no problem. The only problem that I'm having is that my application requires data from the database and each time it fetch the data, 162 records is being return, but I got to do this like 6 time therefore it has slowed down my application performance drastically. So having that said, I though that to speed thing up I should fetch the data once then keep reusing it when I need it. Its the reason why I wanted to know if its possible to pass mysql resultset to a php function.

 

The only workaround that I could think of and worked was to fetch the data once and store the result into a session variable, then whenever i needed that data I just fetch it from the session variable, it seem to work fine and increased performance of the application by just a few seconds. Though storing a mysql resultset in a session variable doesn't sound like a good idea and rather odd, but I can't think of any other solution at the moment.

[corbin]

So wait, you were acting on the same result set more than once?  Once you get to the end of a result set, it starts erroring.

[neogenx]

So wait, you were acting on the same result set more than once?  Once you get to the end of a result set, it starts erroring.

 

Well that is my intention, to use only one resultset over and over, instead of having to re-fetch the data from the database each time it is needed. As for erroring once you reached the end of a resultset, I would agree with you on that, however you can re-use the resultset by resetting the data pointer on the resultset back to the first record by using the function

 

mysql_data_seek("$mysqlResultSet", 0);

 

 

 

[corbin]

Yeah....  Didn't think you were doing that though ;p.

 

 

Also, a variable when alone shouldn't be encased in quotes, especially a resource.

 

 

 

You could just put it into an array....

 

For example, assume $result is a result from mysql_query().

 

$a = array();
while($r = mysql_fetch_assoc($result)) {
    $a[] = $r;
}

[neogenx]

Yeah....  Didn't think you were doing that though ;p.

 

 

Also, a variable when alone shouldn't be encased in quotes, especially a resource.

 

 

 

You could just put it into an array....

 

 

 

For example, assume $result is a result from mysql_query().

 

$a = array();
while($r = mysql_fetch_assoc($result)) {
    $a[] = $r;
}

 

hmm, I will give that a try and post back the result. Though doing so means that each time I need a specific data I would have to search through the array for the correct record string and then explode the the string to get a specific data does it not? or is there a way I can use key -> value pair for this?

[corbin]

It wouldn't be strings.  It would be a multidimensional array.

 

print_r() it, and you'll see what I mean.

[neogenx]

After many trials and errors, I've manage to narrow down the bottle-neck of my application. Apparently it wasn't the fetching data process of the application that slowed it down tremendously.  It was the dynamic div + input field that is being generated for each of the record data that caused the slow down.

 

Thanks Corbin for all your helps and inputs.