Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Jun 2007
    Location
    Canada
    Posts
    49
    Thanks
    1
    Thanked 1 Time in 1 Post

    Assembling a query from a large number of fields

    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.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    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 Code:
    <?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 Code:
    <?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 Code:
    <?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 Code:
    <?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.
    Last edited by kbluhm; 06-09-2007 at 07:57 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •