I've gotten to a point where I absolutely need some clean way to safely pass lists/arrays from php to SQL server stored procedures and table value functions. The PHP SQL server driver still does not support table valued parameters, according to Microsoft docs.
In another question, using XML was suggested as an alternative.
Does anyone have a code sample for passing the equivalent of a TVP using an XML stream and PHP PDO or another clean alternative?
The primitive, but foolproof solution, is to pass it as a delimited string, and use a SPLIT function in your proc to convert the string to a table that you can then JOIN to.
Google SQL SPLIT FUNCTION to get free cut-n-paste code.
You could use xml, but a better, more compact idea would be to use JSON.
$myArray = array("thing 1", "thing 2", "thing 3");
$sql = $pdo->prepare("INSERT INTO `table` (`array_data`) VALUES (:myArray)");
$sql->execute(array(":myArray"=>json_encode($myArray)));
Then when you pull the data back out of the database you can convert it back into an array with:
$myArray = json_decode($res['myArray'], true);
