Solved

URGENT: looping thru columns and updating/inserting data

Posted on 2004-04-09
6
205 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
  • 4
  • 2
6 Comments
 
LVL 1

Author Comment

by:cvsherri
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:cvsherri
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now