How to use a MySQL query result in a function, when the query is created outside of it-CodePudding

This must be super simple, but I can't get there.

I have a simple mysql query, that I'm able to echo on the screen... no problem here. What I want to do, is take that query result, and use it in a function.

Let say I have this query: (I'm omiting parts of the code to simplify, the query works fine, and as I said I can do what ever I want from it outside the function)

$sql = 'SELECT * FROM someTable';
$result = $db_connection->query($sql);

Now I want to use $result in a function. I tried (no success):

function _checkQuery(){
    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
            echo $row["Data"];
        }
    }
}

and also (no success):

_checkQuery($result);

function _checkQuery($db){
    if ($db->num_rows > 0) {
        while($row = $db->fetch_assoc()) {
            echo $row["Data"];
        }
    }
}

This for sure is simple... but it's past midnight, I'm 2 hours looking for a solution and can't find any... any help is apreciated.

Thank you.

EDIT -

I just did some testing and I came to the folowing conclusion, using this piece of code:

function _checkHour($db){
    if ($db->num_rows > 0) {
        echo "x";
        while($row = $db->fetch_assoc()) {
            echo "y";
        }
    }

Conclusions: a) echo "x" works, I get the "x" on screen, so the $db is not empty. b) echo "y" does not work, but the function ends it's course normaly, so I assume the problem is with the line "while($row = $db->fetch_assoc()) {". In the next answer he says I should use "fetch_assoc()" but the piece of code he presents, but I can´t it to work, the functions brakes instantly...

CodePudding user response：

If you do not pass the $result and you run it locally, does the query work?
The first thing to do is copy and past the $sql into mySQL with out any php. <br
You also need to make sure the field name is Data and not data.

It should work, but there are some possibilities.
I have no idea why you would pass the $result object. Especially when you used SELECT * the result object can be very large. So pass the query in the function. If you insist on using a function.

You coding style drives me nuts.
Because of the Intel micro-architecture, calling a function is inefficient. It has to push the current context on to the stack and when it returns it has to pop the stack. That's unnecessary overhead. You need a good reason to use a function. I don't see a reason here.

NEVER use: `SELECT * FROM'
Instead use:

SELECT `id`,`Data` FROM `some_table` WHERE 1

I added id to show how multiple columns are used. But can use just list($data)

NEVER use `fetch_assoc()'
Instead use:

while(list($id,$data) = $db-> $result->fetch_array(MYSQLI_NUM);)) {

Now you have nice neat variables and you did not add every column of ever row into the results object ($results). And you don't have to use inefficient associative arrays.

It's much easier to catch an error if the query fails.
If your column name is data and you use Data the query will fail and you will quickly know there is a problem with the case of the column name. By using $row['data'] there will be no error generated.
And I like to see the fields I am using from that query.

Do you understand that when you use SELECT * the result object contains every column from every row. If the table has many columns and the result has many rows you are wasting a lot of memory.
You want to keep variables as small as possible so you do not overflow the microprocessor cache memory.
You want to keep variable local to the procedure and within called function. You do not want global or public variables if possible.

I was writing code in the 1970s. I was an electrical engineer for 10 years. I know how the the assembly code, the microprocessor micro code, and how the processor executes the micro instruction functions and how your various routine get executed. Which is faster $i , $i = $i 1, or $i = 1?
Once upon a time $i would have been faster because Intel had an inc (increment) instruction. They no longer have an inc instruction.

Every little thing matters. All the little stuff often adds up to be big stuff.

CodePudding user response：

I have found the solution for my own problem! Finally. Thanks to the answers this post received I managed to get back on track.

It was all due to my lack of understandiung of what is really hapening to the $result variable.

For all purposes this code does work:

function _checkHour($db){
if ($db->num_rows > 0) {
    while($row = $db->fetch_assoc()) {
        echo $row["Data"] . '</br>'; //"Data" is valid column of the query
    }
}

I was thinking that $result was an simple array that could be scanned/searched/access as any array, but it turns out it's a pointer (still not sure to where it's pointing though). As I understand it, each time we scan this variable, the pointer stays at hes last location, and must be "reseted" if we want to scan it again from the beguining, using:

$result->data_seek(0);

In my case, since I was ouputing the $result for test purposes outside the function (before running the function) the pointer was already at the end (of whatever it's pointing to) and therefor had nothing else to show... it was as if it was empty.

In my case, I simply have to use the line above to "reset" my pointer and run the function as many time as I want.

If anyone can explain me exactly what is going on I would really appreciate it. Thank you all for you help.