dynamic SQL insert query

Question

hi friends i'm creating a php page to import the data from a csv file into sql database..

here database table and number of fields are specified by user itself..

if user specifies 4 fields, then it is created using a for loop as follows..

<?php
include('connect.php');
$name = $_POST['table_name'];
//echo $name;

$create_tab = "CREATE TABLE $name(id varchar(15) PRIMARY KEY)";




if(mysql_query($create_tab,$con))
            {
                echo "Table <b><i>$name</i></b> created successfully... <br/> <br/>Add columns...";
            }
            else {
                die('Error1'.mysql_error());
            }



$field = $_POST['number_of_fields'];
//echo $name.$field;
echo '<form id="form1" name="form1" method="post" action="update-table.php">';
echo "<p>
          <label for='tablename'></label>
          <input type='hidden' name='tablename' id='tablename' value='$name' size='5'/>
        </p>";

echo "<p>
          <label for='fields'></label>
          <input type='hidden' name='fields' id='fields' value='$field' size='5'/>
        </p>";
echo '<table border="1" cellpadding="5" cellspacing="5">';
for ( $i = 1; $i <= $field; $i ++) {
    echo '<tr>';
    echo '<td>';
    echo "<p>
          $i
        </p>";
        echo'</td>';

    echo '<td>';
    echo "<p>
          <label for='textfield$i'></label>
          <input type='text' name='field$i' id='textfield$i' />
        </p>";
        echo'</td>';
            echo '<td>';
            echo "
            <select name='select$i' id='select$i'>
                    <option value='varchar(200)'>varchar</option>
                    <option value='int'>int</option>
                    <option value='float'>float</option>
                                        <option value='date'>date</option>
                  </select>";
                echo '</td>';
                                echo '</tr>';


    }
            echo '</table>';
?>
        <p>File Location :
          <input type="text" name="fileField" id="fileField" />
      </p>
<br/>
<INPUT type="image" name="search" src="images/alter.gif" border="0" height="75" width=120">

</form>

then table create and alter as follows..

 <?php
    include('connect.php');
$field = $_POST[fields]; 
$name = $_POST[tablename];    

for ( $i = 1; $i <= $field; $i++) {
//getting field names
  $varname = ($txtfield . $i);
    $$varname = $_POST[field.$i]; 
  // echo $$varname;
   $fi = $$varname;

//getting field types
  $selname = ($selfield . $i);
    $$selname = $_POST[select.$i]; 
     $dt = $$varname;

$sql = "ALTER TABLE $name ADD $fi $dt";

if(mysql_query($sql,$con))
            {
                echo "Field <b><i>$fi</i></b> added successfully...<br/>";
            }
            else {
                die('Error1'.mysql_error());
            }

    }


?>

as above database table and fields are crated...

i got the concept of inserting data using static variables as follows..

<?php
// data import
include('connect.php');


$field = $_POST['fileField']; //file directory

echo "<br/><br/>Import file path: ";
echo $field;

 $file = $field; 

$lines = file($file);
$firstLine = $lines[0];

foreach ($lines as $line_num => $line) {
    if($line_num==0) { continue; } //escape the header column
    $arr = explode(",",$line);
    $column1= $arr[0];
    $column2= $arr[1];
// ' escape character
    if (strpos($column2, "'") == FALSE)
{
    $column21 = $column2;
}
else{
$column21 = str_replace ("'", "\'", $column2);
}
        $column3= $arr[2];
            $column4= $arr[3];

//print data from csv
echo "<table border='1' width='800' cellpadding='5' cellspacing='2'>";
echo "<tr>";
echo "<td width='8'>";
echo $column1;
echo "</td>";
echo "<td width='100'>";
echo $column21;
echo "</td>";
echo "<td width='5'>";
echo $column3;
echo "</td>";
echo "<td width='5'>";
echo $column4;
echo "</td>";
echo "</tr>";
echo "</table>";

     $import="INSERT into $name (id,name,year1,year2) values(

     '$column1','$column21','$column3','$column4')";
     mysql_query($import) or die(mysql_error());

}

?>

now, my question is how can i make this insert statement dynamic as such it creates field names and values dynamically inside insert query from the data obtained from for loop in table create and alter query???

Bailey Parker · Accepted Answer · 2012-06-08 08:11:12Z

4

If I understand your question correctly, it sounds like you want to combine your inserts into one query (which is very smart performance wise). SQL allows you to insert multiple rows at once like so:

INSERT INTO table (id, first, last) VALUES(NULL, 'Ryan', 'Silvers'),(NULL, 'Oscar', 'Smith'),(NULL, 'Jessica', 'Owens')

So by using creating an array of VALUES and using implode to join them you can make one query:

//Create rows
foreach($rows as $row) {
    $queryRows[] = "(NULL, '".$row['first']."', '".$row['last']."')";
}

//Create query
$query = 'INSERT INTO table (id, first, last) VALUES'.implode(',', $queryRows);

Now that I understand your real question, I can give you a basic overview of what you need to do to achieve this. You need to create a form that allows a user to enter data that will be inserted into the table.

<form method="post" action="process.php">
    <input name="field1" />
    <!-- and more -->
</form>

Then you need to write a PHP script to process the form submission and insert the data into MySQL:

<?php
    //Check form submission and validate entries, then...

    $stmt = $pdo->prepare('INSERT INTO table (field1) VALUES(:field1)');
    $stmt->execute(array(':field1', $_POST['field1']));
?>

edited Jun 8, 2012 at 8:11

answered Jun 8, 2012 at 7:57

Bailey Parker

15.9k5 gold badges58 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jayakarthik Appasamy Over a year ago

Thanks.. but my question is.. can i get the values inside the query as Insert into table(dynamic value) values (dynamic vaues); where dynamic values is like field1, field2... obtained using for loop??

Bailey Parker Over a year ago

Oh I think I understand your question now. You should still really consider combining all of the insert queries still. I'll update with a link to a page that might help with your actual question.

Vishal Bhimani · Accepted Answer · 2015-07-15 13:11:19Z

Then you need to write a PHP script to process the form submission and insert the data into MySQL:

function insert($tablet,$datad) {

    if(empty($tablet)) { return false; }

    if(empty($this->CONN)){ return false; }

    $conn = $this->CONN;

    $query1 = "select * from user"; 
    $result1 = mysql_query($query1); 
    $numcolumn = mysql_num_fields($result1); 

    $addd = "";
    $count = 0;
    for ( $i = 1; $i < $numcolumn; $i++ ) 
    { 
        $columnnames = mysql_field_name($result1, $i); 
        if(($numcolumn-1) == $i)
        {
            $addd .= $columnnames;
            $data .= "'".$datad[$count]."'";
        }
        else
        {
            $addd .= $columnnames.",";
            $data .= "'".$datad[$count]."',";
        }
        $count++;
    }
    $ins = "INSERT INTO ".$tablet."(".$addd.")"."VALUES(".$data.")";
    mysql_query($ins);
    header('Location: index.php');
    exit;
}

Collectives™ on Stack Overflow

dynamic SQL insert query

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related