...

View Full Version : Assembling a query from a large number of fields



Nightchild
06-08-2007, 02:34 PM
Looking for techniques to properly handle this type of input/validate/save in SQL scenario...

I have a form with a very large number of fields (40+). After the user clicks the "submit" button, the php comes around to validating and assembling a MySQL UPDATE query. Right now I am assembling the input fields into the query like this:

$query = $ query . "ncr_number='" . $_POST['ncr_number'] . "',ncr_date='" . $_POST['ncr_date'] . "',status='" . $_POST['status'] ... <etc><etc>
(repeat for 40+ fields - note some fields are numeric, some text, some date)

This makes the query assembly extremely long and difficult to modify/maintain (amateurish too). What is a better way to get the user input from 40+ fields on a form, and then assemble the UPDATE query? I am considering breaking the form up into smaller forms but the user is insisting everything on one page at this point.

Appreciate the guidance on this obvious first foray into serious PHP.

Fou-Lu
06-08-2007, 03:29 PM
This is risky if done incorrectly (leaves lots of open holes)
If the data type to be inserted is the same (String), then you can pass the values through an HTML array instead of individual names:
name=arrName[firstName]...
name=arrName[secondName]...
And can be accessed through a loop instead. This is nice as it shortens the length of your code, and you can typecast and do the appropriate error checking.
If you have different data types, you will need to look at using a name which relates to the data type, or creating a multi-dimensional array to contain the data type within it.
name=arrName[StrFirstName] -> can be typecast to a String
name=arrName[IntFirstName] -> can be typecast to an int
name=arrName[firstName][String]
name=arrName[SecondName][int]

Eventually you will figure out a good solution to use, but the loop is an option if you fill the holes properly.

kbluhm
06-08-2007, 04:02 PM
Well, since you're naming your keys after your table columns (common practice), the first thought would be that you could just build the query from the $_POST array. But then you'd have to consider keys that aren't column names (ie: submit button). So you could grab the current columns and build the query from them.

Give this function a shot. I pulled it from a MySQL class I've written and did a bit of modifying to make it a stand-alone function. I haven't tested this code, but it should work for you. It will grab the columns from a table as a multi-dimensional array, with the table columns being the array keys, and the values being an associative array of some useful columns info. Any info you don't need, you can just yank from the array.

This will obviously add an extra query though... price you pay for convenience... ;)

<?php

// Get table columns
function table_columns( $table )
{
$qry = 'SHOW COLUMNS FROM ' . ( string ) $table;
$res = mysql_query( $qry );
$cols = array();
while ( $row = mysql_fetch_assoc( $res ) )
{
$cols[ $row['Field'] ] = array
(
'type' => $row['Type'],
'null' => $row['Null'],
'key' => $row['Key'],
'default' => $row['Default'],
'extra' => $row['Extra'],
);
}
return $cols;
}

?>

Here's some example usage:

SELECT

<?php

/**
* SELECT
*/

// Set your initial query and grab table columns
$query = "SELECT * FROM table_name";
$columns = table_columns( 'table_name' );

// Build clauses
$where = array();
foreach ( $_POST as $k => $v )
if ( isset( $columns[$k] ) )
$where[] = $k . ' = \'' . mysql_real_escape_string( $v ) . '\'';

// Append to query and run query if necessary
if ( count( $where ) )
{
$query .= ' WHERE ' . implode( ' AND ', $where );
$result = mysql_query( $query );
}

?>

INSERT

<?php

/**
* INSERT
*/

// Set your initial query and grab table columns
$query = "INSERT INTO table_name ( %COLUMNS% ) VALUES ( %VALUES% )";
$columns = table_columns( 'table_name' );

// Build clauses
$insert = array();
foreach ( $_POST as $k => $v )
if ( isset( $columns[$k] ) )
$insert[$k] = '\'' . mysql_real_escape_string( $v ) . '\'';

// Append to query and run query if necessary
if ( count( $insert ) )
{
$query = str_replace
(
array( '%COLUMNS%', '%VALUES%' ),
array( implode( ', ', array_keys( $insert ) ), implode( ', ', array_values( $insert ) ) ),
$query
);
$result = mysql_query( $query );
}

?>

UPDATE

<?php

/**
* UPDATE
*/

// Set your initial query and grab table columns
$query = "UPDATE table_name SET %VALUES% WHERE this = 'that'";
$columns = table_columns( 'table_name' );

// Build clauses
$values = array();
foreach ( $_POST as $k => $v )
if ( isset( $columns[$k] ) )
$values[] = $k . ' = \'' . mysql_real_escape_string( $v ) . '\'';

// Add query values and run query if necessary
if ( count( $values ) )
{
$query = str_replace( '%VALUES%', implode( ' AND ', $values ), $query );
$result = mysql_query( $query );
}

?>

...you get the idea.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum