Page 1 of 1

Creating INSERT statement from HTML form for MySQL

Posted: Sun Oct 13, 2002 6:02 pm
by crimius
I'm using an HTML form to gather the field values to be inserted into the table. The HTML form that gathers these values is generated dynamically with:

Code: Select all

//find out column names from db defined by mysql_list_fields()
$result_handle = mysql_list_fields ("some_db", $table_name) or die("mysql_list_fields () failed with this error message: '". mysql_error () . "'");

//determine total number of fields for table
$number_fields = mysql_num_fields ($result_handle);

//begin HTML table formatting
echo '<table width="100%" cellspacing="0" cellpadding="5" border="0">', "\n";

for ($index = 0; $index < $number_fields; ++$index) {
    echo '<tr><td width="150"><b>', mysql_field_name ($result_handle, $index), '</b></td>';
    echo '<td><input type="text" name="', mysql_field_name ($result_handle, $index), '" size="30"></td></tr>', "\n";
}
This part works well for dynamically generating a form that will gather values based on a table's fields. This form posts these values to a PHP file that will build the appropriate INSERT statement. This is where I'm running into trouble.

I can successfully build the first part of the SQL statement up to here:

INSERT into table_name (field1_name, field2_name, field3_name) VALUES (

Following is the code I'm using to build the INSERT statement:

Code: Select all

$sql = "INSERT INTO '$table_name' (";

//find out column names from db defined by mysql_list_fields() query
$result_handle = mysql_list_fields ("some_db", $table_name) or die("mysql_list_fields () failed with this error message: '". mysql_error () . "'");

//determine total number of fields for table
$number_fields = mysql_num_fields ($result_handle);

//loop through column-field names and add to SQL statement
for ($index = 0; $index < $number_fields; ++$index) {
	$sql .= "mysql_field_name ($result_handle, $index),";
}
	
//remove trailing comma
$sql .= substr($sql, 0, -1);
	
//add closing parentheses and begin VALUES part of statement
$sql .= ") VALUES (";
Now, since this is all done dynamically without having hard coded field names on the page, I'm not sure how to cycle through the values passed from the form and add them to the SQL statement.

Thanks in advance for any assistance or advice.

Posted: Sun Oct 13, 2002 8:40 pm
by volka

Code: Select all

<?php 
//find out column names from db defined by mysql_list_fields() query 
$result_handle = mysql_list_fields ("some_db", $table_name) or die("mysql_list_fields () failed with this error message: '". mysql_error () . "'"); 

//determine total number of fields for table 
$number_fields = mysql_num_fields ($result_handle); 

$fields = ''; // initialize both
$values = ''; // strings to avoid warnings

// looping through the fields
for ($i=0; $i!=$number_fields; $i++)
{
	$currentField = mysql_field_name ($result_handle, $i);
	
	if (isset($_POSTї$currentField])) 
	{ // a possible field's value has been transmitted
		if (strlen($fields) > 0)) 
		{ // already something in, seperate it
			$fields .= ',';
			$values .= ',';
		}
		// append new field and value
		$fields .= $currentField;
		/* some field-type related castings and/or modifications would be nice here
		 i.e. varchar/text/blob --> 'value'
		 take a look at http://www.php.net/manual/en/function.m ... d-type.php
		 and http://www.mysql.com/documentation/mysq ... l#DESCRIBE */
		$values .= mysql_escape_string($_POSTї$currentField]);
	}
}

// create query
$sql = "INSERT INTO '$table_name' (".$fields.') VALUES ('.$values.')';

// let mysql determine wether the query is complete or something's missing
...
completely untested ;)

Posted: Thu Oct 24, 2002 5:10 pm
by crimius
:D

Thanks Volka. That worked beautifully. Have a great day

Posted: Thu Jan 02, 2003 2:44 pm
by crimius
volka wrote:

Code: Select all

// create query
$sql = "INSERT INTO '$table_name' (".$fields.') VALUES ('.$values.')';
In the INSERT statement, why are $fields and $values surrounded by dots (".")?

thanks