query called in function error

[eluser]sixpack434[/eluser]
ok please help, my head is about to explode. Outside codeigniter this works but for some reason it doesn't inside the codeigniter framework. i'm in the process of transfering my site to using codeigniters framework. anyway the problem:

In my index page I am trying to show for lesson boxes by called a function called showLesson($lessonNo);

e.g.
$query = $this->db->query("SELECT lessonNo FROM lesson ORDER BY dateTime DESC LIMIT 4");
foreach($query->result_array() as $row){

showLesson(10);
}


In showLesson I have a query to get lesson details and display them e.g.
$lessonDetails = mysql_fetch_assoc(mysql_query("SELECT * FROM lesson WHERE lessonNo='$lessonNo'"));
$name = $lessonDetails["name"];
$difficulty = $lessonDetails["difficulty"];
$details = $lessonDetails["details"];

For some reason only the first lesson is shown on the index page and not the other 3. I get the following error

Message: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

referring to $lessonDetails above in showLesson. Now this works outside codeigniter no problem, why is codeigniter being difficult!!

thanks

[eluser]Rick Jolly[/eluser]
I don't know what the problem is. Maybe it would help if you posted your code "as is" and surrounding it in "code" blocks would help readability.

This is slightly off topic, but why use the 2 queries? Couldn't you make this into one query:

Code:

$query = $this->db->query("SELECT * FROM lesson ORDER BY dateTime DESC LIMIT 4");

Also, for readability/maintainability it might help to consistently use CI database methods instead of mixing native mysql functions.

[eluser]sixpack434[/eluser]
ok it seems like a bug in codeigniter as mentioned in post http://ellislab.com/forums/viewthread/49156/

It mentions a fix for oci8 dbdriver, but how do i fix it for mysql?

thanks

[eluser]sixpack434[/eluser]
I found the problem, In the database config file persistent connection need to be turned off i.e. $db['default']['pconnect'] = FALSE;

If this is set to true then if your running queries in a loop, only the first query will work and the rest will throw errors. It took me 3 days now to fix this but i'm glad it's resolved now