Help with MySQL Queries

[Anon]

I've run into a problem that I'm sure has been answered, but I don't have the experience to be able to find any solution.

I would like to be able to pull a lump of information from my MySQL database, and then use a function to sort information out of it. Unfortunately, I can't find anyway to allow my functions to reference a value that hasn't been passed to them, and I can't pass the results of the Database query several times.

ie:

$query = "select * from table order by id desc limit 250";
$result = mysql("database",$query);

function getinfo($field1,$field2) {
while ($data = mysql_fetch_array($result)) { etc. etc. }
}

Gives me an invalid MySQL resource complaint against $result in the function. Alternatively, if I try to change the function to getinfo($field1,$field2,$mysqlarray) { } then the second time I run the function the $result variable is completely toasted. The entire point of my trying to rewrite this (Which is extremely difficult to explain, although it makes sense in MY head. 🙂 ) is to minimize SQL queries. Is there anyway to maintain the integrity of that $result array without requerying? I've tried $resultbak = $result, and it doesn't help. 🙁

If you need more information, I'll do my best to clarify.

[Anon]

Firstly, you'll be wanting to use mysql_query, not mysql as a function.
http://www.php.net/manual/en/function.mysql-query.php

e.g. $result = mysql_query("database",$query);

second, you can move all the resulting data in to arrays and then do whatever you want with them. You have to loop through the result to do so.

secodn, if you just want to move the pointer back to the first "row" of your resultset, you can use mysql_data_seek:

http://www.php.net/manual/en/function.mysql-data-seek.php

[tasistro]

This is an example code of what I've done to handle that problem

class resumenCuentas
{
var $db;
var $sql_cmd;
// this variable hold the results from the sql and is global within the class by calling $this->sql_result
var $sql_result;
var $reportparams;
var $contareport;

function _requestData()
{
    $this->sql_result=mysql_query($this->sql_cmd,$this->db) or die ("UNABLE TO REQUEST DATA<br>SQL :".$this->sql_cmd."<br>");
    if (mysql_num_rows($this->sql_result)>0)
    {
        //echo "SQL WITH DATA<br>";
        return 1;
    }
    else
    {
        //echo "SQL WITHOUT DATA<br>";
        return 0;
    }
}

// this function actually creates the sql string, the idea is that you can override it in other class instances

function _renderSQL()
{
    $this->sql_cmd="select operaciones.descripcion as tipo, cuentas.nombre as bla bla bla";
    //"SELECT * FROM fotoventas WHERE status=\"21\"";
}

function renderReporte()
{

// call the above function to creat the sql string
$this->_renderSQL();

// actually run the sql and if data is returned run process on it
if ($this->_requestData()>0)
{
while ($result_row=mysql_fetch_array($this->sql_result))
// run processes on the result rows, note that $result_row is local to this function, but the result is being held inside $this->sql_result;
// in other words $this->sql_result is the same as your $result, but can be referenced in any function inside the class by $this->sql_result
{
}
}
}

hope it helps

saludos
Gerardo