PHP PDO Prepared Statements: How to auto-generate the SQL statement?

Development Code
,
1

So in PDO using PHP, you can prepare a SQL statement for a few benefits. Those being better efficiency, and protection against SQL injection.

When you prepare an INSERT INTO statement, you do something like this:

INSERT INTO table (fielda, fieldb, fieldc, fieldd) VALUES (?,?,?,?)

You would then bind the parameters, which would go where the ? are as they are placeholders.

What I am trying to think of is a neat way to auto-generate the string of ?,?,? when I don't know what the number of fields will be. The only way that's coming to mind (since I'm still pretty new at PHP scripting) is to do a foreach to count the number of keys in an array, then produce the ? based on that.

I feel like PDO would have a thing for this, but my research (aka, googling) hasn't produced any results.

So my question is literally just "how can I count the number of things in an array, then produce placeholder ?'s for them in a string." I could probably find a stackexchange page for that question if I could figure out how to word it better.

2

You can use count() to get the size of an array. You may want to look at implode() which will concatenate the values of an array into a string for you.

http://php.net/count
http://php.net/implode
http://php.net/array_fill

The following code has not been run, may contain errors:

$input = ['first_name" => "Tom", "last_name" => "Jones"];
$count = count($input);
$statementValues = implode(',', array_fill(0, $count, '?'));
print_r($statementValues);
// ?,?

1 Like
3

That is very useful, thanks. I had known about implode, but not of count or array_fill.

This is how I'm currently doing it:

// Selects a single record from the fetched Assoc Array.
$_Record_Dump = $_Fetched_Assoc_Array[0];
// Produces the needed format for the columns for the prepared statement.
$_Columns = implode("`, `", array_keys($_Record_Dump));

// Creates the needed number of ? for the prepared statement.
foreach ($_Record_Dump as $_KeyRecord => $_Record)
{
	$_VALUES_Qs[] = "?";
}

// Creates the INSERT INTO SQL query as a single large query.
$_Prep_Insert_Selected_SQL = "INSERT INTO dataforms (`" . $_Columns . "`) 
	VALUES (" . implode(", ", $_VALUES_Qs) . ")";