Help. prepared statements and dynamic queries.

jtorral

I have a dilemma. Maybe it's an easy fix but i just cant see the solution.

My code already works without preparing the sql. The problem is preparing the sql and building the dynamic query. In a nut shell, I have a form with 10 different fields. And depending on which field gets data, I build my query so that it only works on the fields the user typed something into.

let me summarize ....

I have a basic query that starts off like this

$updatestring = "";
$query = "UPDATE table a SET ";

if field1 set then $updatestring = $updatestring . " field1 = field1val "
if field2 set then $updatestring = $updatestring . " field2 = field1val "
if field3 set then $updatestring = $updatestring . " field3 = field1val "
if field4 set then $updatestring = $updatestring . " field4 = field1val "

and so on ....

Finally $query = $query . $updatestring which can end up looking like:

UPDATE table a SET field1 = field1val, field3 = field1val .... depending on which fields were set.

I take care of commas and so on. Like I said this works.

However, to prepare the statement really complicates things because if it looks like this:

UPDATE table a SET field1 = ? , field3 = ?

how do I know which variable to bind to the param out of the 10 possible fields ? How do I build the string types and place them in the right order? Do you see my dilemma?

mysqli_stmt_bind_param($stmt, 'ii', $field1val, $field3val);

Any help would be appreciated. I will also post this in the mysql section.

Thanks,

jazz_snob

well, you said any help, so here it is

I don't know mysqli....I don't even remember mysql_query() becuase it was mindnumbing drudgery to use it.

Here is the example from php.net -- you have to pass in a string parameter w/ characters indicating the data types of the values to be bound to each place-holder ? in query, then all of the values. Reminds me of sprintf() or printf():

$stmt = mysqli_prepare($link, "INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'sssd', $code, $language, $official, $percent);

Here is an example using PDO, this is the type of interface I prefer:

$pdo = new PDO($yourConnectionDsn, $user, $password);
$stmt = $pdo->prepare("UPDATE table a SET field1 = ? , field3 = ?");
// here you pass an ordered array of values
$stmt->execute(array($valueForField1, $valueForField2));

The trick is the order of values in the array you use in execute() call must match ordering of fields given in the prepare statement. Having said that, its pretty easy once you get used to it.

The cool thing with PDO is you can set an attribute to throw exceptions rather than have to check for errors every time you do a query. Then you can nest your querries in try/catch blocks, or let the exceptions bubble up to some other handler in the application. Maybe you can do that w/ mysqli too, I don't know.