MySQLi

#1
Orjan

Posted 06 February 2009 - 04:55 PM

Writes binary right handed and hex left handed

Moderators
3,299 posts

I have an interesting question.

At times, you want to nest loops based on sql queries, somewhat like


while ($xrow = mysql_fetch_array($xresult)) {

     while ($yrow = mysql_fetch_array($yresult) {

           // do things with $xrow and $yrow

     }

}

and that's not a problem.

but with mysqli, you need to loop through a result before starting a new one.
how can one work around that in the best way, to do the similar approach?

do I need to create two sql connections, or should I loop through one query first and rewind it and then do this?

any solutions?

#2
Guest_Jordan_*

Posted 06 February 2009 - 05:07 PM

Guests

I don't understand. Why don't you just loop in the same way with MySQLi?

    $query = "SELECT * FROM some query, etc....."
    if ($result = $mysqli->query($query)) {
        while($obj = $result->fetch_object()){
                 echo $obj->some_tableColumn;
        }
    }

#3
Orjan

Posted 06 February 2009 - 05:39 PM

Writes binary right handed and hex left handed

Moderators
3,299 posts

yes, but what about nestled loops? I get error messages that I can't start a new query unless I have fetched all the rows in the last query.

what if, I need to make a new query based on each row content from the last query,for example?

It seems to me that I can't just fetch a row, and then be happy with it and make a new query, then come back and fetch a second row in that old query...

#4
Guest_Jordan_*

Posted 06 February 2009 - 06:20 PM

Guests

My first instinct is to say this works fine for me:

    $query = "SELECT * FROM some query, etc....."
    if ($result = $mysqli->query($query)) {
        while($obj = $result->fetch_object()){
                 $newQuery = "SELECT * FROM <somtbl> WHERE id='{$obj->id}'";
                 $newResults = mysqli->query($query);
                 $newObj = $result->fetch_object();
                 print_r($newObj);
        }
    }

But I am recalling this from memory and I've second guessed myself. Perhaps this doesn't work? But I'm sure I've used this before.

#5
Orjan

Posted 06 February 2009 - 06:31 PM

Writes binary right handed and hex left handed

Moderators
3,299 posts

now I was testing for my self. and the problem was when using prepared statements...
a bit different, but still part of the issue.

Warning: mysqli::prepare() [function.mysqli-prepare]: All data must be fetched before a new statement prepare takes place

#6
Guest_Jordan_*

Posted 06 February 2009 - 06:33 PM

Guests

It sounds like you just didn't prepare your statement then. true or no?

#7
Orjan

Posted 06 February 2009 - 06:38 PM

Writes binary right handed and hex left handed

Moderators
3,299 posts

in this case, I just read one post (as it was such a query, that first hit would do)
and if I didn't close the statement, I couldn't prepare next one.