函数参数，返回数据库表中的所有记录

Question

I have a script that loops through and returns all records in the database table, code below.

PHP:

for($i=0;$i<$group_layer_row;$i++){
$my_layer_string="MyMap_".mb_convert_encoding(mssql_result ($rs_group_layer, $i, 0),"UTF-8","SJIS")."_".mb_convert_encoding(mssql_result ($rs_group_layer, $i, 1),"UTF-8","SJIS");
echo "var ".$my_layer_string.";
";
}

What I am trying to do is turn this into an argument. Somewhat like this(this is an example, please don’t judge).

PHP:

function getLayers(){
$my_layer_string="MyMap_".mb_convert_encoding(mssql_result ($rs_group_layer, $i, 0),"UTF-8","SJIS")."_".mb_convert_encoding(mssql_result ($rs_group_layer, $i, 1),"UTF-8","SJIS");
$layers="var ".$my_layer_string.";
";
echo $layers;
}
for($i=0;$i<$group_layer_row;$i++){
getLayers();
}

Any help on this would be very appreciated.

For reference I am including the sql query

$sql= "SELECT * FROM m_group_layer WHERE group_id=\"".$_SESSION["group_id"]."\" ORDER BY display_order";
$rs_group_layer= mssql_query ($sql, $con);
$group_layer_row =mssql_num_rows($rs_group_layer);

EDIT: This is almost the exact same loop just with different output.

for($i=0;$i<$group_layer_row;$i++){
$my_layer_string="MyMap_".mb_convert_encoding(mssql_result ($rs_group_layer, $i, 0),"UTF-8","SJIS")."_".mb_convert_encoding(mssql_result ($rs_group_layer, $i, 1),"UTF-8","SJIS");
echo "".$my_layer_string." = new OpenLayers.Layer.WMS( \"".$my_layer_string."\",\"http://192.0.0.0/cgi-bin/mapserv.exe?map=C:/ms4w/Apache/htdocs/mapserver/data/toyama/toyama_mymap.map&service=WMS&SRS=EPSG:2449&VERSION=1.1.1&format=image/PNG&layers=".$my_layer_string."\", {'layers': '".$my_layer_string."'},  {isBaseLayer: false, visibility: false,opacity:0.5,alpha:true});
map.addLayer(".$my_layer_string.");
";
}

Answer 1

If I understand you correctly, this is a prime candidate for creating an object. As for making a function out of it, I think it's significantly cleaner to process the db resultset in a for loop. I don't see much benefit to creating a function (as passing the db result back and forth to a function inside a loop is very inefficient). Perhaps you might clarify your reasoning for wanting a function or what you are looking to accomplish?

BUT, if you really wanted to make a function out of it it would pretty much look how you outlined it ...

function getLayer($result_set, $row) {
  $str  = "MyMap_" . mb_convert_encoding(mssql_result($result_set, $i, 0),"UTF-8","SJIS")
  $str .= "_".mb_convert_encoding(mssql_result($result_set, $i, 1),"UTF-8","SJIS");
  return "var MyMap_$str;
";
}

// $con = ...
$sql = "SELECT * FROM m_group_layer WHERE group_id=\"".$_SESSION["group_id"]."\" ORDER BY display_order";
$result = mssql_query ($sql, $con);
$row_count = mssql_num_rows($result);

for($i=0; $i<$row_count; $i++){
  echo getLayer($result, $i);
}

UPDATE -- CLASS EXAMPLE

Okay, hopefully this doesn't scare you away. Everyone was afraid of OOP at some point. The important thing is to keep working at it and eventually you'll be like, 'OMG I <3 OOP LIKE GAGA LOVES HER LITTLE MONSTERS!!!'

I tried to document as much as possible. There's only so much you can explain without teaching a semester course :) How to use it is at the bottom of the code.

<?php

/**
 * Retrieves layers from the db and provides methods for outputting
 */
class LayerMaker
{
  /**
   * Our MSSQL database connection
   * @var MSSQL connection resource
   */
  protected $db_conn;

  /**
   * Our array of records from the DB
   * @var array
   */
  protected $records;

  /**
   * Constructor function
   * 
   * Called when you first instantiate the object. If you specify
   * the db_conn, it will go ahead and retrieve the records as
   * soon as the object is created.
   * 
   * @param MSSQL connection resource $db_conn
   * 
   * @return void
   */
  public function __construct($db_conn=NULL)
  {
    if ($db_conn) {
      $this->set_db_conn($db_conn);
      $this->records = $this->query_db();
    }
  }

  /**
   * Setter function for protected $db_conn property
   * 
   * You could just as easily create a method in the object
   * to create the db connection, but for testing reasons that
   * you likely don't care about it's better to inject the
   * db connection into our object using a setter function like this.
   * 
   * @param MSSQL link identifier $db_conn
   */
  public function set_db_conn($db_conn)
  {
    $this->db_conn = $db_conn
  }

  /**
   * How we get the records from the database into our object's $results property
   * 
   * @return MSSQL record set on success or FALSE if no db connection is set
   */
  protected function query_db()
  {
    // make sure we've set a database connection to use
    // query the db and return the results
    $sql = 'SELECT * FROM m_group_layer WHERE group_id="' .
      $_SESSION["group_id"] . '" ORDER BY display_order';
    return mssql_query($sql, $this->db_conn);
  }

  /**
   * A function to get a count of the rows in our result set
   * 
   * @return int Rows in the result property
   */
  public function count_result_rows()
  {
    if ($this->records) {
      return mssql_num_rows($this->records);
    }
    return 0;
  }

  /**
   * Wrapper for mb_convert_encoding function
   * 
   * @return string
   */
  protected function layer_builder($row)
  {
    $str0  = mb_convert_encoding(mssql_result($this->records, $row, 0),"UTF-8","SJIS")
    $str1 = mb_convert_encoding(mssql_result($this->records, $row, 1),"UTF-8","SJIS");

    return "var MyMap_$str0_$str1";
  }

  /**
   * Finally, build our layers!
   * 
   * @param int $row Result set row number
   * 
   * @return mixed Layer string if $row specified or Array of all layer strings
   *               if no specific row requested
   */
  public function get_layers($row=NULL)
  {
    if ($row) {
      // if we want one specific row ...
      return $this->layer_builder($row);
    } else {
      // otherwise, give us back an array of all the rows
      $layers = array();
      for($i=0; $i<$this->count_result_rows(); $i++){
        $layers[] = $this->layer_builder($i
      }
      return $layers;
    }
  }

  /**
   * Getter function for protected $records property
   *
   * Useful because you might want access to the resultset
   * outside of the object context.
   * 
   * @return array MSSQL record set
   */
  public function get_records()
  {
    return $this->records;
  }
}


// Now this is how you could use it
$conn = (however you retrieve a db connection);
$layer_obj = new LayerMaker($conn);
$layers = $layer_obj->get_layers();

print_r($layers);

?>