is there a better way

[Hephaistos]

This is what I need;

• function to create $sql var with an SQL Query that updates a record in my table.

I dont know what fields are going to be changed so I have to dynamically create my Query. This is what I've done;

function editPC ($Type, $PC_model, $SerieNummer, $DHVNummer, 
		   $AankoopDatum, $PC_ID) {
    if (!$PC_ID) {
      return FALSE;
    }
    $sql = "UPDATE pc SET "
    if ($type) {
      $sql .= "Type = '$type' ";
    }
    if ($PC_model) {
      $sql .= "PC_Model = '$PC_Model' ";
    }
    if ($SerieNummer) {
      $sql .= "SerieNummer = '$SerieNummer' ";
    }
    if ($DHVNummer) {
      $sql .= "DHVNummer = '$DHVNummer' ";
    }
    if ($AankoopDatum) {
      $sql .= "AankoopDatum = '$AankoopDatum' ";
    }
    $sql .= "WHERE PC_ID = '$PC_ID'";
    $this->runQuery($sql);
  }

I don't really like this code but it does work. I'm wondering if I can write a method that takes care of generating the mysql query that i can use for all editing functions. For example;

class edit {
    function MagicQuery ($vars) {
        //magic code
    }

function editPC ($var1, $var2) {
    $this->MagicQuery($var1, $var2);
}
}

Any tips or trick are very welcome. If more information is required I'll be happy to elaborate.

[sarahk]

what you have looks fine. The only change you might like to make is

function editPC ($PC_ID, $varList=false) 
{ 
    if (!$varList) { 
      return FALSE; 
    } 
    $first = true;
    $sql = "UPDATE pc SET " ;
    foreach($varList as $k => $v)
    {
      if ($first) $first = false;
      else $sql .= ', ';
      $sql .= $k . " = '{$v}' "; 
    } 
    $sql = substr( $sql, 0, strlen($sql) - 1);

$sql .= "WHERE PC_ID = '{$PC_ID}'"; 
$this->runQuery($sql); 
  }

// and this would be called with
$aPassMe array();
if (isset ( $Type)) $aPassMe['Type'] = $Type;
if (isset ( $PC_model)) $aPassMe['PC_model'] = $PC_model;
if (isset ( $SerieNummer)) $aPassMe['SerieNummer'] = $SerieNummer;
if (isset ( $DHVNummer)) $aPassMe['DHVNummer'] = $DHVNummer;
if (isset ( $AankoopDatum)) $aPassMe['AankoopDatum'] = $AankoopDatum;

$this->editPC ($PC_ID, $aPassMe);

Additionally I would probably make $aPassMe a property of the class and the isset() code would be another function.

[Hephaistos]

You've inspired me to write the following:

class edit {
  /**
   * This class handles all editing functions
   */

  var $table;
  var $ID;
  var $ID_VALUE;
  $editArray array();

  function editRecord() {
    $sqlSelect = "SELECT * FROM ".$this->table." WHERE ".$this->ID." = ".
      $this->ID_VALUE;
    $resultSelect = mysql_query($sqlSelect);
    $fieldArray = mysql_fetch_assoc($resultSelect);

foreach($this->editArray as $k => $v) {
  $fieldArray['$k'] = $v;
}

$sqlEdit = "UPDATE ".$this->table." SET ";
$first = TRUE;
foreach($fieldArray as $key => $value) {
  if ($first) {
$first = FALSE;
  } else {
$sqlEdit .= ",";
  }
  $sqlEdit .=$key." = '".$value."' ";
}
$sqlEdit .= "WHERE ".$this->ID." = ".$this->ID_VALUE;

$result = mysql_query($sqlEdit);

if ($result) {
  $feedback .=" Edited record";
  return TRUE;
} else {
  $feedback .=" Edit failed: ".mysql_error();
  return FALSE; 
  }


}

As you can see this always works as long as you provide valid keys in the $this->editArray array.

anyway thanks again

[Hephaistos]

for future reference:

<?php
class connect 
{
  var $username;
  var $password;
  var $server;
  var $db;

  function connect() 
    {
    $this->username = 'root';
    $this->password = '';
    $this->server = 'localhost';
    $this->db = 'mysql';
    }

  function connectDB() 
    {
      GLOBAL $feedback;
      $connect = mysql_connect($this->server,$this->username,$this->password);
      if (!$connect) 
	{
	  $feedback .= "database connection failed: ".mysql_error();
	  return FALSE;
	}
      $database = mysql_select_db($this->db,$connect);
      if (!$database) 
	{
	  $feedback .= "database selection failed: ".mysql_error();
	  return FALSE;
	}
    }

}

class insert 
{
  /** 
   * This Class handles all database insert functions
   */

  /**
   * Make sure to set these when asigning this Class
   */
  var $table;
  var $insertArray;

  function insertRecord() 
    {
      GLOBAL $feedback;
      $sql = "INSERT INTO ".$this->table;
      $first = TRUE;
      foreach($this->insertArray as $k => $v) {
	if ($first) 
	  {
	    $sql .= " (";
	    $first = FALSE;
	  } 
	else 
	  {
	    $sql .= ",";
	  }
	$sql .= $k;
      }
      $sql .= ") ";

  $first = TRUE;
  foreach($this->insertArray as $k => $v) 
{
  if ($first) 
    {
      $sql .= "VALUES (";
      $first = FALSE;
    } 
  else 
    {
      $sql .= ",";
    }
  $sql .= "'".$v."'";
}
  $sql .= ")";

  echo $sql;

  $result = mysql_query($sql);
  if ($result) 
{
  $feedback .=" Inserted record";
  return TRUE;
} 
  else 
{
  $feedback .=" Insertion failed: ".mysql_error();
  return FALSE;
}
}

}


class edit 
{
  /**
   * This class handles all editing functions
   */

  /**
   * Make sure to set these when you assign the class
   * to a new object
   */
  var $table;
  var $ID;
  var $ID_VALUE;
  var $editArray;


  function editRecord() 
    {
      GLOBAL $feedback;
      $sqlSelect = "SELECT * FROM ".$this->table." WHERE ".$this->ID." = ".
	$this->ID_VALUE;
      $resultSelect = mysql_query($sqlSelect);
      $fieldArray = mysql_fetch_assoc($resultSelect);

  foreach($this->editArray as $k => $v) 
{
  $fieldArray['$k'] = $v;
}

  $sqlEdit = "UPDATE ".$this->table." SET ";
  $first = TRUE;
  foreach($fieldArray as $key => $value) 
{
  if ($first) 
    {
      $first = FALSE;
    } 
  else 
    {
      $sqlEdit .= ",";
    }
  $sqlEdit .=$key." = '".$value."' ";
}
  $sqlEdit .= "WHERE ".$this->ID." = ".$this->ID_VALUE;

  $result = mysql_query($sqlEdit);

  if ($result) 
{
  $feedback .=" Edited record";
  return TRUE;
} 
  else 
{
  $feedback .=" Edit failed: ".mysql_error();
  return FALSE; 
}
}

}

class delete 
{
  /**
   * Deletes records
   */

  var $id;
  var $id_value;
  var $table;

  function deleteRecord() 
    {
      GLOBAL $feedback;
      if (!$id OR !$id_value OR !$table)
	{
	$feedback .= "missing one or more variables";
	return FALSE;
	} 
      else 
	{
	  $sql = "DELETE FROM ".$this->table." WHERE ".$this->id." = '".
	    $this->id_value."'";
	  $result = mysql_query($sql);
	  $feedback .= "succesfully deleted record";
	  return TRUE;
	}
    }
}