MySQL table --> array

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

Post Reply
SteveA
Forum Newbie
Posts: 5
Joined: Sat Dec 18, 2010 9:08 pm

MySQL table --> array

Post by SteveA »

All,

I use this class to read all of a database table into an array. I found this VERY useful because it allows me to work in memory instead of through a window into another site (my MySQL server). I think this is much faster and easier to use. I'm sure I would modify this if the tables are large but for moderately sized tables this, I believe, is much faster.

Have your way with it! And thanks.

Code: Select all

class database
{
  private $db = null;
//--- __construct --------------------------------------------------------------------------------//
  public function __construct()
  {
    global $namecode;
    try
    {
      $this->db = new mysqli( $namecode['MoyaDotPro']['hostspec'],
                              $namecode['MoyaDotPro']['username'],
                              $namecode['MoyaDotPro']['password'],
                              $namecode['MoyaDotPro']['database'] );
      if( $this->db->connect_error ) throw new Exception( "EFM: {$this->db->connect_error}" );
    }
//------------------------------------------------------------------------------------------------//
    catch( Exception $ex )
    {
      echo "<br>\n<b>Error Message:</b> " . $ex->getMessage() . "<br>\n" .
        '<b>In File:</b> ' . $ex->getFile() . "<br>\n" .
        '<b>On Line:</b> ' .$ex->getLine() . "<br>\n";
      exit;
    }
  }
//---- end ---------------------------------------------------------------------------------------//
//--- __destruct ---------------------------------------------------------------------------------//
  public function __destruct()
  {
    if( $this->db ) { $this->db->close(); }
    $this->db = null;
  }
//---- end ---------------------------------------------------------------------------------------//
//--- Get Array ----------------------------------------------------------------------------------//
  public function GetArray( $tnam )             // $tnam = table name
  /*
   * GetArray( $table-name )
   * reads the table and makes an array ( key => array ) where the key is the array entry id value
   * returns the new array
   */
  {
    $query = "SELECT * FROM {$tnam}";
    try
    {
    $qrslt = $this->db->query( $query );
    $nrows = $this->db->affected_rows;
    switch( $nrows )
    {
    default: $r = '';
    break;
    case -1: $r = ' '.$this->db->error;
    break;
    case 0: $r = ' Empty Table';
    break;
    }
    if( !$qrslt || $r ) throw new Exception( "\n<br><b>EFM: query</b><br>" .
      "\n\"{$query}\"<br>\n<b>failed</b><br>\n<b>MySQL said:</b>{$r}" );
//------------------------------------------------------------------------------------------------//
    $aray = array();
    for( $i = 0; $i < $nrows; $i++ )
    {
      $ntre = $qrslt->fetch_assoc();
      $n = $ntre['id'];
      $aray[$n] = $ntre;                        // id based key
    }
    $qrslt->close();
    return $aray;
    }
//------------------------------------------------------------------------------------------------//
    catch( Exception $ex )
    {
      echo "<br>\n<b>Error Message:</b> " . $ex->getMessage() . "<br>\n" .
        '<b>In File:</b> ' . $ex->getFile() . "<br>\n" .
        '<b>On Line:</b> ' .$ex->getLine() . "<br>\n";
      exit;
    }
  }
//---- end ---------------------------------------------------------------------------------------//
} 
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: MySQL table --> array

Post by pickle »

Depending on your query, this could be quite inefficient actually.

MySQL is designed to be queried - to handle conditions and return a combination and set of data. To duplicate that in PHP you'll have to do all the condition checking manually - something that isn't anywhere close to as efficient as MySQL.

Say you've got a relatively small table of 10,000 user rows & you want to get just one user. That's very easily and efficiently done with a MySQL query. With this class, you'll have to retrieve the entire table (which will take longer than a query for a single user), then iterate through that array with PHP. Horribly inefficient.

About the only place this might be useful is if you're outputing the entire contents of a table. Even then though, you have to iterate through each row twice - once to put it in your array and once to display it on the screen.

As far as the code goes, there are some things you could improve:
  • The constructor should accept the database credentials/properties as parameters. As it stands, this class isn't useful for anyone but yourself, or anyone who is willing to make a global variable with the same structure as your $namecode.
  • Don't output the error message in this class. Simply generate the error - maybe as an exception. Definitely as an exception since you can't return anything from a constructor.
  • Usually function names are named with "camel case" - the first word all lowercase, and each word capitalized after that, ie: getArray(). There's nothing technically wrong with "GetArray", but since this is posted in code critique I'm mentioning everything I notice.
  • You should be doing some logic to ensure $tnam is valid. What if somehow $tnam's value was: `users`;DELETE FROM `users;. I realize this is a class you're likely using internally, but you should always ensure the variables you're putting in a query are not going to break stuff.
  • Your function assumes there will always be a column called "id". That's a dangerous assumption. I'm not completely sure what a good alternative is though. I'd guess do a query to find the primary key, then make that value the index.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply