Best method of connecting to a database (Multiple Queries)

[thing3b]

Lately I seem to have been having a few problems with my PHP app. Every now and then the logs would show "Not enough database connections". Recently the errors have been more like "Not enough memory".

I realised today what the real problem was and was wondering what people thought about it.

The problem that I had seems to be caused by having more than one query connected at a time. My most used database code goes something like this:

(Although I am using Zend Framework style here it should not matter)

$stmt = $db->query($sql);
while ($row = $stmt->fetch()) {
 $someClass->method($row['title']);
 echo $row['title'];
}

But then in $someClass->method I would be doing more database stuff.

function method(){
 while ($row = $stmt->fetch()) {
  echo $row['somethingelse'];
 }
}

Now here are my questions:
- Am I right, assuming that every separate SQL query (connected at the same time) requires it's own database connection?
- Do multiple database connections use a significant amount of memory?
- Has anyone else hit this?
- Does anyone have another way of coding this?

The code by the way was fixed by getting all the data from the SQL query at once, so that I did not need to be fetching from the database every loop. Something like this:

$stmt = $db->query($sql);
$allData = $stmt->fetchAll()
foreach( $allData as $rows){
 $someClass->method($row['title']);
 echo $row['title'];
}

[jatar_k]

>> Am I right, assuming that every separate SQL query requires it's own database connection?

no, they only require a single connection, a connection can be reused through out execution as long as it is within scope

>> Do multiple database connections use a significant amount of memory?

the amount of mem they each take up should be minute

>> Has anyone else hit this?

I've hit mem limits tons of time but the real issue stems from loading way too much garbage into memory, at first glance I don't see a glaring issue with the code you posted. You should look at everything that is set, maybe something was getting set and retaining too much space.

options for looking at what is defined
[php.net...]
[php.net...]

>> Does anyone have another way of coding this?

it seems you found a way and it would be hard to suggest

[firefly2442]

You could try persistent connections, maybe that would help?

[coopster]

No, persistent connections likely won't help. They are so often misunderstood. Here is a thread that touches on the subject with some additional links:

[webmasterworld.com...]

Also, a search over the forums will turn up some other past discussions on persistent connections that are worth reading if the topic interests you. The MySQL link from the thread above though is by far one of the most straightforward statements in regards to the topic.