Solved

URGENT: looping thru columns and updating/inserting data

Posted on 2004-04-09
6
209 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
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

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…
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.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

830 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