MySQL Get Fields Function

Small, short code snippets that other people may find useful. Do you have a good regex that you would like to share? Share it! Even better, the code can be commented on, and improved.

Moderator: General Moderators

Post Reply
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

MySQL Get Fields Function

Post by infolock »

Code: Select all

/**
   * Selects Fields from a database/table and returns the result with all rows or first row.  Also supports INNER JOINS, GROUP BY, ORDER BY.
   *
   * @Author : Jonathon Hibbard
   *
   * @param string $db_name         //Name of Database
   * @param string $table           //Name of Table
   * @param array $fields           //Field Names we want to select
   * @param array $inner            //Fields to be INNER JOINED
   * @param array $where            //WHERE fields.
   * @param string $group_by        //Field to group by.
   * @param string $order_by        //Field to order by
   * @param string $asc             //Asc or Desc order
   * @param string $return_array    //Decides if $row_result will be an array or single row from the result
   * @return $row_result            //Returns the result
   *
   * @example : (1) getFields('tool','master','array('master.master_id','master.id'));
   *            (2) getFields('tool','master','array('master.master_id','master.id','name.name'),array(0=>array('tool','name','master','id',)),'master.value <> "", false);
   *            (3) getFields('tool','master','master_id',false,false,false,false,false,true);
   */
  function getFields($db_name = NULL, $table = NULL, $fields =  NULL, $inner = NULL, $where = NULL, $group_by = NULL, $order_by = NULL, $asc = NULL, $return_array = true) {
    $C_NAME = __CLASS__."::".__FUNCTION__;
    $sql = "SELECT ";
    is_array($fields) ? $sql .= implode(',',$fields) : $sql .= $fields;
    $sql .= " FROM ".$db_name.".".$table;
    if(!empty($inner)) {
      $total_inners = count($inner);
      for($i=0; $i<$total_inners; $i++)
        $sql .= " INNER JOIN ".$inner[$i][0].".".$inner[$i][1]." ON ".$inner[$i][1].".".$inner[$i][2]." = ".$inner[$i][3].".".$inner[$i][2];
    }
    if(!empty($where)) {
      is_array($where) ? $sql .= " WHERE ".implode(' AND ',$where) : die("Fatal Error : Variable must be an array.  Variable type is : ".gettype($where));
    }
    !empty($group_by) ? $sql .= " GROUP BY ".$group_by : '';
    !empty($order_by) ? $sql .= " ORDER BY ".$order_by : '';
    !empty($asc)      ? $sql .= " ".$asc : '';
    $sql .= " # Query resides in ".__FILE__."->".$C_NAME." on line ".__LINE__;
    if($return_array === true) {
      $rst = mysql_query($sql) or die('Query Failed!  Query was:<br /> '.$sql.'<br />Error in '.$C_NAME.' on LINE '.__LINE__);
      $i=0;
      while($row = mysql_fetch_assoc($rst)) {
        $row_result[] = $row[$fields[$i]];
        $i++;
      }
    } else {
      $rst = mysql_query($sql) or die('Query Failed!  Query was:<br /> '.$sql.'<br />Error in '.$C_NAME.' on LINE '.__LINE__);
      $row_result = mysql_fetch_assoc($rst);
    }
      return $row_result;
  }
Last edited by infolock on Wed Sep 21, 2005 1:18 pm, edited 3 times in total.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Should the line

Code: Select all

return $rrow_result
be

Code: Select all

return $row_result;
? :)
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

yep. this has been fixed.
Post Reply