Solved

URGENT: looping thru columns and updating/inserting data

Posted on 2004-04-09
6
212 Views
Last Modified: 2013-12-12
Hail all from Folsom, Cali...

I have a small problem that I am *hoping* can be solved...

I have a bit of code that works wonders for me in ASP, and one of my clients has asked that it be changed to PHP... this is plexing me as i can not find any documention on how to do this...

The part that i am finding difficult is this, I dont know what form veriables will be passed in form the form POST, and I dont know what colums will exist in the table. Thus, the code I currently use checks for each column in the database to see if there is an existing form POST variable with a value other then "" <blank> and if there IS a value, then it will be added to the insert od update SQL script that is being built.


So here is the ASP code... perhaps you can make sence of it and tell me if it is possable in PHP/MySQL

I REALLY NEED IT TO BE A FUNCTION THAT I CAN INCLUDE

code:--------------------------------------------------------------------------------
function saveFormData(strTable, strDBName)
//returns identity
on error resume next
      saveFormData = 0
        
      set objCn = server.CreateObject("ADODB.Connection")
      set objCmd = server.CreateObject("ADODB.Command")
      set objRS = server.CreateObject("ADODB.Recordset")
        
      strSQL = "select top 1 * from " & strTable
      set objRS = getRS(strSQL, strDBName)

      for each iField in objRS.Fields
        if Request.Form(iField.name) <> "" then
            strComma = ""
            If strCols <> "" Then strCols = strCols & ", " 
            If strVals <> "" Then strVals = strVals & ", " 
            strCols = strCols & iField.name
            strVals = strVals & "'" & replace(Request.Form(iField.name),"'","''") & "'"
        end if
      next
      objRS.Close

//Add Date info, These colums SHOULD exist on all tables
      If strCols <> "" Then strCols = strCols & ", " 
      If strVals <> "" Then strVals = strVals & ", " 
      strCols = strCols & "CREATE_BY, CREATE_DTE"
      strVals = strVals & "'" & session("User") & "', '" & now() & "'"

//Execute Insert
      strSQL = "insert into " & strTable & " (" & strCols & ") Values (" & strVals & ")"
      'Response.Write strSQL
      objCn.Open application(strDBName & "_ConnectionString")
      objCmd.ActiveConnection = objCn
      objCmd.CommandTimeout=999
      objCmd.Commandtext = strSQL
      objCmd.Execute
      if instr(1,err.Description,"Cannot insert duplicate key")>0 then
      Response.Write "<h1>CAN NOT ENTER DUPLICATE RECORD</h1>"
      Response.Write "<h3>Please ues your browsers Back button to return to the previous page.</h3>"
      Response.End
      elseif err.number <> 0 then
      Response.Write "<h1>SQL Error: " & err.number & " </h1>"
      Response.Write "<h3>" & err.Description & "</h3>"
      Response.End
      end if
//Get Identity
      objCmd.ActiveConnection = objCn
      objCmd.CommandTimeout=999
      objCmd.Commandtext = "SELECT @@IDENTITY AS 'Identity'"
      set objRS = objCmd.Execute

      saveFormData = objRS("Identity")

      objRS.Close
--------------------------------------------------------------------------------

0
Comment
Question by:cvsherri
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 1

Author Comment

by:cvsherri
ID: 10795015
HERE IS an attempt to make this work by another person, but it dosne really work as well as i hoped, as i mentioned, i really need it to be a function that i can include, vie REQUIRE file

PHP:
--------------------------------------------------------------------------------
 
var $table_descriptions; // List of table descriptions keyed by $tablename
var $table_ids; // List of table primary keys (to UPDATE on)
var $user_data; // Data from a POST ($arr['fieldname'] = 'value')
foreach($this->table_descriptions as $tablename => $table){
  foreach($table as $column){// Loop through table columns
    if(array_key_exists($column, $user_data)){// Find a match
      $sqllist[] = "$column = '".$user_data[$column]."'"; // Add to SQL
    }
  }
  $sqls[$tablename] = $$tablename = "UPDATE $tablename SET ".implode(",\n", $sqllist)." WHERE $table[0] = ".$this->table_ids[$table[0]]; // Generate a query
  $sqllist = array(); // Reset array
}
foreach($sqls as $sql){ // Loop queries
  $result = mysql_query($sql); // Do queries
}

--------------------------------------------------------------------------------


0
 
LVL 5

Expert Comment

by:TheClickMaster
ID: 10797589
This should give you an idea of how to do it. I am really not very familliar with ADO and PHP so I dont know if this works. As said, something to start with.

function saveFormData($strTable, $strDBName)
{
                $saveFormData = 0;
       
                $dbc = new COM("ADODB.Connection");
      $objCmd = new COM("ADODB.Command");
      $objRs = new COM("ADODB.Recordset");

      $dbc->Provider = "MSDASQL";
      $dbc->Open($strDBName);

   
               $strSQL = "select top 1 * from ".$strTable;
      
               $objRs = $dbc->Execute($strSQL);

      $i = 0;
               while ($i < $rs->Fields->Count)
      {
                $name = $rs->Fields($i)->name;
                        if (isset($_POST[$name]))
                 {
                                   $strComma = "";
                                   if ($strCols != "") $strCols .= ", ";
                                   if ($strVals != "") $strVals .= ", ";
                                   $strCols .= $information["name"];
                                   $strVals .= "'".str_replace($_POST[$name],"'","''")."'";
                           }
                 $i ++;
       }
         
           //Add Date info, These colums SHOULD exist on all tables
           if ($strCols != "") $strCols .= ", ";
           if ($strVals != "")  $strVals .= ", ";
           $strCols .= "CREATE_BY, CREATE_DTE";
      
            // You have to check the format of the date function to adapt it
            // to reproduce the formating of now()
            // go to http://ca2.php.net/manual/en/function.date.php
           
           $strVals .= "'".$_SESSION["User"]."', '".date("F j, Y, g:i a")."'";

           //Execute Insert
           $strSQL = "insert into ".$strTable." (".$strCols.") Values (".strVals.")";


 .... You should get the idea by now...
 ... I hope this works.




0
 
LVL 5

Expert Comment

by:TheClickMaster
ID: 10797614
Here is some information I found

http://php.weblogs.com/odbc
http://php.weblogs.com/COM_php

There is a class to work with ADO (ADOdb)
http://php.weblogs.com/ADODB
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:cvsherri
ID: 10810825
kewl i will try that. i dont actualy want to use ADO, my preferance is to use MySQL as the database, using the native PHP handels for database interaction
0
 
LVL 5

Expert Comment

by:TheClickMaster
ID: 10810914
OMg you kidding me :|

I coded the complete function in MYSQL then changed averything to ADO...
Arggh I'll try to find that file. :(
0
 
LVL 5

Accepted Solution

by:
TheClickMaster earned 500 total points
ID: 10811068
Here is the MySQL version then =)

<?php
function saveFormData($strTable, $strDBName)
{
      $database = mysql_connect("localhost","user","pass");
        mysql_select_db($strDBName,$database);
        
        $strSQL = "select top 1 * from ".$strTable;
      $result = mysql_query($strSQL);

       $i = 0;
     while ($i < mysql_num_fields($result))
     {
               $info = mysql_fetch_field($result, $i);
               $name = $info->name;
               if (isset($_POST[$name]))
               {
                           $strComma = "";
                    if ($strCols != "") $strCols .= ", ";
                    if ($strVals != "") $strVals .= ", ";
                    $strCols .= $name;
                    $strVals .= "'".str_replace($_POST[$name],"'","''")."'";
               }
                $i ++;
      }
         
         //Add Date info, These colums SHOULD exist on all tables
         if ($strCols != "") $strCols .= ", ";
         if ($strVals != "")  $strVals .= ", ";
         $strCols .= "CREATE_BY, CREATE_DTE";
 
            // You have to check the format of the date function to adapt it
            // to reproduce the formating of now()
            // go to http://ca2.php.net/manual/en/function.date.php
        
         $strVals .= "'".$_SESSION["User"]."', '".date("F j, Y, g:i a")."'";

         //Execute Insert
         $strSQL = "insert into ".$strTable." (".$strCols.") Values (".strVals.")";
         $result = mysql_query($strSQL);
        
         // an error if errno > 0
         if(mysql_errno())
         {
                     //Something here
                  print "MYSQL ERROR: ".mysql_error()."<br>";
         }
        
         // get the Value of the last auto-increment field
         $returning = mysql_insert_id($database);
        
         mysql_close($database);
        
         return $returning;
}

?>
0

Featured Post

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question