I have a Table structure as
id, trackid, table_name, operation,
oldvalue, newvalue, field,
changedonetime
Now if I have 3 rows for
the same "trackid" same "field", then how can i select
the latest out of
the three?
i.e. for e.g.:
id = 100 trackid = 152 table_name
= jos_menu operation= UPDATE oldvalue = IPL newvalue = IPLcccc
field = name live = 0 changedonetime =
2010-04-30 17:54:39
and
id = 101 trackid = 152 table_name =
jos_menu operation= UPDATE oldvalue
= IPLcccc newvalue = IPL2222 field = name live = 0 changedonetime =
2010-04-30 18:54:39
As u can see above
the secind entry is
the latest change,
Now what query I should use to get
the only one and Latest row out of many such rows...
$distupdqry = "select DISTINCT trackid,table_name from jos_audittrail where live = 0 AND operation = 'UPDATE'";
$disupdsel = mysql_query($distupdqry);
$t_ids = array();
$t_table = array();
while($row3 = mysql_fetch_array($disupdsel))
{
$t_ids[] = $row3['trackid'];
$t_table[] = $row3['table_name'];
//$t_table[] = $row3['table_name'];
}
//echo "<pre>";print_r($t_table);echo "<pre>";
//exit;
for($n=0;$n<count($t_ids);$n++)
{
$qupd = "SELECT * FROM jos_audittrail WHERE operation = 'UPDATE' AND trackid=$t_ids[$n] order by changedone DESC ";
$seletupdaudit = mysql_query($qupd);
$row4 = array();
$audit3 = array();
while($row4 = mysql_fetch_array($seletupdaudit))
{
$audit3[] = $row4;
}
$updatefield = '';
for($j=0;$j<count($audit3);$j++)
{
if($j == 0)
{
if($audit3[$j]['operation'] == "UPDATE")
{
//$insqry .= $audit2[$i]['operation']." ";
//echo "<br>";
$updatefield .= "UPDATE `".$audit3[$j]['table_name']."` SET ";
}
}
if($audit3[$j]['operation'] == "UPDATE")
{
$updatefield .= $audit3[$j]['field']." = '".$audit3[$j]['newvalue']."', ";
}
}
/*echo "<pre>";
print_r($audit3);
exit;*/
$primarykey = "SHOW INDEXES FROM `".$t_table[$n]."` WHERE Key_name = 'PRIMARY'";
$prime = mysql_query($primarykey);
$pkey = mysql_fetch_array($prime);
$updatefield .= "]";
echo $updatefield = str_replace(", ]"," WHERE ".$pkey['Column_name']." = '".$t_ids[$n]."'",$updatefield);
}
In
the above code I am fetching ou
the distinct IDs in which update operation has been done, and then accordingly query is fired to get all
the changes done on different fields of
the selected distinct ids...
Here I am creating
the Update query by fetching
the records from
the initially described table which is here mentioned as audittrail table...
Therefore I need
the last made change in
the field so that only latest change can be selected in
the select queries i have used...
please go through
the code.. and see how can i make
the required change i need finally..
