Page 1 of 1

MySQL table --> array

Posted: Wed Dec 22, 2010 4:01 pm
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 ---------------------------------------------------------------------------------------//
} 

Re: MySQL table --> array

Posted: Thu Dec 23, 2010 10:33 am
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.