Dynamic INSERT statement

tanandan

I’m trying to build a simple and flexible application with PHP and mySQL. Basically, I have several mySQL tables each with a unique number of fields/columns.

The problem I’m facing is that can’t figure out how to write a dynamic mySQL insert statement based off a PHP array variable. For example, I have a table with three fields: title, author, and year. I want a user to be able to INSERT a record into the table via a simple webform. The catch is that I want to be able to re-use the mySQL statet throughout my application, so it needs to be somewhat generic.

I thought I could go about this by declaring an array variable like so:

$table_variables = array(title, author, year);

My hope is that each form on my application will use the same php/mySQL code base and all I’ll need to do is update the array variable.

Normally, I’d write an INSERT statement like so:

$sql = "INSERT INTO books(title, author, year)
VALUES(
'" . $POST['title'] . "'),
'" . $POST[author] . "'),
'" . $_POST[year] . "')”;

For the application though, I want things to fill in dynamically, something like:

$sql = "INSERT INTO books($table_variables[0], $table_variables[1], $table_variables[2])
VALUES(
'" . $POST[$table_variables[0]] . "'),
'" . $POST[$table_variables[1]] . "'),
'" . $_POST[$table_variables[2]] . "')”;

Is there any way to embed a increment variable within the declaration of another variable. For example, is there a way to embed i++ within the declaration of my $sql variable? Without doing that, I don’t know how to build up the sql statement.

shu

//First, the values in your array should be enclosed with quotes
$table_variables = array('title', 'author', 'year');

//Second, your INSERT statements have too many right parenthesis
$sql = "INSERT INTO books(title, author, year)
VALUES('" . $_POST['title'] . "', '" . $_POST['author'] . "', '" . $_POST['year'] . "');";

//You probably will have to loop through your array to achieve this
$sql = 'INSERT INTO books(';
$val = 'VALUES(';

for ($i=0; $i<count($table_variables); $i++) {

$sql .= $table_variables[$i] . ', ';
//I could wrong, but I don't think you can do $_POST[$table_variables[0]]
$val .= "'" . ${'_POST[' . $table_variables[$i] . ']'} . "', ";

}

$sql = preg_replace("/, $/", '', $sql) . ') ' . preg_replace("/, $/", '', $val) . ');';

tanandan

Thanks Shu. To implement this code, do I ultimately create a new third variable that concatenates the $sql and $val variables to create my final INSERT statement?

Oh, and sorry about the poor syntax in my original example.

shu

//No, this statement is your final INSERT statement.
//It already did the concatenating for you!
$sql = preg_replace("/, $/", '', $sql) . ') ' . preg_replace("/, $/", '', $val) . ');';

//Just execute it like:
$result = mysql_query($sql);

tanandan

Bingo! Thanks Shu.

For what it's worth, you can use $_POST[$table_variables[$val]]

Thanks again.

cgraz

Don't forget to mark this thread resolved if it is.