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] -> Array (
[0] -> 1
[1] -> 2
)
$field_vals[make] -> Array (
[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. I would want this to be processed in the site form processor
function. Basically, all my HTML 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?
This is how I would do it, if I used static code:
---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-/-
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-/-
Thanks in Advance,
Mike
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
