Solved

URGENT: looping thru columns and updating/inserting data

Posted on 2004-04-09
6
210 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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 look for a specific file type in a local or remote server directory using PHP.

739 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