MySql Correct Method of Coding

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Gareth
Forum Newbie
Posts: 1
Joined: Mon Nov 20, 2006 8:20 am

MySql Correct Method of Coding

Post by Gareth »

Hi guys,

First time poster on here and I apologise for the stupid post.

I have numerous sites written in PHP. I have different coding approaches to my database access. Can someone post up a code snippet where needed as well as answering some of my questions?

Q. Should I open and close my connection per request to the database or per page?

Q. Does the connection close automatically once the page dies, or am I risking open connections?

Q. When I make a select request to the database and I get no data back, what is the best method of handling this?

Q. Can you post a code snippet on a standard way of making an SQL Request to the database and then accessing the data, including error handling and dealing with empty datasets?

Its just I have become somewhat confused as the best methods to do things and lately my MySql server is dying from my crass db code. I need some STANDARDS guidance once and for all :)

Help? Please....:)
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: MySql Correct Method of Coding

Post by Mordred »

> Q. Should I open and close my connection per request to the database or per page?
Per page.

> Q. Does the connection close automatically once the page dies, or am I risking open connections?
It closes, unless it is a persistent connection. There are complications in using persistent connections in multiprocess servers like Apache, so stick to non-persistent. If the database is on the same host as the web server, better connect through a socket, not through TCP.

> Q. When I make a select request to the database and I get no data back, what is the best method of handling this?
This depends on your application logic, it's not a matter of database access. Having selects with 0 rows is a perfectly valid db operation.

> Its just I have become somewhat confused as the best methods to do things and lately my MySql server is dying from my crass db code. I need some STANDARDS guidance once and for all :)

If it's dying from your DB code, the fault is maybe in your SQL and database design. Check the slow queries log. Profile your database queries. Check for cartesian joins (SELECT * FROM table1, table2 WHERE ...)
Post Reply