PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Mon Oct 22, 2018 8:25 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 2 posts ] 
Author Message
 Post subject: MySQL table --> array
PostPosted: Wed Dec 22, 2010 5:01 pm 
Offline
Forum Newbie

Joined: Sat Dec 18, 2010 10:08 pm
Posts: 5
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.
Syntax: [ Download ] [ Hide ]
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 ---------------------------------------------------------------------------------------//
}


Top
 Profile  
 
PostPosted: Thu Dec 23, 2010 11:33 am 
Offline
Briney Mod
User avatar

Joined: Mon Jan 19, 2004 7:11 pm
Posts: 6445
Location: 53.01N x 112.48W
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.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group