Struggling with audit trail

[bizgen]

I have created an audit trail to record the creation of a new list of selected categories against a newly created service provider.

The problem I am having is that it is only posting the first record to the database table even though I have included the audit trail within the foreach statement. The same audit trail has worked fine in other areas of the database, but this is the only one where I have used it in relation to an array and a foreach statement so am wondering if I am doing something wrong with that.

Code below......................


foreach ($_POST['selected_cat'] as $levelc_lk) {
$querycatcodes = "SELECT * FROM $table_cat_level3 WHERE levelc_lk = '$levelc_lk'";
$result_querycatcodes = MYSQL_QUERY ($querycatcodes);
$levelb_lk=mysql_result($result_querycatcodes,0,"l evelb_lk");
$levela_lk=mysql_result($result_querycatcodes,0,"l evela_lk");

$insert="INSERT INTO $table_main (clientid,levela_lk,levelb_lk,levelc_lk) VALUES ('$clientid','$levela_lk','$levelb_lk','$levelc_lk ')";
MYSQL_QUERY($insert);

//audit trail -----------------------------------------------------------------------------------------
$type="Create";
$field="Cat Record";
$userid=$_SESSION['user_id'];
$old_data="Not Applicable";
$new_data="$levelc_lk";
$audit="INSERT INTO $table_audit_main (userid, clientid, type, field, old_data, new_data) VALUES ('$userid','$clientid','$type','$field','$old_data ','$new_data')";
MYSQL_QUERY($audit);

//------------------------------------------------------------------------------------------------------

}


Any help appreciated.

[imvain2]

I would suspect part of the problem is your foreach. Foreach is meant to loop through arrays, but you are trying to loop through a list.

try:

$selected_cats = explode(",",$_POST['selected_cat']);

foreach ($selected_cats as $levelc_lk) {

[MrGamm]

My guess is that your hogging the database and pulling too many queries...

Why not use the for loop to create the query you need.

INSERT INTO (1,2,3) VALUES (1,2,3), (1,2,3), (1,2,3),(1,2,3), (1,2,3), (1,2,3),(1,2,3), (1,2,3), (1,2,3)

I can't remember the syntax exactly... but you also have the option to INSERT DELAYED or with LOW PRIORITY... I am not sure which one will reduce the server load for your instance... but it's worth looking into... jmo...

It would be my preference to record the entire action of the for loop in a single audit entry. Why audit a for loop? Why not audit the function?