Very Tricky Array Situation

[micxnet]

Hi there,

I have one arrray: $field_vals
There are three values in the array, however two are multi-dimension array.

i.e:
$field_vals[base] ->
[0] -> 1
[1] -> 2
$field_vals[make] ->
[0] -> 5
[1] -> 7
[2] -> 8
$field_vals[qty] -> 1

I have a MySQL with a table (part) for the data to be stored in: part_id (key), base_id, make_id, qty
I need to build a series of MySQL queries to INSERT the data into the table.

ie.
INSERT INTO part(cols) VALUES(1,5,1);
INSERT INTO part(cols) VALUES(1,7,1);
INSERT INTO part(cols) VALUES(1,8,1);
INSERT INTO part(cols) VALUES(2,5,1);
INSERT INTO part(cols) VALUES(2,7,1);

From the example array above, (2x3x1) 6 INSERT queries would be required. How would I go about generating a loop that would go through each array, outputting the selected values, as well as building the MYSQL queries to be executed on the form submission.

This is very difficult to work out, so if any additional information is required to help find a solution to my complex dilema, please let me know. Thanks in advance for any help.

[wickning1]

[code]$db = mysql_connect();
$qty = $field_vals['qty'];
foreach ($field_vals['base'] as $base) {
   foreach ($field_vals['make'] as $make) {
      $inserts .= "($base, $make, $qty),";
   }
}
$sql = "INSERT INTO part(base, make, qty) VALUES " . substr($inserts, 0, -1);
if (mysql_query($sql, $db)) echo "done.";
else echo "error.";[/code]
Something like that should do it.

[micxnet]

Thanks for the quick response. It will work that way, however, I have a general form processor that I use for my website and I was wondering if I could somehow integrate it into that function as well. Basically, all my forms have:
$field[table_column] <- which stores the field value or values in the case above.
$name[table_column] <- which stores the field name, this is always a single value since it is a hidden field.

The [table_column] corresponds to the exact column in the database. I also three additional values:
$table <- Mandatory, stores the table name.
$key <- Mandatory, stores the primary key column for the table.
$link <- Optional, however used if a entry is required to be added into another table that is related to the key in the primary table.

On submission, I use the $table value to list out all the columns that exist in the database table. Then I use a for() loop to go through each table column, which allows me to process both $field[table_column] and $field[table_column].

For me to be able to do a foreach() to achieve the list of queries I require to be executed in the database, I will most likely have to change the way I use the for loop, or will I? I am not exactly sure. My form processor code is quite long, however, I will place it at the bottom for reference.

What I could do is keep my first for() loop which outputs the submitted data on a confirmation page and create a separate section of the function altogether, which generates the queries I require. At the moment I generate a part of the MySQL queries inside the for loop, however, doesn't work quite as well when you have an array, rather then a single variable.

First of all, is it possible to do, if you do not want to static code to generate the queries?

I am not asking for people to completely rewrite my code, just some tips or hints on how to achieve this would be greatly appreciated.

Here is my current form processor function for reference, if you need any additional information, please let me know.

[code]<?php
function formprocess($table,$key,$fname,$fval,$stage,$lnktblcol=0) {
    // setup db connection
    $db = new sql;
    // Switch through each stage
    switch ($stage) {
    
    case "Submit":
        // set inital error variable
        $error = false;
        // lookup main table columns
        $columns = $db->show_columns($table);
        $count = count($columns);
        // include validator configuration file
        include('inc/validator.config.php');
        // begin loop through each column
        for ($i=0;$i<$count;$i++) {
            // set variables
            $column = $columns[$i];
            $option = "";
            
            // check that column exists in HMTL form
            if ($fname[$column] || $fval[$column]) {
                // begin row and set column value
                $content .= "<tr><td width='100' valign='top'>".$fname[$column].":</td><td width='175'><b>";
                $value = $fval[$column];
                // validate column value
                if (!is_array($value)) {
                    $fldmsg = validate($value,$db_col[$column]);
                } else {
                    $fldmsg = "OK";
                }
                // if not table key
                if ($column != $key) {
                    // set column field and table2 for drop down values
                    $selcolumn = ereg_replace($table."_","",$column);
                    $table2 = ereg_replace("_id","",$selcolumn);
                    // if drop down value
                    if (ereg("_id",$column) && !ereg("base_id",$column)) {
                        // grab table columns for drop down value
                        $columns2 = $db->show_columns($table2);
                        $lnkcolumn = ereg_replace($table2."_","",$columns2[2]);
                        // grab link column data
                        if (ereg("_id",$lnkcolumn)) {
                            $table3 = ereg_replace("_id","",$lnkcolumn);
                            if (!is_array($value)) {
                                $db->sql_query("SELECT * FROM $table2 INNER JOIN $table3 ON $table2.".$table2."_$lnkcolumn = $table3.$lnkcolumn WHERE $table2.$selcolumn = '$value' LIMIT 1");
                                $data = $db->fetch_rows();
                                $option = $data[1]." ".$data[4];
                            } else {
                                $vc = count($value);
                                for($r=0;$r<$vc;$r++) {
                                    $db->sql_query("SELECT * FROM $table2 INNER JOIN $table3 ON $table2.".$table2."_$lnkcolumn = $table3.$lnkcolumn WHERE $table2.$selcolumn = '$value[$r]' LIMIT 1");
                                    $data = $db->fetch_rows();
                                    $option .= $data[1]." ".$data[4].", ";
                                }
                                $option = trim($option,", ");
                            }
                        // grab general column data
                        } else {
                            if (!is_array($value)) {
                                $db->sql_query("SELECT * FROM $table2 WHERE $selcolumn = '$value' LIMIT 1");
                                $data = $db->fetch_rows();
                                $option = $data[1];
                            } else {
                                $vc = count($value);
                                for($r=0;$r<$vc;$r++) {
                                    $db->sql_query("SELECT * FROM $table2 WHERE $selcolumn = '$value[$r]' LIMIT 1");
                                    $data = $db->fetch_rows();
                                    $option .= $data[1].", ";
                                }
                                $option = trim($option,", ");
                            }
                        }
                    } elseif (ereg("base_id",$column)) {
                        if (!is_array($value)) {
                            $db->sql_query("SELECT * FROM $table2 WHERE $selcolumn = '$value' LIMIT 1");
                            $data = $db->fetch_rows();
                            $option = $data[0];
                        } else {
                            $vc = count($value);
                            for($r=0;$r<$vc;$r++) {
                                $db->sql_query("SELECT * FROM $table2 WHERE $selcolumn = '$value[$r]' LIMIT 1");
                                $data = $db->fetch_rows();
                                $option .= $data[0].", ";
                            }
                            $option = trim($option,", ");
                        }
                    } else {
                        $option = $value;
                    }
                    // Set final display value for drop down values
                    $value = $option;
                } else {
                    $keyval = $fval[$column];
                    // Check if key Exists
                    $db->sql_query("SELECT * FROM $table WHERE $key = '$keyval'");
                    $check = $db->fetch_rows();    
                }
                // check for: insert or update
                if (!$check) {
                    $mode = "INS";
                    $sqlcols .= $column.",";
                    $sqlvals .= "'".$fval[$column]."',";
                } else {
                    $mode = "UPD";
                    if ($column != $key) {
                        $sql_1 .= "$column='$fval[$column]',";
                    } else {
                        $sql_2 .= " WHERE $key='$fval[$column]'";
                    }
                    $sqlsets = $sql_1.$sql_2;
                }
                // completed row
                $content .= $value;
                $content .= "</b></td><td><b class='fldmsg'>$fldmsg</b></td></tr>\r\n";
            // skip if db column doesn't exist in HTML form
            } else {
                $fldmsg = "OK";
            }
            // check if all rows are OK to be processed by database    
            if ($fldmsg != "OK") {
                $error = true;
            }
        // end of output loop
        }
        // Compile SQL Statement
        if ($mode == "INS") {
            $sql_d = "INSERT INTO $table($sqlcols) VALUES($sqlvals);";
            $sql_d = ereg_replace(",)",")",$sql_d);
            $sql[] = $sql_d;
            $submit = "<input type='submit' name='submit' value='Continue' class='submit' />\r\n";
            
            // Create link table entry
            if ($lnktblcol) {
                $link_colname = $lnktblcol;
                $link_table = ereg_replace("_$key","",$link_colname);
                $keyid = $keyval;
                $sql[] = "INSERT INTO $link_table($link_colname) VALUES('$keyid');";
            }
        } elseif ($mode = "UPD") {
            $sql_d = "UPDATE $table SET $sqlsets";
            $sql_d = ereg_replace(", W"," W",$sql_d);
            $sql[] = $sql_d;
            $submit = "<input type='submit' name='submit' value='Overwrite' class='submit' style='color:red;' />\r\n";
        }
        // set variables for HTML page
        $pagename = "Confirm Details";
        if ($error) {
            $submit = "<input type='button' name='submit' value='Go Back' class='submit' onclick='history.go(-1)' />\r\n";
        }
        // Compile HTML page
        $page .= "<tr><td colspan='3' valign='top'><h2>$pagename</h2></td></tr>\r\n";
        $page .= $content;
        
        $csql =  count($sql);
        for ($x=0;$x<$csql;$x++) {
            $page .= "<input type='hidden' name='query[]' value=\"$sql[$x]\"/>\r\n";
        }
        
        $page .= "<tr><td colspan='3' valign='top' align='center'>$submit</td></tr>\r\n";
        return $page;
    break;
    
    case "Continue":
        $query = $_POST["query"];
        $querycnt = count($query);
        for ($y=0;$y<$querycnt;$y++) {
            $result = $db->sql_query(stripslashes("$query[$y]"));
        }
        
        // set variables for HTML page
        $pagename = "Results";
        if ($result) {
            $msg = "It has been added.";
        } else {
            $msg = "There was a problem";
        }
        // Compile HTML page
        $page .= "<tr><td colspan='3' valign='top'><h2>$pagename</h2></td></tr>\r\n";
        $page .= "<tr><td colspan='3' valign='top'>$msg</td></tr>\r\n";
        $page .= "<tr><td colspan='3' valign='top' align='center'><a href='javascript:history.go(-2)'>Add Another</a></td></tr>\r\n";
        return $page;
    break;
    
    case "Overwrite":
        $query = $_POST["query"];
        $querycnt = count($query);
        for ($y=0;$y<$querycnt;$y++) {
            $result = $db->sql_query(stripslashes("$query[$y]"));
        }
        
        // set variables for HTML page
        $pagename = "Results";
        if ($result) {
            $msg = "It has been updated.";
        } else {
            $msg = "There was a problem";
        }
        // Compile HTML page
        $page .= "<tr><td colspan='3' valign='top'><h2>$pagename</h2></td></tr>\r\n";
        $page .= "<tr><td colspan='3' valign='top'>$msg</td></tr>\r\n";
        $page .= "<tr><td colspan='3' valign='top' align='center'></td></tr>\r\n";
        return $page;
    break;
    }
}
?>
[/code]

[micxnet]

Maybe I have to use a different type of loop; such as a do-while or while instead of for or foreach loop? Would there be a combination of those that I could use instead? It would be great if I could use continue to keep the loop going, however, that looks like it won't give me the result i'm looking for.

[micxnet]

Unfortunately, there seems to be no way to generate this code dynamically so I have completed the function I require in static code.

Here is the code:
[code]
function partgen($base,$make,$kit) {
    // setup db connection
    $db = new sql;
    $mcount = count($make);
    for ($x=0;$x<$mcount;$x++) {
        $mres = mysql_query("SELECT make_name FROM make WHERE make_id='$make[$x]'");
        while ($mdata = mysql_fetch_row($mres)) {
            $mlist .= $mdata[0].", ";
        }
    }
    foreach ($base as $bval) {
        $blist .= $bval.", ";
        foreach ($make as $mval) {
            $db->sql_query("SELECT part_id FROM part WHERE part_base_id='$bval' && part_make_id='$mval' && part_kit_count='$kit'");
            $data = $db->fetch_rows();
            if (!$data) {
                $query[] = "INSERT INTO part(part_base_id,part_make_id,part_kit_count) VALUES('$bval','$mval','$kit');";
            } else {
                $query[] = "UPDATE part SET part_base_id='$bval',part_make_id='$mval',part_kit_count='$kit' WHERE part_id='$data[0]'";
                $check++;
            }
        }
    }
    $count = count($query);
    for($i=0;$i<$count;$i++) {
        $querylist .= "<input type='hidden' name='query[]' value=\"$query[$i]\"/>\r\n";
    }
    $blist = trim($blist,", ");
    $mlist = trim($mlist,", ");
    $page .= "<tr><td colspan='2'><h2>Parts Management</h2></td></tr>\r\n";
    $page .= "<tr><td width='100'>Base(s):</td><td>$blist</td></tr>\r\n";
    $page .= "<tr><td width='100'>Make(s):</td><td>$mlist</td></tr>\r\n";
    $page .= "<tr><td width='100'>Kit Count:</td><td>$kit</td></tr>\r\n";    
    $page .= $querylist;
    $page .= "<tr><td colspan='2' align='center'><input type='submit' name='submit' value='Continue' class='submit'/></td></tr>\r\n";
    return $page;
}
[/code]

Thanks for your help wickning1.

However, if anyone figures out how to do it more dynamically, could you please PM.