Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

URGENT: looping thru columns and updating/inserting data

Posted on 2004-04-09
6
Medium Priority
?
218 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
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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
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…
Suggested Courses

886 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