jcanker Posted August 20, 2007 Share Posted August 20, 2007 I am trying to create an edithistory table that has a text column into which I will put a string that can be read using strtok later to revert changes if necessary. I want to build this function so that it's dynamic enough to be applied to every table so that I don't have to write one for each table that might be updated. When a row is added to any table (a new entry), it retrieves the next available editid # from the edithistory table and assigns it to that item in the "edits" column for its row in its own table. Every edit made is a new entry in the edithistory table (entryid auto_increment primary key), and the editid is a reference back to the object being edited. Basically, when an update is submitted in a form, here's the order of operations: ->pull the old row for that item ->put it into an array named $oldarray ->update the table with the new information from the form ->pull the same row item that is now updated ->put the result into an array called $newarray ->pass $oldarray, $newarray, the name of the table where the update was made, and $editid for that row item into the function track_edit_history. ->track_edit_history function is supposed to pull the field names for the referenced table, and step through, comparing which elements are different between $oldarray and $newarray. When it finds a difference, it adds the appropriate field name, the old value, and the new value into at string that is placed in the edithistory column once it is finished building. The problem that I'm having is the layers of arrays that are involved here. I'm proficient enough to handle if/thens and process form data with the database, but I'm just starting to get the hang of arrays beyond mysql_query (select )and handling the returned data. I had a version of this that seemed to echo the proper values to the screen, but once I tweaked to to actually put the values into the database, now I'm just getting <fieldname> changed from Array to Array as the built string. Here's what I have so far: (sorry, cutting and pasting from my php editor doesn't seem to keep the same exact formating to make everything line up nice....) ??? In the code that is processing the form change to update the database: elseif(isset($_POST['attribute_edit']) && $_POST['attribute_edit']=="Submit") { //get the old results so that we can update the edit history table.... $result = mysql_query("select * from attributes where attribid ='$attribid'"); $oldarray = db_result_to_array($result); echo"<BR> oldarray is: $oldarray. and elem0 is $oldarray[0]"; // $oldarray = mysql_query("select * from attributes where attribid ='$attribid'"); $editid = $_POST['editid']; //this is a hidden element in the form $attribid = $_POST['attribid']; //this is a hidden element in the form $attribname = $_POST['attribname']; $human = $_POST['human']; $description = $_POST['description']; $creator = $_SESSION['username']; $timestamp = mktime(); $abbreviation = $_POST['abbreviation']; //now update the db $result = mysql_query("update attributes set attribname='$attribname',human=$human,description='$description',abbreviation='$abbreviation' where attribid='$attribid'"); if(!$result) { echo"<BR>DEBUGGING ERROR: Could not update the attribute table with this information. SQL ERROR: ".mysql_errno(); $page_content .="<table><tr><td>Error updating this attribute in the database!</td></tr>"; } else{$page_content .="<table><tr><td>Successfully updated this attribute in the database!</td></tr>";} //now update the edit history table $result = mysql_query("select * from attributes where attribname = '$attribname'"); if(!$result){echo"<BR>DEBUGGING ERROR: Could not retrieve new attribute settings from attribute db. SQLERROR:".mysql_errno();} $newarray = db_result_to_array($result); $table = "attributes"; $historyresult = track_edit_history($table,$oldarray,$newarray,$editid); if($historyresult ==0){$page_content .="<tr><td>Error entering the edit history! SQLErr:".mysql_errno()."</td></tr>";} elseif($historyresult ==1){$page_content .="<tr><td>Successfully entered edit history into the tracking db</td></tr>";} $page_content = "</table>"; }//close of elseif(isset($_POST['attribute_edit'] && $_POST['attribute_edit']=="Submit") And here is the function track_edit_history: function track_edit_history($table,$oldarray,$newarray,$editid) { //first, check to see if 0 was passed as the editid, meaning we have to generate the next available number if($editid==0) { $result = mysql_query("select editid from edithistory Order by editid desc LIMIT 1 "); $result = db_result_to_array($result); foreach ($result as $row){$editid = $row[0] +1;} } $result = mysql_query("show columns from ".$table); if(!$result) { echo "<BR>DEBUGGING ERROR: Could not retrieve the column names for table:$table. SQLERROR: ".mysql_errno();die(); } if (mysql_num_rows($result) > 0) { $result=db_result_to_array($result); $updatetext = ""; $i=0; foreach($result as $row) { if($oldarray[$i] != $newarray[$i]) { $oldarray1 = $oldarray[$i]; $newarray1 = $newarray[$i]; $row1 = $row['Field']; $updatetext .="~$row1 changed from $oldarray[$i] to $newarray[$i] |" ; echo"<BR>debugging info: update text from track_edit_history is $updatetext"; } $i++; } } $timestamp = mktime(); $creator = $_SESSION['username']; $query = "insert into edithistory (editid,timestamp,creator,tablename,editmade) VALUES ($editid,$timestamp,'$creator','$table','$updatetext')"; $result = mysql_query($query); if(!$result){echo "DEBUGGING ERROR: Could not insert this edit data in to edithistory. SQL ERROR: ".mysql_errno(); return 0;} else{return 1;} }//close of function All of this returns: Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 157 Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 157 Notice: Undefined offset: 2 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 157 Notice: Undefined offset: 2 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 157 ...and so on for however many elements are in $oldarray and $newarray I'm either processing arrays in the wrong order, or there are multidimential layers that I'm not understanding what's happening yet. Can anyone help me? Quote Link to comment Share on other sites More sharing options...
php_tom Posted August 20, 2007 Share Posted August 20, 2007 Here's what I have so far: In the code that is processing the form change to update the database: Hahaha That's pretty funny! Quote Link to comment Share on other sites More sharing options...
jcanker Posted August 20, 2007 Author Share Posted August 20, 2007 Yah...I accidentally tabbed and hit post while I was futzing around trying to get the code pasted. I was editing it while you were reading it Quote Link to comment Share on other sites More sharing options...
php_tom Posted August 20, 2007 Share Posted August 20, 2007 Yeah, not meaning to pick on you, it's just that when I start a new project, usually here's what I have so far: Quote Link to comment Share on other sites More sharing options...
jcanker Posted August 20, 2007 Author Share Posted August 20, 2007 ROFL! Looking at it that way gave me a good laugh that I needed at this point in the day. I've been working round the clock for the past week trying to get this website production ready. Thanks for the laugh Quote Link to comment Share on other sites More sharing options...
php_tom Posted August 20, 2007 Share Posted August 20, 2007 OK, seriously now... Can you post which line is #157? I don't have your whole code so I'm not sure.... Quote Link to comment Share on other sites More sharing options...
jcanker Posted August 20, 2007 Author Share Posted August 20, 2007 Line 157 is the line in the function. It's the line that compares the two arrays: if($oldarray[$i] != $newarray[$i]) { $oldarray1 = $oldarray[$i]; $newarray1 = $newarray[$i]; $row1 = $row['Field']; .....etc I think that somehow it's finding another array there instead of a specific element.... Quote Link to comment Share on other sites More sharing options...
jcanker Posted August 20, 2007 Author Share Posted August 20, 2007 For the record, the function db_result_to_array($result) is: function db_result_to_array($result) { $res_array = array(); for ($count=0; $row = @mysql_fetch_array($result); $count++) $res_array[$count] = $row; return $res_array; } Quote Link to comment Share on other sites More sharing options...
php_tom Posted August 20, 2007 Share Posted August 20, 2007 Here's a suggestion (not positive it will work): Change foreach($result as $row) { if($oldarray[$i] != $newarray[$i]) { $oldarray1 = $oldarray[$i]; $newarray1 = $newarray[$i]; $row1 = $row['Field']; $updatetext .="~$row1 changed from $oldarray[$i] to $newarray[$i] |" ; echo"<BR>debugging info: update text from track_edit_history is $updatetext"; } $i++; } to while($row = mysql_fetch_row($result)) { if($oldarray[$i] != $newarray[$i]) { $oldarray1 = $oldarray[$i]; $newarray1 = $newarray[$i]; $row1 = $row['Field']; $updatetext .="~$row1 changed from $oldarray[$i] to $newarray[$i] |" ; echo"<BR>debugging info: update text from track_edit_history is $updatetext"; } } Quote Link to comment Share on other sites More sharing options...
jcanker Posted August 20, 2007 Author Share Posted August 20, 2007 that returns: Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 150 150 is the first line of the suggested change ( I took out some comments and such that were mucking up the code...) Quote Link to comment Share on other sites More sharing options...
jcanker Posted August 20, 2007 Author Share Posted August 20, 2007 If I comment out the line $result=db_result_to_array($result) that is above $updatetext=""; then I get: Notice: Undefined index: Field in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 141 debugging info: update text from track_edit_history is ~ changed from Array to Array | Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 138 Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 138 Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 138 Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 138 Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 138 Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 138 Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 138 Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 138 Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 138 Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 138 Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 138 Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 138 Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 138 Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 138 Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 138 Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ProjectHobbes_com\collaboration\includes\db_fns.php on line 138 line 138-141 is: if($oldarray[$i] != $newarray[$i]) { $oldarray1 = $oldarray[$i]; $newarray1 = $newarray[$i]; $row1 = $row['Field']; Quote Link to comment Share on other sites More sharing options...
jcanker Posted August 21, 2007 Author Share Posted August 21, 2007 Thank you, PhpTom, for helping point me in the right direction. After sleeping on it I came back to it this morning. Well, I finally got something to work, but I think it's "Brute Force" coding. There's probably a more elegant way of achieving this. I would greatly appreciate any help with simplifying this, specifically the foreach{} sections that manually build the oldarray[] and newarray[] in the main code. I tried to use while() and for() statements to build those arrays, but that resulted in just the very last element being put into the oldarray[] or newarray[]. Here's what I'm using: The main page code is: $page_content="<table>"; $result= mysql_query("select * from attributes where attribid = ".$_POST['attribid']); $arraycount = mysql_num_fields($result); $result = db_result_to_array($result); $acount = count($result); $size = sizeof($result); $oldarray = array(); $i = 0; foreach($result as $row) { $oldarray[] = $row[0];echo"<BR>thisarray = ".$oldarray[0]; $oldarray[] = $row[1];echo"<BR>thisarray = ".$oldarray[1]; $oldarray[] = $row[2];echo"<BR>thisarray = ".$oldarray[2]; $oldarray[] = $row[3];echo"<BR>thisarray = ".$oldarray[3]; $oldarray[] = $row[4];echo"<BR>thisarray = ".$oldarray[4]; $oldarray[] = $row[5];echo"<BR>thisarray = ".$oldarray[5]; $oldarray[] = $row[6];echo"<BR>thisarray = ".$oldarray[6]; $oldarray[] = $row[7];echo"<BR>thisarray = ".$oldarray[7]; $oldarray[] = $row[8];echo"<BR>thisarray = ".$oldarray[8]; } $editid = $_POST['editid']; //this is a hidden element in the form $attribid = $_POST['attribid']; //this is a hidden element in the form $attribname = $_POST['attribname']; $human = $_POST['human']; $description = $_POST['description']; $creator = $_SESSION['username']; $timestamp = mktime(); $abbreviation = strtoupper($_POST['abbreviation']); //now update the db $result = mysql_query("update attributes set attribname='$attribname',human=$human,description='$description',abbreviation='$abbreviation' where attribid='$attribid'"); if(!$result) { echo"<BR>DEBUGGING ERROR: Could not update the attribute table with this information. SQL ERROR: ".mysql_errno(); $page_content .="<tr><td>Error updating this attribute in the database!</td></tr>"; } else{$page_content .="<tr><td>Successfully updated this attribute in the database!</td></tr>";} //now get the array of the new data... $result= mysql_query("select * from attributes where attribname = '$attribname'"); if(!$result){echo"<BR>DEBUGGING ERROR: Could not retrieve new attribute settings from attribute db. SQLERROR:".mysql_errno();} $result = db_result_to_array($result); foreach($result as $row) { $newarray[] = $row[0];echo"<BR>newarray = ".$newarray[0]; $newarray[] = $row[1];echo"<BR>newarray = ".$newarray[1]; $newarray[] = $row[2];echo"<BR>newarray = ".$newarray[2]; $newarray[] = $row[3];echo"<BR>newarray = ".$newarray[3]; $newarray[] = $row[4];echo"<BR>newarray = ".$newarray[4]; $newarray[] = $row[5];echo"<BR>newarray = ".$newarray[5]; $newarray[] = $row[6];echo"<BR>newarray = ".$newarray[6]; $newarray[] = $row[7];echo"<BR>newarray = ".$newarray[7]; $newarray[] = $row[8];echo"<BR>newarray = ".$newarray[8]; } $table = "attributes"; $historyresult = track_edit_history($table,$oldarray,$newarray,$editid); $page_content .= $historyresult."</table>"; And the function track_edit_history that is called near the end (the part that actually updates the edithistory table) is: function track_edit_history($table,$oldarray,$newarray,$editid) { $result = mysql_query("show columns from ".$table); if(!$result){echo "<BR>DEBUGGING ERROR: Could not retrieve the column names for table:$table. SQLERROR: ".mysql_errno();die();} if (mysql_num_rows($result) > 0) { $i=0; while($row = mysql_fetch_row($result)) { $fieldarray[] = $row[0]; $i++; } } $acount = count($oldarray); $updatetext = ""; for($i = 0;$i < $acount;$i++) { if($oldarray[$i] != $newarray[$i]) { $updatetext .= "^".$fieldarray[$i]."~".$oldarray[$i]."~".$newarray[$i]; } } $timestamp = mktime(); $creator = $_SESSION['username']; $query = "insert into edithistory (editid,timestamp,creator,tablename,editmade) VALUES ($editid,$timestamp,'$creator','$table','$updatetext')"; echo"<BR><BR>The Query will be: $query"; $result = mysql_query($query); if(!$result){$content = "<tr><td><B> ERROR: Could not insert this edit data in to edithistory. SQL ERROR: ".mysql_errno()."</b></td></tr>";} else{$content = "<tr><td><B> Successfully Entered This Change in the Edit History Database</b></td></tr>";} return $content; }//end of function Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.