simplifying repeating database connections in a class?

[news]

Well, I wrote my first PHP class today. Yeah!
But to get it to work, in each function within the class I have to
repeat the database connection lines, and that just seems redundant;
there has to be a better way that I'm just not bright enough to think
of.

Any suggestions?
(It's the first 3 lines of each of the two functions below. When I have
it fully written, there will be about 10 similar functions, each
repeating those three lines.)

Thanks for any feedback!
Liam

class e_test {

function gettech() {
include('../Connections/userDBconnect.p hp');
$db = mysql_select_db ("printing", $connection) or $error .= "Could
not select database1: " . mysql_error()." <p />";
$dbconn = $connection;
// CREATE TECH ARRAY
$sql_T = "SELECT email FROM tbl_users WHERE dept = '1' AND
active='1'";
$result_T = @mysql_query($s ql_T, $dbconn) or $error .= "Could not
query1: " . mysql_error()." <p />";
$count_T = mysql_num_rows( $result_T) or $error .= "Could not select
query2: " . mysql_error()." <p />";
$i_T = 0;
while ($row_T = mysql_fetch_arr ay($result_T)) {
$email_T = $row_T[email];
$techs .= $email_T;
$i_T++;
if ($i_T < $count_T) {
$techs .= ",";
}
}
echo $error;
return $techs;
}

function getdez() {
include('../Connections/userDBconnect.p hp');
$db = mysql_select_db ("printing", $connection) or $error .= "Could
not select database2: " . mysql_error()." <p />";
$dbconn = $connection;
// CREATE DESIGNER ARRAY
$sql_D = "SELECT email FROM tbl_users WHERE dept = '3' AND
active='1'";
$result_D = @mysql_query($s ql_D, $dbconn) or $error .= "Could not
query3: " . mysql_error()." <p />";
$count_D = mysql_num_rows( $result_D) or $error .= "Could not query4:
" . mysql_error()." <p />";
$i_D = 0;
while ($row_D = mysql_fetch_arr ay($result_D)) {
$email_D = $row_D[email];
$designers .= $email_D;
$i_D++;
if ($i_D < $count_D) {
$designers .= ",";
}
}
echo $error;
return $designers;
}

function getall() {
$this->group = $techs.",".$des igners;
$thelist = $this->gettech().",". $this->getdez();
return $thelist;
}

}

$maillist = new e_test();
echo $maillist->gettech();
echo "<p />";
echo $maillist->getdez();
echo "<p />";
echo $maillist->getall();

[Jerry Stuckle]

ne**@celticbear .com wrote:

Well, I wrote my first PHP class today. Yeah!
But to get it to work, in each function within the class I have to
repeat the database connection lines, and that just seems redundant;
there has to be a better way that I'm just not bright enough to think
of.

Any suggestions?
(It's the first 3 lines of each of the two functions below. When I have
it fully written, there will be about 10 similar functions, each
repeating those three lines.)

Thanks for any feedback!
Liam

class e_test {

function gettech() {
include('../Connections/userDBconnect.p hp');
$db = mysql_select_db ("printing", $connection) or $error .= "Could
not select database1: " . mysql_error()." <p />";
$dbconn = $connection;
// CREATE TECH ARRAY
$sql_T = "SELECT email FROM tbl_users WHERE dept = '1' AND
active='1'";
$result_T = @mysql_query($s ql_T, $dbconn) or $error .= "Could not
query1: " . mysql_error()." <p />";
$count_T = mysql_num_rows( $result_T) or $error .= "Could not select
query2: " . mysql_error()." <p />";
$i_T = 0;
while ($row_T = mysql_fetch_arr ay($result_T)) {
$email_T = $row_T[email];
$techs .= $email_T;
$i_T++;
if ($i_T < $count_T) {
$techs .= ",";
}
}
echo $error;
return $techs;
}

function getdez() {
include('../Connections/userDBconnect.p hp');
$db = mysql_select_db ("printing", $connection) or $error .= "Could
not select database2: " . mysql_error()." <p />";
$dbconn = $connection;
// CREATE DESIGNER ARRAY
$sql_D = "SELECT email FROM tbl_users WHERE dept = '3' AND
active='1'";
$result_D = @mysql_query($s ql_D, $dbconn) or $error .= "Could not
query3: " . mysql_error()." <p />";
$count_D = mysql_num_rows( $result_D) or $error .= "Could not query4:
" . mysql_error()." <p />";
$i_D = 0;
while ($row_D = mysql_fetch_arr ay($result_D)) {
$email_D = $row_D[email];
$designers .= $email_D;
$i_D++;
if ($i_D < $count_D) {
$designers .= ",";
}
}
echo $error;
return $designers;
}

function getall() {
$this->group = $techs.",".$des igners;
$thelist = $this->gettech().",". $this->getdez();
return $thelist;
}

}

$maillist = new e_test();
echo $maillist->gettech();
echo "<p />";
echo $maillist->getdez();
echo "<p />";
echo $maillist->getall();

Since you're using a class (hooray!), you could open the connection in
the constructor to your class and store it in a member variable of the
class.

Another way would be to open the connection in the main part of your
program. In this method, you could either store the connection variable
in a member variable of your class (preferred) or pass it as a parameter
to your functions.

BTW - you have:

include('../Connections/userDBconnect.p hp');

twice. I suspect this is just basically a mysql_connect() call. Rather
than include the code this way, you should place it in a function and
include the file once (generally at the top of your script). Then you
can call the function as necessary. They way you're doing it requires
the server to fetch and parse the file twice - unnecessary overhead.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===

[Carl]

ne**@celticbear .com wrote:

Well, I wrote my first PHP class today. Yeah!
But to get it to work, in each function within the class I have to
repeat the database connection lines, and that just seems redundant;
there has to be a better way that I'm just not bright enough to think
of.

Any suggestions?
(It's the first 3 lines of each of the two functions below. When I have
it fully written, there will be about 10 similar functions, each
repeating those three lines.)

Thanks for any feedback!
Liam

class e_test {

function gettech() {
include('../Connections/userDBconnect.p hp');
$db = mysql_select_db ("printing", $connection) or $error .= "Could
not select database1: " . mysql_error()." <p />";
$dbconn = $connection;
// CREATE TECH ARRAY
$sql_T = "SELECT email FROM tbl_users WHERE dept = '1' AND
active='1'";
$result_T = @mysql_query($s ql_T, $dbconn) or $error .= "Could not
query1: " . mysql_error()." <p />";
$count_T = mysql_num_rows( $result_T) or $error .= "Could not select
query2: " . mysql_error()." <p />";
$i_T = 0;
while ($row_T = mysql_fetch_arr ay($result_T)) {
$email_T = $row_T[email];
$techs .= $email_T;
$i_T++;
if ($i_T < $count_T) {
$techs .= ",";
}
}
echo $error;
return $techs;
}

function getdez() {
include('../Connections/userDBconnect.p hp');
$db = mysql_select_db ("printing", $connection) or $error .= "Could
not select database2: " . mysql_error()." <p />";
$dbconn = $connection;
// CREATE DESIGNER ARRAY
$sql_D = "SELECT email FROM tbl_users WHERE dept = '3' AND
active='1'";
$result_D = @mysql_query($s ql_D, $dbconn) or $error .= "Could not
query3: " . mysql_error()." <p />";
$count_D = mysql_num_rows( $result_D) or $error .= "Could not query4:
" . mysql_error()." <p />";
$i_D = 0;
while ($row_D = mysql_fetch_arr ay($result_D)) {
$email_D = $row_D[email];
$designers .= $email_D;
$i_D++;
if ($i_D < $count_D) {
$designers .= ",";
}
}
echo $error;
return $designers;
}

function getall() {
$this->group = $techs.",".$des igners;
$thelist = $this->gettech().",". $this->getdez();
return $thelist;
}

}

$maillist = new e_test();
echo $maillist->gettech();
echo "<p />";
echo $maillist->getdez();
echo "<p />";
echo $maillist->getall();

My suggestions follow.

- create another method that does all the hard work of selecting the
email addresses with one parm which determines the department id you
want to select, or blank for all dept's. (semi-pseudo code, will not
compile)

getEmailAddress es($departmentI d='DEPT_ALL') {
$sql = "SELECT email FROM tbl_users WHERE active='1'";
if ($departmentId != 'DEPT_ALL') {
$sql .= ' AND dept = ' . $departmentId ;
}

$addresses = array();

$result = runQuery($sql)
while ( $result->hasMoreRows( ) ){
$addresses[] = /* get e-addr from $result */
}

return $addresses;
}

-- from within your gettech() and getDez() methods:
function getTech(){
return getEmailAddress es('1');
}

Note however that although you did indeed create a class, this class
functions more like a collection of functions than an object. I find
that a good place to start when designing classes/objects is to assign
it an object name, something that describes what it is in basic terms.
Using this name you can then get an idea of the behavior (methods)
expected of this object and the attributes (variable) that the object
must have.

You can also clean up the code quite a bit by moving the database
specific code into the userDBconnect code. Ideally, this class should
know as little about the database implementation as possible..

Hope this helps.
Carl.

[news]

Jerry Stuckle wrote:

ne**@celticbear .com wrote:

Well, I wrote my first PHP class today. Yeah!
But to get it to work, in each function within the class I have to
repeat the database connection lines, and that just seems redundant;
there has to be a better way that I'm just not bright enough to think
of.

Any suggestions?
(It's the first 3 lines of each of the two functions below. When I have
it fully written, there will be about 10 similar functions, each
repeating those three lines.)

Thanks for any feedback!
Liam
Since you're using a class (hooray!), you could open the connection in
the constructor to your class and store it in a member variable of the
class.

I tried that, evidently incorrectly, because each time the functions
within the class would run, they couldn't "see" that established
connection and the queries wouldn't work.
I tried assigning those three lines to fariables...but that so doesn't
work. Another person mentioned making it into a function...whic h sounds
like a great idea! I just need to figure it out.
Another way would be to open the connection in the main part of your
program. In this method, you could either store the connection variable
in a member variable of your class (preferred) or pass it as a parameter
to your functions.
I do that for all my PHP pages, have those three database est. lines at
the top. But, and I guess I'm just inexperienced enough to not know how
a SQL querey inside a function can "see" the database connection
outside it, because every time I try, I get SQL errors.
That's why I'm calling the include twice, once in each function,
because I can't seem to make it fisible to the inside of the function
otherwise.

BTW - you have:

include('../Connections/userDBconnect.p hp');

twice. I suspect this is just basically a mysql_connect() call. Rather
than include the code this way, you should place it in a function and
include the file once (generally at the top of your script). Then you
can call the function as necessary. They way you're doing it requires
the server to fetch and parse the file twice - unnecessary overhead.

So, how would that work?
Something like:

function db_connect() {
include('../Connections/userDBconnect.p hp');
$db = mysql_select_db ("printing", $connection) or $error .=
mysql_error();
$dbconn = $connection;
}
function test_sql() {
$sql_Fi = "SELECT email FROM tbl_users WHERE dept = '5' AND
active='1'";
$result_Fi = @mysql_query($s ql_Fi, db_connect());
}

No, I know that won't work. OH! Like:

include('../Connections/userDBconnect.p hp');
$db = mysql_select_db ("printing", $connection) or $error .=
mysql_error();
$dbconn = $connection;

function db_connect($sql ,$dbconn) {
return @mysql_query($s ql, $dbconn);
}
function test_sql() {
$sql_Fi = "SELECT email FROM tbl_users WHERE dept = '5' AND
active='1'";
$result = db_connect($sql _Fi,$dbconn);
while ($row = mysql_fetch_arr ay($result)) {
... etc
}
}

I'll try that... but, $dbconn, to be used in test_sql() to be sent to
db_connect, needs to be sent TO test_sql()....
OK, brain melting.
If you have any good web sites that help in this manner, I'd appreciate
it. =)
Thanks for the reply!
Liam

[news]

Carl wrote:

ne**@celticbear .com wrote:

Well, I wrote my first PHP class today. Yeah!
But to get it to work, in each function within the class I have to
repeat the database connection lines, and that just seems redundant;
there has to be a better way that I'm just not bright enough to think
of.

Any suggestions?

My suggestions follow.
Note however that although you did indeed create a class, this class
functions more like a collection of functions than an object. I find
that a good place to start when designing classes/objects is to assign
it an object name, something that describes what it is in basic terms.
Using this name you can then get an idea of the behavior (methods)
expected of this object and the attributes (variable) that the object
must have.

You can also clean up the code quite a bit by moving the database
specific code into the userDBconnect code. Ideally, this class should
know as little about the database implementation as possible..

Holy mother of God! The power!
Inspired by your example, I turned that collection of functions (which
ended up being several of nearly the same function,) into this:

include('../Connections/userDBconnect.p hp');
$db = mysql_select_db ("printing", $connection) or $error .= "Could not
select database1: " . mysql_error()." <p />";
$dbconn = $connection;

class class_pa_email {

function mail_sql($sql_n ="0") {
$sql = "SELECT email FROM tbl_users WHERE active='1'";
if ($sql_n != "0") {
$sql .= " AND dept = '$sql_n'";
}
$result = @mysql_query($s ql) or $error .= "Could not query1: " .
mysql_error()." <p />";
$count = mysql_num_rows( $result) or $error .= "Could not select
query2: " . mysql_error()." <p />";
$result_array = array($result,$ count);
return $result_array;
}

function get_pa_mail($de pt) {
$result_array = $this->mail_sql($dept );
list($result_T, $count_T) = $result_array;
$i_T = 0;
while ($row_T = mysql_fetch_arr ay($result_T)) {
$email_T = $row_T[email];
$techs .= $email_T;
$i_T++;
if ($i_T < $count_T) {
$techs .= ",";
}
}
echo $error;
return $techs;
}
}
$maillist = new class_pa_email( );

echo $maillist->get_pa_mail('1 '); // echos just dept 1
echo "<p />";
echo $maillist->get_pa_mail('2 '); // echos just dept 2
echo "<p />";
echo $maillist->get_pa_mail('0 '); // echos all departments

I had no idea that in the mysql_query function you only needed to give
it the variable containing the querey string; I learned it so you had
to provide it with both the querey and the connection variable. Your
way makes SQL queries inside functions so much easier!

OK, so now about making the class not "know" about the database...are
you meaning, take the SQL function outside of the class and putting the
function in the other PHP file that's included, and then call to it
from the function in the class?

And what do you mean exactly by the object name thing? Are you refering
to something different than what I finally did above?
If you of anygood Web sites you'd recommended for beginning PHP
objects, I'd be appreciative. =)

Thanks for the reply!
Liam

[Jerry Stuckle]

ne**@celticbear .com wrote:

Jerry Stuckle wrote:

ne**@celticbe ar.com wrote:

Since you're using a class (hooray!), you could open the connection in
the constructor to your class and store it in a member variable of the
class.

I tried that, evidently incorrectly, because each time the functions
within the class would run, they couldn't "see" that established
connection and the queries wouldn't work.
I tried assigning those three lines to fariables...but that so doesn't
work. Another person mentioned making it into a function...whic h sounds
like a great idea! I just need to figure it out.

It's pretty simple - all you need to do is return the connection
variable from the function.

PHP Functions are generally their own little blocks of code. That is,
if you define $dbconn in one function, no one outside of that function
can see it (unless you make it global - which is another story). This
is good, because it means you don't have to worry about two functions
having two different variables with the same name.

You could do something like this:

function connect() {
$connection = mysql_connect(. .....);
$db = mysql_select_db (....);
return $connection;
}

To call:

$myconn = connect();

Of course, you will want to add error handling, you might want to pass a
variable (the db name) to the function, or whatever. But hopefully it
will get you on the right track.

I do that for all my PHP pages, have those three database est. lines at
the top. But, and I guess I'm just inexperienced enough to not know how
a SQL querey inside a function can "see" the database connection
outside it, because every time I try, I get SQL errors.
That's why I'm calling the include twice, once in each function,
because I can't seem to make it fisible to the inside of the function
otherwise.

You can also pass the connection as a parameter to a function.

You need to read up on functions more. They are at the heart of the PHP
language. Basically, you can pass any number of parameters to a
function, and use them inside of that function. You can return one
parameter from the function and use it outside of the function.

(Actually, this isn't *quite* correct - you can use references when
passing parameters to the function and effectively get more than one
value returned. But for now don't worry about that end - just log it in
the back of your mind and come back to it later).


So, how would that work?
Something like:

function db_connect() {
include('../Connections/userDBconnect.p hp');
$db = mysql_select_db ("printing", $connection) or $error .=
mysql_error();
$dbconn = $connection;
}
function test_sql() {
$sql_Fi = "SELECT email FROM tbl_users WHERE dept = '5' AND
active='1'";
$result_Fi = @mysql_query($s ql_Fi, db_connect());
}

No, I know that won't work. OH! Like:

include('../Connections/userDBconnect.p hp');
$db = mysql_select_db ("printing", $connection) or $error .=
mysql_error();
$dbconn = $connection;

function db_connect($sql ,$dbconn) {
return @mysql_query($s ql, $dbconn);
}
function test_sql() {
$sql_Fi = "SELECT email FROM tbl_users WHERE dept = '5' AND
active='1'";
$result = db_connect($sql _Fi,$dbconn);
while ($row = mysql_fetch_arr ay($result)) {
... etc
}
}

Not quite. Something like:

(See the connect() function above)

function test_sql($conn) {
$sql_Fi = "....";
$result = mysql_query($sq l_Fi, $conn);
while ($row = mysql_fetch_arr ay($result)) {
...
}

$dbconn = connect();
test_sql($dbcon n);
Remember - code in a function is not executed until you call the function.

Here, connect() opens the connection and returns the result
($connection) to the caller. test_sql() takes one parameter which will
be the connection. It makes the call to mysql and does whatever.

After the functions themselves is the main code. The first line calls
connect() to make the connection and stores the result in $dbconn. The
main code then passes $dbconn to test_sql, which uses the value to
perform the mysql query.

Now - in connect(), this value is known as $connection. In the main
code it is $dbconn, and in test_sql it's $conn. But they are all three
the same thing - just under different names. I normally don't do it
this way (I use some standard naming conventions I've developed for my
code) - but here I did it to show you that names between functions and
your main code are *not* important. They're just a way of referring to
the value *in that part of the code*. And in connect(), $connection has
no relationship to *any* other variable of the same name *anywhere* else
in the code. The only relationship is the one created by returning
$connection from the function and the statement:

$dbconn = connect();
I'll try that... but, $dbconn, to be used in test_sql() to be sent to
db_connect, needs to be sent TO test_sql()....
OK, brain melting.
If you have any good web sites that help in this manner, I'd appreciate
it. =)
Thanks for the reply!
Liam

It's kinda complicated until you get the hang of it. Just think of a
function as being closed box with a little guy and an adding machine
inside. You can push a slip with a bunch of numbers into the box. When
the little guy is done, he can pass back out a slip with a single number
(the total) back out to you. But you can't have any interaction with
him other than that slip of paper, and vice versa.

This is how a function works. You can pass any number of values
(parameters) into the function, which the function can then use. And
the function can return exactly one value back for you to use.

Sorry, I don't know of any good websites - haven't looked for one for a
while. But you should be able to find something by googling for PHP
tutorial.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===