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.
Page 1 of 4 123 ... LastLast
Results 1 to 15 of 49
  1. #1
    Regular Coder
    Join Date
    Nov 2010
    Location
    Oregon
    Posts
    243
    Thanks
    23
    Thanked 10 Times in 10 Posts

    Insert array VALUES in SQL statement

    This code works for the normal SQL insert.
    Now lets re-write this in the second example below except PHP will write the code from the selected table.

    Example 1
    PHP Code:
    <?php
    $table   
    "employees" ;            
    $columns "FirstName, Lastname" ;  

    // assign fields to be posted
    $F1 $_POST[FirstName];
    $F2 $_POST[LastName]; 

    // pass value to SQL
    $val "'$F1','$F2'";

    if(
    $_POST) { mysql_query("INSERT INTO $table ($columns) VALUES ($val)");
    header("Location: ./$mytable.php ");
    }
    Notice: This is written the same way as above except PHP is writing the code
    Question is: Could the insert VALUES ($val) work with PHP creating it like this?

    Example 2
    PHP Code:
    $table   $_POST[SelTable];   // post selected table
    $columns $_POST[SelColumns]; // post selected fields

    $res mysql_query"SELECT $columns FROM $table");
      
    $data  = array();  
      
    $i 0
      
    $show_once false;  
        while (
    $row mysql_fetch_array($res)) { 
          if (!
    $show_once) {
        foreach (
    array_keys($row) AS $fname ) { 
          if ( !
    is_int($fname)) { $i++; 
          
    $F$i $_POST[$fname]; // $F$i = does not work like this
          
    $data[] = "'\$F$i'";
    }} 
    $show_once true
    $data implode($data',') ;
    $val $data// this writes: '$F1', $F2' etc..
    }}

    if(
    $_POST) { mysql_query("INSERT INTO $table ($columns) VALUES ($val)");
    header("Location: ./$table.php ");
    ?> 
    Last edited by DataTalk; 01-24-2011 at 04:40 PM. Reason: PHP tags

  • #2
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,468
    Thanks
    8
    Thanked 1,085 Times in 1,076 Posts
    I'm not totally sure of what you're actually trying to do.
    I have a hunch that it should be done another way.

    But, to answer your question, you are attempting what is called "Variable variable".

    Here is the correct syntax for that with a little example you can try:
    PHP Code:
    <?php

    $F1
    ="Bill";
    $F2="Smith";

    for(
    $i=1$i<3$i++){

    $test .= ${F.$i};

    }

    echo 
    $test;


    ?>

    The echoed output is: BillSmith

    ===================================

    You would be changing this:

    $F$i = $_POST[$fname];
    $data[] = "'\$F$i'";


    To this:

    ${F.$i} = $_POST[$fname];
    $data[] = ${F.$i};





    .
    Last edited by mlseim; 01-23-2011 at 10:26 PM.

  • #3
    Regular Coder
    Join Date
    Nov 2010
    Location
    Oregon
    Posts
    243
    Thanks
    23
    Thanked 10 Times in 10 Posts
    Here is the best way I can explain what I am trying to do;

    EXAMPLE 1
    As you know, this is the normal way of inserting data from a Form into the Table
    PHP Code:
    if($_POST) { mysql_query("INSERT INTO employees (FirstName, LastName) 
    VALUES ('"
    .$_POST['FirstName']."', '".$_POST['LastName']."')"); 

    With that being said, lets say we wanted to use this same script to input data into any table.
    The insert statement has 3 parts $table $columns $val.
    So I will re-write this so we can use this as a template

    PHP Code:
    if($_POST) { mysql_query("INSERT INTO $table ($columns) VALUES ($val)"); 

    Next we need to populate the template with any selected $table and $columns

    $table = $_POST[SelectedTable];
    $columns = $_POST[SelectedColumns];

    Now, here is where it gets tricky adding the VALUES because they are functions not "data"

    So I wrote the script in my first post example 2 to write the FieldNames for the post function
    I am not sure how to insert them into the VALUES ($val)
    Last edited by DataTalk; 01-24-2011 at 01:58 AM.

  • #4
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    Something like this? All you'll need is the table name and an associative array where the keys match the field names. It will build the INSERT for you and properly sanitize the values.
    PHP Code:
    function mysql_insert$table, Array $values$link_identifier NULL, & $error NULL )
    {

        
    // Sanitize table name    
        
    $table  '`' str_replace'.''`.`'$table ) .'`';

        
    // Grab the field names
        
    $fields array_keys$values );

        
    // Sanitize field names
        
    foreach ( $fields as $k => $v )
        {
            
    $fields$k ] = '`' str_replace'.''`.`'$v ) .'`'
        
    }

        
    $fields implode', '$fields );

        
    // Sanitize values
        
    foreach ( $values as $k => $v )
        {
            
    $values$k ] = '\'' mysql_real_escape_string$v$link_identifier ) .'\''
        
    }

        
    $values implode', '$values );

        
    // Build the INSERT query
        
    $sql 'INSERT INTO ' $table ' SET ( ' $fields ' ) VALUES ( ' $values ' )';

        
    // Send the query
        
    $res mysql_query$sql$link_identifier );

        
    // Anything go wrong?
        
    if ( ! $res )
        {
            
    $error mysql_error$link_identifier );
            return 
    FALSE;
        }

        
    // Return the inserted ID
        
    return mysql_insert_id$link_identifier );


    Usage:
    PHP Code:
    // Connect!
    $db_conn mysql_connect'xxx''xxx''xxx' );
    mysql_select_db'xxx'$db_conn );

    // Define our new employee
    $employee = array(
        
    'FirstName' => 'Joe',
        
    'LastName'  => 'Smith',
    );

    // Insert and grab the new ID
    $insert_id mysql_insert'employees'$employee$db_conn$error_text );

    // Anything go wrong?
    if ( ! $insert_id )
    {
        echo 
    'Error: '$error_text;
    }
    // Nope!
    else
    {
        echo 
    'Successfully created employee #'$insert_id'!';

    This is entirely untested, but should work as-is or with very little tweaking.

    If that's not closer to what you need, then I'm completely lost as to what you're looking to accomplish.
    Last edited by kbluhm; 01-24-2011 at 02:32 AM.

  • Users who have thanked kbluhm for this post:

    DataTalk (01-25-2011)

  • #5
    Regular Coder
    Join Date
    Nov 2010
    Location
    Oregon
    Posts
    243
    Thanks
    23
    Thanked 10 Times in 10 Posts
    Yes - Thanks! This looks more like what I am trying to accomplish here.

    So if I am following this correctly, If I post any table with the selected columns for that table,
    this should build and sanitize the INSERT INTO.

    Note-1. Should SET be in the SQL statement

    Note-2. I added the post $table and post $values and tested it
    There is an error on line below // *** ERROR HERE ****

    PHP Code:
    // include connect
    // You Are Now Connected - Session Started

    $table  $_POST[SelTable]; // post selected table
    $values $_POST[SelColumns]; // post selected fields

    function mysql_insert$table, Array $values$link_identifier NULL )
    {
        
    // sanitize table name    
        
    $table  '`' str_replace'.''`,`'$table ) .'`';

        
    // Grab the keys and values
        
    $keys   array_keys$values );
        
    $values array_values$values );

        
    // sanitize field names

    // **** ERROR HERE ****
        
    foreach ( $keys as $k => $v )
        {
            
    $keys$k ] = '`' str_replace'.''`.`'$v ) .'`'
        
    }

        
    $keys implode', '$keys );

        
    // sanitize values
        
    foreach ( $values as $k => $v )
        {
            
    $values$k ] = '\'' mysql_real_escape_string$v$link_identifier ) .'\''
        
    }

        
    $values implode', '$values );

        
    // Build the INSERT query
        
    $sql 'INSERT INTO ' $table ' SET ( ' $keys ' ) VALUES ( ' $values ' )';

        
    // Send the query
        
    mysql_query$sql$link_identifier );

        
    // Return the inserted ID
        
    return mysql_insert_id$link_identifier );

    Not following the second code kbluhm posted!
    We don't know what table or columns will be posted in, so how would we know to define employees it may be customers or products etc.....
    Last edited by DataTalk; 01-24-2011 at 03:48 AM.

  • #6
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    What error are you getting?

    The second code example is how you'd use the function within your code:
    PHP Code:
    // Define our new employee
    $employee = array(
        
    'FirstName' => 'Joe',
        
    'LastName'  => 'Smith',
    );

    // Insert and grab the new ID
    $insert_id mysql_insert'employees'$employee$db_conn$error_text ); 
    The function's first parameter allows you to define the table name.
    Last edited by kbluhm; 01-24-2011 at 03:51 AM.

  • #7
    Regular Coder
    Join Date
    Nov 2010
    Location
    Oregon
    Posts
    243
    Thanks
    23
    Thanked 10 Times in 10 Posts
    In the second example, until the user does post the table and columns we don't know what table or columns will be;
    so we cant define employees, it may be customers or products or jobs etc...
    Last edited by DataTalk; 01-24-2011 at 03:29 PM.

  • #8
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    Yeah, the table name could come from a posted value if you'd like.

    The posted values could come from $_POST as well, but they will likely have to be filtered somehow... if you send a field name to the function that does not exist it will throw an error.
    PHP Code:
    $table_name $_POST['table_name'];

    $employee = array(
        
    'FirstName' => isset( $_POST['FirstName'] ) ? $_POST['FirstName'] : '',
        
    'LastName'  => isset( $_POST['LastName'] )  ? $_POST['LastName']  : '',
    );

    // Insert and grab the new ID
    $insert_id mysql_insert$table_name$employee ); 
    Last edited by kbluhm; 01-24-2011 at 04:02 AM.

  • #9
    Regular Coder
    Join Date
    Nov 2010
    Location
    Oregon
    Posts
    243
    Thanks
    23
    Thanked 10 Times in 10 Posts
    Please Read! This should get us all on the same page!

    I AM WRITING A FORM TEMPLATE THAT WILL POST DATA TO ANY SELECTED TABLE
    We must pre-defined the template not knowing the TableName and Columns(fields) that will be posted in.

    I'm not worried about the field data that gets posted to the table right now, I will throw that in after this works.
    I am using employees with 2 fields (FirstName LastName) temporarily to build the template.
    When the template is working properly, the employees table gets replaced with $table = $_POST[SelTable] and fields with $columns = $_POST[SelColumns]

    Now its up to the user POST[table] POST[columns] ..
    The code will create the DataEntry Form for whatever table -n- fields the user selected.

    So this code gets the array FieldNames from any selected table!
    $VarData = $fname will have to pre-construct the associated array because again we
    don't know until the user posts the table what the columns names will be

    Code:
    $table  = $_POST[SelTable]; // post selected table
    $values = $_POST[SelColumns]; // post selected fields
    
    $res = mysql_query( "SELECT $columns FROM $table");
      $show_once = false;  
        while ($row = mysql_fetch_array($res)) { 
          if (!$show_once) {
        foreach (array_keys($row) AS $fname ) { 
          if ( !is_int($fname)) {  
    
    // Define the table-n-columns that get posted
    $VarData = array(
        '$fname' => '$fname'
    );
    }} 
    $show_once = true; 
    }}
    
    if($_POST) { mysql_query("INSERT INTO $table ($columns) VALUES ($VarData)");
    header("Location: ./$table.php ");
    }
    Here is the proposed data-entry part
    PHP Code:
    // build a query of input fields based on the same $table Selection
    $res2 mysql_query"SELECT $columns FROM $table");
      
    $show_once2 false;  
        while (
    $row2 mysql_fetch_array($res2)) { 
          if (!
    $show_once2) {
        foreach (
    array_keys($row2) AS $fname2 ) { 
          if ( !
    is_int($fname2)) {
    build the Query for field names here:

    // list the same fields with input so it will work in the above posting structure
    echo "<br><input type='text' name='$fname2'>"
    Last edited by DataTalk; 01-24-2011 at 07:24 PM.

  • #10
    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 first off, I see where the error is occurring... no semi-colon at the end of the line within the foreach loop.

    Second, you're losing me here. You do know what field names are in the database, correct?

    Knowing the field names allows you to pre-construct an associative array, with the keys being the field names and the values being the data to be associated with those field names.

    Here are two functions... the new one being mysql_strict(), so you can insert data such as NOW() and avoid having it escaped or surrounded by quotes.

    These function would be included somewhere in a globally accessible file. These are custom functions and if you want to use them they must exist somewhere in your code prior to their use:
    PHP Code:
    /**
     * mysql_strict( mixed $value )
     * Allows values to be strictly passed to mysql_insert()
     * 
     * @return string
     */

    function mysql_strict$value )
    {
        if ( ! 
    class_exists'__mysql_strict' ) )
        {
            class 
    __mysql_strict
            
    {
                private 
    $value;
                public function 
    __construct$value )
                {
                    
    $this->value $value
                
    }
                public function 
    __toString()
                {
                    return ( string ) 
    $this->value;
                }
            }
        }
        return new 
    __mysql_strict$value );
    }

    /**
     * mysql_insert( mixed $table, array $values [, $link_identifier = NULL [, & $error = NULL ] ] )
     * Simplifies creation of INSERT queries
     * 
     * @return int
     */

    function mysql_insert$table, Array $values$link_identifier NULL, & $error NULL )
    {

        
    // Sanitize table name    
        
    $table  '`' str_replace'.''`.`'$table ) .'`';

        
    // Grab the field names
        
    $fields array_keys$values );

        
    // Sanitize field names
        
    foreach ( $fields as $k => $v )
        {
            
    $fields$k ] = '`' str_replace'.''`.`'$v ) .'`'
        
    }

        
    $fields implode', '$fields );

        
    // Sanitize values
        
    foreach ( $values as $k => $v )
        {
            if ( 
    $v instanceof __mysql_strict )
            {
                
    $values$k ] = ( string ) $v;
            }
            else
            {
                
    $values$k ] = '\'' mysql_real_escape_string$v$link_identifier ) .'\'';
            }
        }

        
    $values implode', '$values );

        
    // Build the INSERT query
        
    $sql 'INSERT INTO ' $table ' SET ( ' $fields ' ) VALUES ( ' $values ' )';

        
    // Send the query
        
    $res mysql_query$sql$link_identifier );

        
    // Anything go wrong?
        
    if ( ! $res )
        {
            
    $error mysql_error$link_identifier );
            return 
    FALSE;
        }

        
    // Return the inserted ID
        
    return mysql_insert_id$link_identifier );


    ...then when it comes down to saving your data to the database, it'd be done like so:
    PHP Code:
    if ( ! empty( $_POST ) )
    {
        
        
    $data = array(
            
    // `CreatedDate` is an example field to show how mysql_strict() would be used
            
    'CreatedDate' => mysql_strict'NOW()' ),
            
    // The rest of your employee data
            
    'FirstName'   => $_POST['FirstName'],
            
    'LastName'    => $_POST['LastName'],
        );

        
    $insert_id mysql_insert'employees'$dataNULL$error_text );

        if ( ! 
    $insert_id )
        {
            echo 
    'Error creating employee: '$error_text;
        }
        else
        {
            echo 
    'Successfully created employee #'$insert_id;
        }

        exit;


    I realize this all may very well be over your head... but it's been a long Sunday of programming and I need to get away from my current project for a while.
    Last edited by kbluhm; 01-24-2011 at 04:33 AM.

  • Users who have thanked kbluhm for this post:

    DataTalk (01-25-2011)

  • #11
    Regular Coder
    Join Date
    Nov 2010
    Location
    Oregon
    Posts
    243
    Thanks
    23
    Thanked 10 Times in 10 Posts
    Yes I know what FieldNames are!
    Notice in my code: foreach (array_keys($row) AS $fname ) { in #9
    that specifically extracts just the Field Names from any table posted.

    I do like the INSERT NOW () I will get this working first.
    Last edited by DataTalk; 01-24-2011 at 03:26 PM.

  • #12
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by kbluhm View Post
    Knowing the field names allows you to pre-construct an associative array, with the keys being the field names and the values being the data to be associated with those field names.
    op said data can be functions, not sure if mysql function only and/or php( variable variables with values from post evaluated before building the query?),

    best regards

  • #13
    Regular Coder
    Join Date
    Nov 2010
    Location
    Oregon
    Posts
    243
    Thanks
    23
    Thanked 10 Times in 10 Posts
    Quote Originally Posted by oesxyl View Post
    op said data can be functions, not sure if mysql function only and/or php( variable variables with values from post evaluated before building the query?),

    best regards
    Thanks, That's what I have been asking.

    Is it possible to pre-construct the associative array NOT KNOWING THE FIELD NAMES.

    If so: $fname (as in my example #9) would have to somehow be applied to pre-construct the array.

    I'm sure nobody wants to go through the trouble of writing a code that will not work in the end.

    I AM WRITING A FORM TEMPLATE THAT WILL POST DATA TO ANY SELECTED TABLE
    IF THE POSTING STRUCTURE WORKS,
    I WILL THEN ADD THE $VAR FIELDS IN A SEPARATE QUERY LATER THATS THE EASY PART
    JUST QUERY THE SAME $COLUMNS INTO INPUT FIELDS AND BAM! WERE MULTI TASKING..
    Last edited by DataTalk; 01-24-2011 at 05:33 PM.

  • #14
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by DataTalk View Post
    Thanks, That's what I have been asking.

    Is it possible to pre-construct the sssociative array NOT KNOWING THE FIELD NAMES.

    If so: $fname (as in my example #9) would have to somehow be applied to pre-construct the array.

    I'm sure nobody wants to go through the trouble of writing a code that will not work in the end.
    i don't think that is hard to make it work but i'm sure is hard to make it safe,

    I AM WRITING A FORM TEMPLATE THAT WILL POST DATA TO ANY SELECTED TABLE
    IF THE POSTING STRUCTURE WORKS,
    I WILL THEN ADD THE $VAR FIELDS IN A SEPARATE QUERY LATER THATS THE EASY PART
    JUST QUERY THE SAME $COLUMNS INTO INPUT FIELDS AND BAM! WERE MULTI TASKING..
    multitasking? i can't follow.

    best regards

  • #15
    Regular Coder
    Join Date
    Nov 2010
    Location
    Oregon
    Posts
    243
    Thanks
    23
    Thanked 10 Times in 10 Posts
    The multitasking quote was a joke, lol...
    because were doing many things with the same Form


  •  
    Page 1 of 4 123 ... LastLast

    Posting Permissions

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