Creating INSERT statement from HTML form for MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
crimius
Forum Commoner
Posts: 28
Joined: Sun Oct 13, 2002 6:02 pm
Location: Austin, Texas
Contact:

Creating INSERT statement from HTML form for MySQL

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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 ;)
crimius
Forum Commoner
Posts: 28
Joined: Sun Oct 13, 2002 6:02 pm
Location: Austin, Texas
Contact:

Post by crimius »

:D

Thanks Volka. That worked beautifully. Have a great day
crimius
Forum Commoner
Posts: 28
Joined: Sun Oct 13, 2002 6:02 pm
Location: Austin, Texas
Contact:

Post 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
Post Reply