Computers And Industry

PHP MySql Statement Generator


The following example shows how to use the class to do an insert into a mysql database. The table called names has the fields first, birthdate, and employeeid number fields. It also should have a field called RECID that is an autoincrment field.

Note the Question marks (?) in the sql properties. When using this class, updates and insert statements correspond to a format such that the class can replace the markers accordingly. It may be a bit cryptic at first but one gets used to it pretty quickly.

Using the Class


host = "your.database.hostname";
$cnObj->db = "DatabaseName";
$cnObj->user = "UserName";
$cnObj->password = "UserPassword";
if ($cnObj->Open())
{


   // insert the new record
   $stmt = new pfsSqlStatement();
   $stmt->cn = $cnObj->cn;
   $stmt->sql = "INSERT INTO names (?) VALUES (?);
   $stmt->setstring("first", "Bob");
   $stmt->setstring("last", "Smith");
   $stmt->setdatetime("birthdate", mktime(14, 31, 29, 7, 25, 1973)); 
   $stmt->setnumeric("employeeid", 10010);
   $stmt->execute();


  // now update the record
   $recID = $cnObj->GetLastInsertedID();

   $stmt2 = new pfsSqlStatement();
   $stmt2->cn = $cnObj->cn;
   $stmt2->sql = "UPDATE names SET ? WHERE RECID = " . strval($recID);
   $stmt2->setstring("first", "Robert");
   $stmt2->setdatetime("birthdate", mktime(14, 31, 29, 7, 31, 1972));
   $stmt2->execute();


    // now delete the record
    $stmt3 = new pfsSqlStatement();
    $stmt3->cn = $cnObj->cn;
    $stmt3->sql = "DELETE FROM names WHERE RECID = " . strval($recID);
    $stmt3->execute();


   $cnObj->Close();
}


?>



The pfsSqlStatement class

This is the pfsSqlStatement class. Note that it is dependent upon the pfsConnection class which was posted previously.

template = "";
    unset($this->fldvals);
    $this->fldvals = array();
  }


  // --------------------------------------------
  // sets the value of a field and if necessary
  // adds it to the fldvals collection
  // --------------------------------------------
  function setfield($fld, $sval)
  {

    $found = 0;
    $fldcount = count($this->fldvals);
    $ufld = strtoupper($fld);

    for ($i = 0; $i < $fldcount; $i++)
    {
      $currentfieldname = strtoupper($this->fldvals[$i]->fieldname);
      if (strcmp($ufld,$currentfieldname) == 0) {
        $this->fldvals[$i]->fieldvalue = $sval;
        $found = 1;
      }
    }

    if ($found == 0) {
      $this->fldvals[$fldcount] = new sqlfield();
      $this->fldvals[$fldcount]->fieldname = $fld;
      $this->fldvals[$fldcount]->fieldvalue = $sval;
    }
  }


  // ----------------------------------------------
  // sets a numeric value
  // ----------------------------------------------
  function setnumeric($fld, $n)
  {
    if (!is_numeric($n)) {
      $n1 = "0";
    }
    else {
     $n1 = strval($n);
    }
    $this->setfield($fld, $n1);
  }



  // -----------------------------------------------
  // sets a string value, adds surrounding tick marks
  // and escapes the value
  // ------------------------------------------------
  function setstring($fld, $s)
  {
    $s1 = "'" . addslashes($s) . "'";
    $this->setfield($fld, $s1);
  }


  // ------------------------------------------------
  // sets a date from a unix timestamp (only date)
  // ------------------------------------------------
  function setdate($fld, $timestamp)
  {
     $s1 = date($this->MYSQL_DATE,$timestamp);
     $s1 = "'" . $s1 . "'";
     $this->setfield($fld, $s1);
  }


  // ------------------------------------------------
  // sets a date time from a unix timestamp
  // ------------------------------------------------
  function setdatetime($fld, $timestamp)
  {
     $s1 = date($this->MYSQL_DATETIME,$timestamp);
     $s1 = "'" . $s1 . "'";
     $this->setfield($fld, $s1);
  }


  // -------------------------------------------------
  // gets the mode
  // -------------------------------------------------
  function mode()
  {

     $mtype = $this->MODE_OTHER;
     $keyword_insert = $this->KEYWORD_INSERT;
     $keyword_update = $this->KEYWORD_UPDATE;

     $keyword = strtoupper(substr($this->template,0,6));
     if (strcmp($keyword, $keyword_insert) == 0) {
       $mtype = 1;
     }
     elseif (strcmp($keyword, $keyword_update) == 0) {
       $mtype = 2;
     }
     else {
       $mtype = 0;
     }
     return $mtype;
  }




  // ------------------------------------------------
  // getsql gets the sql statement
  // ------------------------------------------------
  function getsql()
  {

     $statementmode = $this->mode();
     $localsql = $this->template;


     // INSERT STATEMENT
     if ($statementmode == 1) {
       $fldstring = "";
       $fldvalstring = "";
       $fldcount = count($this->fldvals);
       $cmma = "";

       for ($i = 0; $i < $fldcount; $i++) {
         $fldstring = $fldstring . $cmma . $this->fldvals[$i]->fieldname;
         $fldvalstring = $fldvalstring . $cmma . $this->fldvals[$i]->fieldvalue;
         $cmma = ", ";
       }

       // now replace 1st ? mark
       $qpos = strpos($localsql, "?");
       $tmpleft = substr($localsql, 0, $qpos);
       $tmpright = substr($localsql, $qpos + 1);
       $localsql = $tmpleft . $fldstring . $tmpright;

       // now replace 2nd ? mark
       $qpos = strpos($localsql, "?");
       $tmpleft = substr($localsql, 0, $qpos);
       $tmpright = substr($localsql, $qpos + 1);
       $localsql = $tmpleft  . $fldvalstring . $tmpright;

     }

     // UPDATE STATEMENT
     elseif ($statementmode == 2) {
       $fldsetstring = "";
       $fldcount = count($this->fldvals);
       $cmma = "";

       for ($i = 0; $i < $fldcount; $i++) {
         $fldsetstring = $fldsetstring . $cmma . $this->fldvals[$i]->fieldname . 
         " = " . $this->fldvals[$i]->fieldvalue;
         $cmma = ", ";
       }

       // now replace 1st ? mark
       $qpos = strpos($localsql, "?");
       $tmpleft = substr($localsql, 0, $qpos);
       $tmpright = substr($localsql, $qpos + 1);
       $localsql = $tmpleft . $fldsetstring . $tmpright;

     }


     // DEFAULT - SELECT, DELETE, ANYTHING ELSE
     else {
       $localsql = $this->template;
     }

     return $localsql;
  }


  // ------------------------------------------------
  // executes the sql statement
  // requires connection class be set
  // ------------------------------------------------
  function execute()
  {
     $tmp = $this->getsql();

   if ($this->debug == 1)
   {
    echo($tmp);
    echo("

"); } $result = $this->cn->query($tmp); return $result; } // ------------------------------------------------- // does a plain old query // ------------------------------------------------- function query() { $result = $this->cn->query($this->sql); return $result; } } // END SQLSTATEMENT CLASS // ----------------------------------------------------- // SQL Field Class // ----------------------------------------------------- class sqlfield { var $fieldname; var $fieldvalue; } ?>
Open Source

Paul F. Sirpenski
Personal Open Source Directory Of Paul F. Sirpenski

ASP.NET Core
Open Source directory Of the Microsoft Asp.Net Core project.

Developed By Paul F. Sirpenski. Copyright 2021.