An easy way to insert a record with multiple variables.

[otuatail]

Is there a neat easy way to insert a record with 7 variables using prepare and execute? Using lots of question marks can lead to confusion. Possibly an array set up to go with the SQL.

	$sqlList = "INSERT INTO Bank_Data SET EntryDate = '" .$EntryDate . "', Input = " . $input . ", Output = " . $out . ", Reason = " . $Reason . ", Comment = '" . $Comment . "', Tag = '" . $Tag . "', AddDate = '" . $AddDate  . "'";
	$stmt = $pdo->prepare($sqlList);
	$stmt->execute([]);

Thanks!

[pbismad]

otuatail Possibly an array set up to go with the SQL

Yes. In fact, if you have more than 2-3 fields, you should be using a data-driven design, where you have an array that defines the expected fields, labels, validation rules. and what processing (insert query in this case). You would then loop over this defining data to validate, then process the data. You would dynamically build the sql query statement from the expected, defined field names and dynamically build a corresponding array of input data for the ->execute(...) call.

[NogDog]

I prefer to use named place-holders, making the code easier to read/maintain.

$sqlList = "
INSERT INTO Bank_Data SET
  EntryDate = :EntryDate,
  Input = :input,
  Output = :out,
  Reason = :Reason,
  Comment = :Comment,
  Tag = :Tag,
  AddDate = :AddDate
";
$stmt = $pdo->prepare($sqlList);
$stmt->execute([
  ':EntryDate' => $EntryDate,
  ':input' => $input,
  ':out' => $out,
  ':Reason' => $Reason,
  ':Comment' => $Comment,
  ':Tag' => $Tag,
  ':AddDate' => $AddDate
]);

Note that the :xxxxxx place-holder names do not necessarily have to exactly match the column and/or variable names; they just have to be the same in both the query and in the array keys.

[otuatail]

would something like this work?

	$sqlIn = "INSERT INTO Bank_Data SET EntryDate = :Entry, Input = :In, Output = :Out, Reason = :Reason, Comment = :Coment, Tag = :Tag, AddDate = :AddDate";	
	$array = [EntryDate->:Entry, Input->In, Output->Out, Reason->Reason, Comment->Coment, Tag->Tag, AddDate->AddDate];
	$stmt = $pdo->prepare($sqlIn);
	$stmt->execute($array);
	

[NogDog]

otuatail would something like this work?

The array keys need to be the place-holder names (including the leading : character). The array values those keys point to are then whatever you want to assign to those place-holders. See the array I created in the execute() call in my prior reply for an example, but feel free to change the place-holder names as you see fit.

[otuatail]

Okay but you are using a single apostrophe around the place holder as in ':EntryDate' and you use => to the value. Is this a C type pointer?

[NogDog]

otuatail Is this a C type pointer

No, it's just the PHP syntax for array definitions, and it's an equals sign and the right "arrow", i.e. this: =>, not this: ->. The value on the left side is the array key, to which the value on the right side gets assigned. So if you do...

$test = [
    'foo' => "bar"
];

...then the value of $test['foo'] will now be the string "bar".

[NogDog]

PS: Using the colon as the prefix of the array keys is specific to assigning an array of bound parameters to a PDO prepared query -- not something you would normally do for every-day, run-of-the-mill array definitions.

[pbismad]

That is the php array element syntax for an associative-key/value pair. The associative key name must match the name of the prepared query place holder.

You were given the following straight-forward instructions for converting an existing old query to a prepared query -

1. remove the variables that are inside the sql query statement (keep these variables for use later).
2. remove any single-quotes that were around the variables and any {} or concatenation dots that were used to get the variables into the sql query statement.
3. put a prepared query place-holder ? into the sql query statement where each variable was at.
4. call the ->prepare() method for the resulting sql query statement. this returns a PDOstatement object, and should be named $stmt or similar.
5. call the ->execute([...]) method with an array containing the variables you removed in step #1.
6. for a SELECT query, use either the ->fetch() method (for a single row of data), the ->fetchAll() method (for a set of data), or sometimes the ->fetchColumn() method (when you want a single value from one row of data.)

Had you made use of those instructions, you would have ended up with this -

$sql = "INSERT INTO Bank_Data SET EntryDate=?, Input=?, Output=?, Reason=?, Comment=?, Tag=?, AddDate=?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$EntryDate, $input, $out, $Reason, $Comment, $Tag, $AddDate]);

How is that confusing?

You have stated you are faced with converting a lot of code. This list of conversion instructions, that I gave you on a different help forum, which also included an example for the query you were dealing with at that time, was to allow you to convert existing code with the least amount of work.

As to a neat and easy way of getting the computer to do this work for you, here's an abbreviated data-driven example -

// define expected fields
$fields['EntryDate'] = ['label'=>'Entry Date','validation'=>['required','date'],'processing'=>['insert']];
// add elements for the rest of the fields

$table = 'Bank_Data';


// at the point of building and executing the insert query
$set = []; // array to hold the set terms
$params = []; // array to hold the input parameters
foreach($fields as $field=>$arr)
{
	if(in_array('insert',$arr['processing']))
	{
		$set[] = "`$field`=?";
		$params[] = $_POST[$field];
	}
}

if(!empty($set))
{
	$sql = "INSERT INTO `$table` SET " . implode(',',$set);
	// examine the sql query statement and the array of parameters
	echo $sql; print_r($params);
	$stmt = $pdo->prepare($sql);
	$stmt->execute($params);
}