PHP echo query result in Class??

Question

Hi all I have a question about PHP Class. I am trying to get the result from Mysql via PHP. I would like to know if the best practice is to display the result inside the Class or store the result and handle it in html.

For example, display result inside the Class

class Schedule {
           public $currentWeek;

            function teamQuery($currentWeek){

            $this->currentWeek=$currentWeek;


            }
            function getSchedule(){
                $connection = mysql_connect(DB_SERVER,DB_USER,DB_PASS);
                    if (!$connection) {
                        die("Database connection failed: " . mysql_error());
                    }

                    $db_select = mysql_select_db(DB_NAME,$connection);
                    if (!$db_select) {
                        die("Database selection failed: " . mysql_error());
                    }

                  $scheduleQuery=mysql_query("SELECT guest, home, time, winner, pickEnable FROM $this->currentWeek ORDER BY time", $connection);

                    if (!$scheduleQuery){
                        die("database has errors: ".mysql_error());
                          }


                    while($row=mysql_fetch_array($scheduleQuery, MYSQL_NUMS)){
                    //display the result..ex: echo $row['winner'];

                    }     
                    mysql_close($scheduleQuery); 

                    //no returns
                    }


        }

Or return the query result as a variable and handle in php

class Schedule {
           public $currentWeek;

            function teamQuery($currentWeek){

            $this->currentWeek=$currentWeek;


            }
            function getSchedule(){
                $connection = mysql_connect(DB_SERVER,DB_USER,DB_PASS);
                    if (!$connection) {
                        die("Database connection failed: " . mysql_error());
                    }

                    $db_select = mysql_select_db(DB_NAME,$connection);
                    if (!$db_select) {
                        die("Database selection failed: " . mysql_error());
                    }

                  $scheduleQuery=mysql_query("SELECT guest, home, time, winner, pickEnable FROM $this->currentWeek ORDER BY time", $connection);

                    if (!$scheduleQuery){
                        die("database has errors: ".mysql_error());
                    // create an array    }
                    $ret = array();   

                    while($row=mysql_fetch_array($scheduleQuery, MYSQL_NUMS)){

                    $ret[]=$row;
                    }     
                    mysql_close($scheduleQuery); 

                    return $ret;  // and handle the return value in php
                    }


            }

Two things here:

1. I found that returned variable in php is a little bit complex to play with since it is two dimension array. I am not sure what the best practice is and would like to ask you experts opinions.

2. Every time I create a new method, I have to recreate the $connection variable: see below

   $connection = mysql_connect(DB_SERVER,DB_USER,DB_PASS); if (!$connection) { die("Database connection failed: " . mysql_error()); }

                       $db_select = mysql_select_db(DB_NAME,$connection);
                       if (!$db_select) {
                           die("Database selection failed: " . mysql_error());
                       }
   

It seems like redundant to me. Can I only do it once instead of calling it anytime I need a query? I am new to php class. hope you guys can help me. thanks.

Answer 1

I treat classes like these as 'accessors' so they purely query the database and return the result. That way any PHP code which calls it can do whatever it likes with it. this may be displaying or it may be a check or it may be an update. This is good design as it separates the datastore from the logic from the display and means your code will be more flexible. But yes, it is a little more complex.

In regards to re-creating the connection each time. This may or may not be necessary. Depending on your setup you may be able to create a connection pool. To make it easier for you for now, you can abstract the creation of a connection to its own method. This way you only need to call this method at the start to get a connection handle. This saves you from having many copies of the same code all over the place.

If you are new to PHP classes I suggest doing a bit of research on object oriented design. This will give you an idea on why it would be beneficial to abstract some functions, and also why you would want to return the results instead of displaying them.

Answer 2

Its probably a bad idea to echo the result int he class nstead you should return the result or result set for echoing else where.

May the connection a mebmer of the class like:

protected $_connection = null;

Then in your constructor you can assign the database connection. Though normally your db connection would be wrapped by yet another class.

Additionally if i were you i would not use the mysql functions. Instead use the Mysqli or PDO_Mysql drivers. They encapsulate all this functionality in an object orientend manner by default. You can then extend those classes with your custom functionality instead of working from scratch.